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 38C1D9F944D for ; Sat, 13 May 2006 18:26:15 -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 29228-05 for ; Sat, 13 May 2006 18:26:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id EC9449F9316 for ; Sat, 13 May 2006 18:26:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k4DLQ6Xa006204; Sat, 13 May 2006 17:26:07 -0400 (EDT) To: "andremachado" cc: pgsql-performance@postgresql.org Subject: Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows) In-reply-to: <20060512143716.M32697@techforce.com.br> References: <20060512143716.M32697@techforce.com.br> Comments: In-reply-to "andremachado" message dated "Fri, 12 May 2006 12:48:52 -0200" Date: Sat, 13 May 2006 17:26:06 -0400 Message-ID: <6203.1147555566@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/334 X-Sequence-Number: 19121 "andremachado" writes: > continuing the saga, > http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php , > my coleague created a test database with fake data (see below). I tried to use this data to replicate your results, and could not. I grabbed a copy of what I think is the latest Firebird release, firebird-1.5.3.4870, built it on a Fedora Core 4 machine (32-bit, couldn't get it to build cleanly on my newer 64-bit machine :-() and compared to Postgres 8.1 branch tip on the same machine. On the interesting sub-sub-EXISTS query, I see these results: Firebird: SQL> set stats on; SQL> set plan 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 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$FOREIGN1)) PLAN (DEC2 NATURAL) PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN1)) PLAN (CADASTRO NATURAL) Current memory = 786704 Delta memory = 309056 Max memory = 786704 Elapsed time= 344.19 sec Cpu = 0.03 sec Buffers = 75 Reads = 2081702 Writes = 16173 Fetches = 21713743 The cpu = 0.03 sec bit is bogus; in reality the CPU is maxed out and the isql process accumulates very nearly 344 seconds runtime. Postgres: bc=# \timing Timing is on. bc=# 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 ; UPDATE 15490 Time: 420350.628 ms Now I know nothing about Firebird and it's quite possible that I missed some essential tuning step, but I'm sure not in the same ballpark as your report of 0.72 sec to run this query. regards, tom lane