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
Date: Tue, 27 Dec 2005 10:13:43 -0700
Message-ID: <20051227171343.GA70061@winnie.fuhr.org> (raw)
In-Reply-To: <200512271709.29185.albert@sedifa.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, albert@sedifa.com
Subject: Re: Performance problems with 8.1.1 compared to 7.4.7
In-Reply-To: <20051227171343.GA70061@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox