Re: Big differences in plans between 8.0 and 8.1

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