Skip site navigation (1) Skip section navigation (2)

Re: Big differences in plans between 8.0 and 8.1

From: Gabriele Turchi <gabriele(dot)turchi(at)l39a(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Michael Fuhr <mike(at)fuhr(dot)org>, pgsql-performance(at)postgresql(dot)org, Marco Colombo <marco(at)esiway(dot)net>
Subject: Re: Big differences in plans between 8.0 and 8.1
Date: 2006-07-15 20:22:50
Message-ID: 1152994970.3541.31.camel@apollo5.casa.intranet (view raw or flat)
Thread:
Lists: pgsql-performance
Il giorno sab, 15/07/2006 alle 13.04 -0700, Joe Conway ha scritto:
> Gabriele Turchi wrote:
> > 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?
> > 
> 
> Why not just periodically (once an hour?) run "ANALYZE registrazioni;" 
> during the day. This will only update the statistics, and should be very 
> low impact.
> 

This is my "solution" too... but: is enough? Or else: there is a better
way to do this? If the performance in the better case is 50 times faster
than the worse case, during an hour (50/100 record inserted in
"registrazioni") how much the performance can fall before the new
"ANALYZE" is run? Otherwise, running ANALYZE more frequently can badly
affect the overall performance?

A so big difference in postgres performance, can be considered a bug or
a over-optimization in the plan making? Why (at least apparently) the
8.0 version is not affected?

> HTH,
> 
> Joe
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Thank you all very much,
Gabriele



In response to

Responses

pgsql-performance by date

Next:From: Peter EisentrautDate: 2006-07-15 23:53:30
Subject: Re: increment Rows in an SQL Result Set postgresql
Previous:From: Joe ConwayDate: 2006-07-15 20:04:33
Subject: Re: Big differences in plans between 8.0 and 8.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group