Re: [SQL] Deleting indexes before vacuum?

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