Thread: bug with vacuum analyze?

I believe I have found a bug in the vacuum procedures.  I am helping out
a friend who is running a "alumni" sites of sorts using postgresql 7.2.3
as the backend.  The first page someone loads does most of the queries
that any page will need and stores it in a session file (php style) and
hands a cookie to the user.

If I never do a vacuum analyze, it takes between 8-11 (high spikes
around 23) seconds most of the time for this first page to load.  If I
do vacuum, nothing seems to change.  If I do a vacuum analyze, the
minimum load time is between 41-44 seconds.  Higher system loads,
instead of adding 4-10 seconds, easily double the number up beyond 80
seconds.  Yes, each of these tables have at least one index (the most
indexes I believe are 4).  Those with multiple, I believe, all have a
primary index key.

Effects of vacuum full and vacuum freeze haven't been tested.  The only
way to recover from these horrible times seems dump/drop/reload.

Is this a bug, known or otherwise, are their workarounds besides don't
do it?

Thank you,
Trever Adams

P.S. This is a RedHat 8.0 box with all errata fixes.  Specifics can be
provided later if needed.
--
"Perilous to all of us are the devices of an art deeper than we possess
ourselves." -- Gandalf the White [J.R.R. Tolkien, "The Two Towers", Bk
3, Ch. XI]

On Mon, 2003-03-10 at 13:29, Trever L. Adams wrote:
> If I never do a vacuum analyze, it takes between 8-11 (high spikes
> around 23) seconds most of the time for this first page to load.  If I
> do vacuum, nothing seems to change.  If I do a vacuum analyze, the
> minimum load time is between 41-44 seconds.  Higher system loads,
> instead of adding 4-10 seconds, easily double the number up beyond 80
> seconds.  Yes, each of these tables have at least one index (the most
> indexes I believe are 4).  Those with multiple, I believe, all have a
> primary index key.

> Is this a bug, known or otherwise, are their workarounds besides don't
> do it?

It doesn't look like a bug in VACUUM -- more likely, running VACUUM
ANALYZE causes the optimizer to use a different query plan, which
happens to perform much worse for the queries you're running.

(In general, ANALYZE should improve query plans, but it seems that by
chance the bogus plan the optimizer chooses without stats is actually
better than the choice it makes when more informed).

Can you post the relevant query, the schemas of any involved relations
and the output of EXPLAIN ANALYZE for the query both before and after
running VACUUM ANALYZE?

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC