From: Michael Fuhr <mike@fuhr.org>
To: Gabriele Turchi <gabriele.turchi@l39a.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big differences in plans between 8.0 and 8.1
Date: Sat, 15 Jul 2006 13:02:10 -0600
Message-ID: <20060715190210.GA76277@winnie.fuhr.org> (raw)
In-Reply-To: <1152972852.3541.0.camel@apollo5.casa.intranet>
References: <1152972852.3541.0.camel@apollo5.casa.intranet>
On Sat, Jul 15, 2006 at 04:14:11PM +0200, Gabriele Turchi wrote:
> Hi all. I have a strange (and serious) problem with an application
> ported from postgres 8.0 to 8.1.
>
> The old installation is postgresql-8.0.4-2.FC4.1 running on a Fedora 4,
> the new one is postgresql-8.1.4-1.FC5.1 running on a fedora 5.
>
> Some query is now _very_ slow. I've found some deep differences between
> query plans.
Have you run ANALYZE in 8.1? Some of the row count estimates in
the 8.1 plan differ significantly from the actual number of rows
returned, while in the 8.0 plan the estimates are accurate. For
example, in one case the 8.0 plan shows 349 rows estimated, 349
rows returned:
-> Seq Scan on registrazioni (cost=0.00..11.98 rows=349 width=19) (actual time=0.029..2.042 rows=349 loops=1)
Filter: (date((now() - '02:00:00'::interval)) = data)
but the 8.1 plan shows 2 rows estimated, 349 rows returned:
-> Seq Scan on registrazioni (cost=0.00..11.98 rows=2 width=44) (actual time=0.025..2.315 rows=349 loops=1)
Filter: (date((now() - '02:00:00'::interval)) = data)
This suggests that the 8.1 statistics are out of date, possibly
because ANALYZE or VACUUM ANALYZE hasn't been run since the data
was loaded. Try running ANALYZE in 8.1 and post the new plans if
that doesn't help.
--
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, gabriele.turchi@l39a.com
Subject: Re: Big differences in plans between 8.0 and 8.1
In-Reply-To: <20060715190210.GA76277@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