Date: Tue, 27 Dec 2005 10:13:43 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Albert Cervera Areny <albert@sedifa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with 8.1.1 compared to 7.4.7
Message-ID: <20051227171343.GA70061@winnie.fuhr.org>
References: <200512271709.29185.albert@sedifa.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200512271709.29185.albert@sedifa.com>

On Tue, Dec 27, 2005 at 05:09:28PM +0100, Albert Cervera Areny wrote:
> 	However, now we have a query that is much slower with 8.1 compared to 7.4. 
> The query lasts 7minutes (all the times we try) with 8.1, keeping CPU usage 
> at 93~97% while it lasts 25 seconds in 7.4 the first time going down to 4 
> seconds the following tries.
> 	We're not experts at all but we can't see anything strange with the 
> differences of EXPLAIN in the queries. Below I paste the query and the 
> EXPLAIN output.

Could you post the EXPLAIN ANALYZE output of the query on both
systems?  That'll show how accurate the planner's estimates are.

Have you run ANALYZE (or VACUUM ANALYZE) on the tables in both
versions?  The row count estimates in the 8.1.1 query differ from
those in the 7.4.7 query.  Are the two versions using the same data
set?

Are your configuration settings the same in both versions?  You
mentioned increasing work_mem, but what about others like
effective_cache_size, random_page_cost, and shared_buffers?

-- 
Michael Fuhr