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