Re: firebird X postgresql 8.1.2 windows, performance comparison

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