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
Attachment:
sql_query_slow.txt.tar.gz
Description: GNU Zip compressed data
