Message-ID: <491DABAD.10401@archonet.com>
Date: Fri, 14 Nov 2008 16:47:41 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.17 (X11/20080914)
MIME-Version: 1.0
To: Patrice Beliveau <pbeliveau@avior.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: Difference in query plan
References: <491DA3DA.5050002@avior.ca>
In-Reply-To: <491DA3DA.5050002@avior.ca>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Patrice Beliveau wrote:
> I have a database in a production server (8.1.9) with to schema
> containing the sames table same index, same every thing, but with
> different data. When I execute a query in one schema, it take much more
> time to execute then the other schema.
[snip]
> I'm wondering where to start searching to fix this problem

> Production server schema 1 query plan:
> Nested Loop  (cost=569.23..634.43 rows=1 width=121) (actual
> time=1032.811..1032.811 rows=0 loops=1)
[snip]
> Total runtime: 1034.204 ms

> Production server schema 2 query plan:
> Nested Loop  (cost=133.42..793.12 rows=1 width=123) (actual
> time=0.130..0.130 rows=0 loops=1)
[snip]
> Total runtime: 0.305 ms

Well there's something strange - the estimated costs are fairly similar
(643.43 vs 793.12) but the times are clearly very different (1034 vs 0.3ms)

The suspicious line from the first plan is:
>               ->  Seq Scan on mrp m  (cost=0.00..119.92 rows=5892
> width=39) (actual time=0.343..939.462 rows=5892 loops=1)

This is taking up almost all the time in the query and yet only seems to
be scanning 5892 rows.

Run a vacuum verbose against table "mrp" and see if it's got a lot of
dead rows. If it has, run VACUUM FULL and REINDEX against it and see if
that solves your problem.

I'm guessing you have / had a long-running transaction interfering with
vacuum on this table, or perhaps a bulk update/delete?

-- 
  Richard Huxton
  Archonet Ltd