From: Gabriele Turchi <gabriele.turchi@l39a.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org, Marco Colombo <marco@esiway.net>
Subject: Re: Big differences in plans between 8.0 and 8.1
Date: Sat, 15 Jul 2006 21:55:49 +0200
Message-ID: <1152993350.3541.22.camel@apollo5.casa.intranet> (raw)
In-Reply-To: <20060715190210.GA76277@winnie.fuhr.org>
References: <1152972852.3541.0.camel@apollo5.casa.intranet>
<20060715190210.GA76277@winnie.fuhr.org>
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
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: gabriele.turchi@l39a.com, mike@fuhr.org, marco@esiway.net
Subject: Re: Big differences in plans between 8.0 and 8.1
In-Reply-To: <1152993350.3541.22.camel@apollo5.casa.intranet>
* 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