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>



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"andremachado" <andremachado@techforce.com.br> writes:
> Firebird has something similiar to EXPLAIN. Please look below.

Hm, maybe I just don't know how to read their output, but it's not
obvious to me where they are doing the min/max aggregates.

> Is there something really wrong with the postgresql configuration (at my
> previous msg) that is causing this poor performance at these 2 queries?

I don't think it's a configuration issue, it's a quality-of-plan issue.

Could you put together a self-contained test case for this problem? I
don't have the time or interest to try to reverse-engineer tables and
test data for these queries --- but I would be interested in finding out
where the time is going, if I could run the queries.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org