Re: REINDEX slow?

Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?

Basically, I just re-indexed a whole bunch of databases on one system, and
now queries are much faster (though the REINDEX itself took about an hour
and a half, and this was a specific index * 15 tables * 20 databases). If
bloat is still an issue, and REINDEX is still a recommended way to improve
performce (which at least a number of articles was suggesting, is this
still the case?), then is it possible to have a REINDEX version that can
utilize the old index if the index is not corrupted? Or would this be too
much work? I would prefer to keep the amounts of locks happening on tables
to a minimum.

Alternatively, if I created a second index, and then dropped the first,
would this be faster (though I would suppose that an ANALYZE would need to
be done to recognize the utility of the new index, thereby negating any
speed improvements)?

Regards!
Ed

On Sun, 11 Apr 2004, Tom Lane wrote:

> Edmund Dengler <edmundd(at)eSentire(dot)com> writes:
> > Question: Does a REINDEX use the old version to build
> > the new version, or is it the semantic equivalent to "drop index; build
> > new index".
>
> The latter.
>
> > If the second, any particular reason
>
> REINDEX is customarily used to recover from a corrupted-index situation.
>
> regards, tom lane
>