From: Tom Lane <tgl@sss.pgh.pa.us>
To: andremachado <andremachado@techforce.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
Date: Tue, 25 Apr 2006 22:06:29 -0400
Message-ID: <11303.1146017189@sss.pgh.pa.us> (raw)
In-Reply-To: <20060425160609.M20649@techforce.com.br>
References: <20060425160609.M20649@techforce.com.br>
"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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, andremachado@techforce.com.br
Subject: Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
In-Reply-To: <11303.1146017189@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox