X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id AC8E211F60AE for ; Thu, 27 Apr 2006 09:32:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 10899-05 for ; Thu, 27 Apr 2006 09:32:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 3EB6D11F61F8 for ; Thu, 27 Apr 2006 09:32:22 -0300 (ADT) Received: from techforce.com.br (techforce.com.br [216.218.247.150]) by svr4.postgresql.org (Postfix) with ESMTP id 0DDC45B5BEB for ; Thu, 27 Apr 2006 12:32:22 +0000 (GMT) Received: from techforce.com.br (techforce.com.br [127.0.0.1]) by techforce.com.br (8.12.11/8.12.11) with ESMTP id k3RCWVKg024428 for ; Thu, 27 Apr 2006 05:32:31 -0700 From: "andremachado" To: pgsql-performance@postgresql.org Subject: Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)] Date: Thu, 27 Apr 2006 10:32:31 -0200 Message-Id: <20060427122235.M42721@techforce.com.br> X-Mailer: Open WebMail 2.51 20050228 X-OriginatingIP: 161.148.39.27 (andremachado@techforce.com.br) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/632 X-Sequence-Number: 18716 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>