On Fri, Jun 6, 2008 at 9:36 AM, Roberts, Jon <Jon.Roberts@xxxxxxxxxxx> wrote: > In Oracle, there is a method to determine when it is advisable to > rebuild indexes. Are there any guidelines for this in PostgreSQL? > > I found this but it doesn't indicate at which point an index should be > rebuilt other than corruption. > > http://www.postgresql.org/docs/8.3/interactive/routine-reindex.html PostgreSQL isn't quite as finicky about indexes as oracle can be. If you've ever rebuilt a table and forgot to rebuild the indexes in oracle you know what I'm talking about. PostgreSQL generally takes care of indexes pretty well. There are two reasons to reindex in pgsql. The first one is a corrupted index. Note that if you're running on quality hardware, and a properly configured db (i.e. fsync isn't off, etc...) then you shouldn't get corrupted indexes. If you get them quite often, then you've got worse problems than just figuring out when to reindex. The second common situation that requires a reindex is when you suffer from index bloat. This can be caused by certain out of the ordinary update patterns and by vacuum full.
