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
