Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)

"andremachado" <andremachado(at)techforce(dot)com(dot)br> 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