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

--
Michael Fuhr

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 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