From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Hagerty <matthew@venux.net>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Deleting indexes before vacuum?
Date: Sat, 20 Nov 1999 01:14:25 -0500
Message-ID: <6453.943078465@sss.pgh.pa.us> (raw)
In-Reply-To: <4.1.19991119195147.00a16ef0@mail.venux.net>
Matthew Hagerty <matthew@venux.net> writes:
> I read a post sometime back where someone said their normal routine for
> running a vacuum was to delete the indexes first, then recreate them after
> the vacuum. Is this necessary? If the indexes are gone does vacuum
> analyze still make sense?
Sure. Vacuum analyze is mostly about deleting dead tuples, reclaiming
the space they occupied, and computing statistics about column values.
These activities are useful whether there are indexes or not. If there
are indexes, vacuum also cleans up useless entries in the indexes (ie,
pointers to dead tuples).
The reason for the drop index/vacuum/create index raindance is that
vacuum's method of cleaning up indexes seems to be horrendously
inefficient. It actually takes less time to rebuild an index on a
large table *from scratch* than to let vacuum fix up the index.
This is, of course, pretty bogus. I personally have no idea *why*
vacuum's index-handling code is so slow --- I've been griping about it
freely for a year or more, but have not had time to look into causes or
solutions. Perhaps someone else has a better idea of what's going on
here (...paging Vadim...)
Anyway, the drop/vacuum/recreate business is nothing more nor less than
a quick-hack workaround for a performance deficiency in the current
implementation of vacuum. That deficiency should go away eventually,
but in the meantime, if vacuum takes too long on your large tables,
you might give it a try.
> I am concerned about this because I never removed the indexes prior to
> vacuum, but today I executed this simple query:
> select note_id from appnotes where note_id=6068;
> note_id
> -------
> 17768
> (1 row)
> This was rather alarming, so I deleted all the indexes, ran vacuum,
> recreated the indexes. Now the query works:
Ugh. Definitely a busted index. Vacuum is *not* designed to recover
from corrupted-data situations. Dropping/rebuilding indexes will
recover from index corruption problems (with or without a vacuum), so
long as the underlying table is OK. That seems to be what you saw here.
I have no words of wisdom about what might have caused the index
corruption --- if you can find a sequence that reproduces it, please
file a bug report!
> I am running pg-6.4 (I know it needs an update) on this particular server
> and 6.5.x on others.
6.5 is more stable than 6.4 AFAIK. It's still got bugs of course...
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, matthew@venux.net
Subject: Re: [SQL] Deleting indexes before vacuum?
In-Reply-To: <6453.943078465@sss.pgh.pa.us>
References: <4.1.19991119195147.00a16ef0@mail.venux.net> <6453.943078465@sss.pgh.pa.us>
* 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