Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "andremachado" <andremachado(at)techforce(dot)com(dot)br>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
Date: 2006-04-26 02:06:29
Message-ID: 11303.1146017189@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"andremachado" <andremachado(at)techforce(dot)com(dot)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 where ITEM_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_ARQUIVO in (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

In response to

pgsql-performance by date

Next:From: David WheelerDate: 2006-04-26 02:27:48
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Previous:From: Leigh DyerDate: 2006-04-26 01:53:06
Subject: Re: Large (8M) cache vs. dual-core CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group