Hello, Many thanks for your suggestions. I will try them. The last two queries almost did not use disk, but used 100% cpu. The differences of performance are big. Firebird has something similiar to EXPLAIN. Please look below. Is there something really wrong with the postgresql configuration (at my previous msg) that is causing this poor performance at these 2 queries? I tweaked until almost no disk was used, but now it is using 100% cpu and took too much time to complete. Thanks. Andre Felipe Machado http://www.techforce.com.br SQL> set plan on; SQL> set stats on; SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and 1 in (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end; PLAN (CAD3 INDEX (PK_CADASTRO_DESC)) PLAN (DEC2 NATURAL) PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18)) PLAN (CADASTRO NATURAL) Current memory = 911072 Delta memory = 355620 Max memory = 911072 Elapsed time= 1.89 sec Cpu = 0.00 sec Buffers = 2048 Reads = 1210 Writes = 14 Fetches = 310384 SQL> SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end; PLAN (CAD3 INDEX (RDB$FOREIGN18)) PLAN (DEC2 NATURAL) PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18)) PLAN (CADASTRO NATURAL) Current memory = 938968 Delta memory = 8756 Max memory = 15418996 Elapsed time= 1.09 sec Cpu = 0.00 sec Buffers = 2048 Reads = 0 Writes = 0 Fetches = 301007 SQL>
