Re: Importance of re-index

On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote:
> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
> > In addition to making sure databases are vacuumed regularly, it is worth
> > running REINDEX on tables that see a lot of updates (or insert/deletes).
> >
> > Running REINDEX on a regular basis will keep the indexes compacted and
> > can noticeably improve the database performance.
> >
> > The other benefit is that the disk space taken by your database can be
> > significantly reduced.
> >
> > This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
> >
> > FWIW, in my experience it is DEFINITELY worth reindexing regularly.
>
> But note that reindex is one of those "invasive" commands that may cause
> problems for certain types of 24/7 operations, while vacuum is meant to
> run concurrently almost any time of day. Reindex was originally
> designed to fix broken indexes, and, at least in earlier encarnations,
> should something stop it in the middle of reindexing I believe it is
> possible to be left with no index.
>
> It's got its uses, but it's got its issues as well.

And if you're vacuuming frequently enough, there shouldn't be that much
need to reindex.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461