Date: Sat, 15 Jul 2006 13:02:10 -0600
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
Message-ID: <20060715190210.GA76277@winnie.fuhr.org>
References: <1152972852.3541.0.camel@apollo5.casa.intranet>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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