From: Gabriele Turchi <gabriele.turchi@l39a.com>
To: Joe Conway <mail@joeconway.com>
Cc: 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 22:22:50 +0200
Message-ID: <1152994970.3541.31.camel@apollo5.casa.intranet> (raw)
In-Reply-To: <44B94A51.9050108@joeconway.com>
References: <1152972852.3541.0.camel@apollo5.casa.intranet>
<20060715190210.GA76277@winnie.fuhr.org>
<1152993350.3541.22.camel@apollo5.casa.intranet>
<44B94A51.9050108@joeconway.com>
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
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, mail@joeconway.com, mike@fuhr.org, marco@esiway.net
Subject: Re: Big differences in plans between 8.0 and 8.1
In-Reply-To: <1152994970.3541.31.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