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

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 8 | next ›
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJul 15, '06 at 2:14p
activeJul 18, '06 at 11:29a
posts8
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase