From: andremachado <andremachado@techforce.com.br>
To: pgsql-performance@postgresql.org
Subject: firebird X postgresql 8.1.2 windows, performance comparison
Date: Mon, 13 Mar 2006 15:11:54 -0300
Message-ID: <20060313175614.M17691@techforce.com.br> (raw)
Hello,
Attached is the text file containing the last rounds of configurations.
This time, used "show all" just before issuing each relevant "explain analyze"
to ensure available information.
Note that the last runs are being executed concurrently with other problematic
query that is consuming 100% cpu for HOURS.
Some people suggested to reduce shared buffers, but for few users (1 or 2
simultaneously for this app, as my friend told me), it could be large.
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/
brought some light over the subject. For few users, could be a viable alternative.
But, despite these huge improvements in speed, there are other problematic
queries with postgresql.
One of them is:
select count(distinct NF.ID_NF ) as contagem, DE.AM_REFERENCIA as campo
from DECLARACAO DE inner join CADASTRO CAD on
(CAD.ID_DECLARACAO=DE.ID_DECLARACAO)
inner join NOTA_FISCAL NF on (NF.ID_CADASTRO=CAD.ID_CADASTRO)
inner join EMPRESA EMP on (EMP.ID_EMPRESA=DE.ID_EMPRESA)
inner join ARQUIVO_PROCESSADO ARQ on (ARQ.ID_ARQUIVO=DE.ID_ARQUIVO)
group by DE.AM_REFERENCIA
order by DE.AM_REFERENCIA
firebird windows executed in 1min30s
postgresql windows is running for 3 hours and still not finished.
I already know that count() is VERY performance problematic in postgresql.
Is there a way to work around this?
Unfortunately, the deadline for my friend project is approaching and he is
giving up postgresql for firebird.
If some work around is available, he will give another try. But i already saw
that count and joins are still problem.
He asked me if other people are struggling with poor performance and wondered
if all other users are issuing simple queries only.
Any suggestions?
Thanks .
Andre Felipe Machado
Attachments:
sql_query_slow.txt.tar.gz (application/x-gzip, 17.6K) download
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: andremachado@techforce.com.br
Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
In-Reply-To: <20060313175614.M17691@techforce.com.br>
* 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