Skip site navigation (1) Skip section navigation (2)

Re: Question on REINDEX

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
Cc: Bill Chandler <billybobc1210(at)yahoo(dot)com>,pgsql-perform <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Question on REINDEX
Date: 2005-04-18 20:13:54
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> 1) When is it necessary to run REINDEX or drop/create
>> an index?  All I could really find in the docs is:

> If you need to VACUUM FULL, you need to REINDEX as well.  For example, if you
> drop millions of rows from a table.

That's probably a pretty good rule of thumb.  It's worth noting that
VACUUM FULL tends to actively bloat indexes, not reduce them in size,
because it has to create new index entries for the rows it moves before
it can delete the old ones.  So if a VACUUM FULL moves many rows you
are likely to see the indexes get bigger not smaller.

> Better to up your max_fsm_pages and do regular VACUUMs regularly and 
> frequently so that you don't have to REINDEX at all.

Yes, definitely.  Also consider using CLUSTER rather than VACUUM FULL
when you need to clean up after massive deletions from a table.  It's
not any less intrusive in terms of locking, but it's often faster and it
avoids the index bloat problem (since it effectively does a REINDEX).

			regards, tom lane

In response to

pgsql-performance by date

Next:From: William YuDate: 2005-04-18 20:37:11
Subject: Re: How to improve db performance with $7K?
Previous:From: Bruno Wolff IIIDate: 2005-04-18 19:58:19
Subject: Re: Question on vacuumdb

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group