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>
