From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: firebird X postgresql 8.1.2 windows, performance comparison
Date: Mon, 13 Mar 2006 11:15:00 -0800
User-Agent: KMail/1.8
Cc: "andremachado" <andremachado@techforce.com.br>
References: <20060313175614.M17691@techforce.com.br>
In-Reply-To: <20060313175614.M17691@techforce.com.br>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200603131115.01056.josh@agliodbs.com>

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