Subject: Re: firebird X postgresql 8.1.2 windows, performance
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: andremachado <andremachado@techforce.com.br>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060313175614.M17691@techforce.com.br>
References: <20060313175614.M17691@techforce.com.br>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1142280146.2087.7.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Mon, 13 Mar 2006 14:02:26 -0600

On Mon, 2006-03-13 at 12:11, andremachado wrote:
> 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?

Well, it's not uncommon in mvcc databases.  My testing against Oracle
9.x series showed little difference on similar machines.  In fact, my
workstation running PostgreSQL was faster at count() queries than our
old Sun 420 running Oracle, which has much more memory.

Can we see an explain output and schema (if needed) for this query? 
Just plain explain, not analyze, since, like you said, it's been running
for hours.

I'd like to just add, that if you use any database long enough, you'll
eventually come up with queries that it runs slow on that other
databases run quickly on.  It's just the nature of the beast.  That
said, I've never seen a team work so hard to fix poorly performing
queries as the guys that write PostgreSQL.  If there's a natural, basic
fix to the problem, you'll see it pretty quick, whether that be in the
query itself, the planner, or the execution of the query.  And if it's
just not possible in PostgreSQL, you'll usually hear that pretty quick
too.