Andre,
> http://candle.pha.pa.us/main/writings/pgsql/hw_performance/
> brought some light over the subject. For few users, could be a viable
> alternative.
That article is very old. Read this instead:
http://www.powerpostgresql.com/PerfList
> 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.
How about an EXPLAIN?
And, did you run ANALYZE on the data?
> 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.
No, actually we excel at complex queries. Some of the data warehousing
stuff I run involves queries more than a page long. Either you're
hitting some Windows-specific problem, or you still have some major basic
tuning issues.
That being said, there's nothing wrong with Firebird if he wants to use it.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco