Re: Big differences in plans between 8.0 and 8.1 - Mailing list pgsql-performance

From Gabriele Turchi
Subject Re: Big differences in plans between 8.0 and 8.1
Date
Msg-id 1152993350.3541.22.camel@apollo5.casa.intranet
Whole thread Raw
In response to Re: Big differences in plans between 8.0 and 8.1  (Michael Fuhr <mike@fuhr.org>)
Responses Re: Big differences in plans between 8.0 and 8.1  (Joe Conway <mail@joeconway.com>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: Michael Fuhr
Date:
Subject: Re: Big differences in plans between 8.0 and 8.1
Next
From: Joe Conway
Date:
Subject: Re: Big differences in plans between 8.0 and 8.1