Subject: Re: Big differences in plans between 8.0 and 8.1
From: Gabriele Turchi <gabriele.turchi@l39a.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org, Marco Colombo <marco@esiway.net>
In-Reply-To: <20060715190210.GA76277@winnie.fuhr.org>
References: <1152972852.3541.0.camel@apollo5.casa.intranet>
	<20060715190210.GA76277@winnie.fuhr.org>
Content-Type: text/plain
Organization: L39A S.r.l.
Date: Sat, 15 Jul 2006 21:55:49 +0200
Message-Id: <1152993350.3541.22.camel@apollo5.casa.intranet>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Il giorno sab, 15/07/2006 alle 13.02 -0600, Michael Fuhr ha scritto:
> 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

Running an ANALYZE really change the plan, now it is fast as before
(8.0).

On the production system a VACUUM FULL ANALYZE is run every morning
after a clean-up, when the "registrazioni" table is empty. During the
day this table fills up (about 500 record any day), and apparently the
performances are free-falling very quickly. This behaviour has not
changed between the old and the new installation.	

Can you suggest an easy way to collect and keep up-to-date these
statistics in a very low-impact way?

I'm stunned from a so big difference in execution time from a so small
difference in the records number...

> 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.
> 

Thank you very much,
Gabriele