Thread: Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
From
"andremachado"
Date:
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>
Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
From
Tom Lane
Date:
"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