"andremachado" <andremachado@xxxxxxxxxxxxxxxx> 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
