Re: index question..

"Williams, Travis L, NPONS" <tlw(at)att(dot)com> writes:
> I thought reindexing was only to be used on corrupt tables.. will
> vacumming fix the problem?

Scott's being unnecessarily vague about the index bloat problem.
The issue is quite simple and easily understood: vacuuming does delete
dead index entries, but it does not collapse out entire unused pages in
indexes. So (a) an index can never get smaller, even if you delete many
entries; (b) if the range of index entries changes over time, the index
will grow. For instance, if you're indexing a timestamp column, the
right end of the btree will constantly get expanded as the maximum
column value increases. But there's no mechanism to make the portion of
the index that covers your original oldest timestamp go away, even if
the entry itself has gone away.

Reindexing fixes this by constructing a whole new index from scratch.

We'd like plain vacuum to remove empty pages too, but doing so without
locking out concurrent accesses to the index is a tricky problem.
Perhaps it will get fixed in 7.4 ...

regards, tom lane