Обсуждение: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)

Hello,
The performance comparison saga of the last month continues (see list archive).
After some time experimenting on windows, the conclusion is clear:

windows is likely crap for databases other than MS-SQL.

I guess that MS-SQL uses lot of undocumented api calls, may run in kernel
mode, ring 0 and a lot of dirty tricks to get some reasonable performance.

Then, I asked my coleague to send a new FB dump and a Pg dump to try at my
desktop machine.
This time, the database is somewhat bigger. Around 20 million records.

The timings are attached. Tried to follow the same query sequence on both files.
Both databases are much more faster on linux than on windows, and the desktop
machine is not dedicated and tuned. (no scsi, no raid, many services enabled,
ext3 fs, etc).
At many of the queries, postgresql is faster, sometimes way MUCH faster.
But Firebird have very good defaults out of the box and a few of the queries
are really a pain in Postgresql.
Please, see the abismal timing differences at the last 2 queries, for example.
They used 100% cpu, almost no disk activity, no twait cpu, for loooong time to
complete.
Maybe these queries bring into the light some instructions weaknesses, or bad
tuning.
Do you have some suggestions?
Regards.

Andre Felipe Machado
http://www.techforce.com.br
linux blog

"andremachado" <andremachado@techforce.com.br> writes:
> After some time experimenting on windows, the conclusion is clear:
> windows is likely crap for databases other than MS-SQL.

Maybe.  One thing that comes to mind is that you really should do some
performance tuning experiments.  In particular it'd be a good idea to
increase checkpoint_segments and try other settings for wal_sync_method.
Your fifth query,

bddnf=# explain analyze update NOTA_FISCAL  set VA_TOTAL_ITENSDNF = (select sum(ITEM_NOTA.VA_TOTAL) from ITEM_NOTA
whereITEM_NOTA.ID_NF = NOTA_FISCAL.ID_NF)  where ID_NF in (select NF2.ID_NF from DECLARACAO DE inner join CADASTRO CAD
on(CAD.ID_DECLARACAO=DE.ID_DECLARACAO) inner join NOTA_FISCAL  NF2 on (NF2.ID_CADASTRO=CAD.ID_CADASTRO) where
DE.ID_ARQUIVOin (1) ); 

shows runtime of the plan proper as 158 seconds but total runtime as 746
seconds --- the discrepancy has to be associated with writing out the
updated rows, which there are a lot of (719746) in this query, but still
we should be able to do it faster than that.  So I surmise a bottleneck
in pushing WAL updates to disk.

The last two queries are interesting.  Does Firebird have any equivalent
of EXPLAIN, ie a way to see what sort of query plan they are using?
I suspect they are being more aggressive about optimizing the max()
functions in the sub-selects than we are.  In particular, the 8.1 code
for optimizing min/max just punts if it sees any sub-selects in the
WHERE clause, which prevents us from doing anything with these examples.

    /*
     * Also reject cases with subplans or volatile functions in WHERE. This
     * may be overly paranoid, but it's not entirely clear if the
     * transformation is safe then.
     */
    if (contain_subplans(parse->jointree->quals) ||
        contain_volatile_functions(parse->jointree->quals))
        return NULL;

This is something I'd wanted to go back and look at more carefully, but
never got around to.

            regards, tom lane