From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Bill Chandler <billybobc1210@yahoo.com>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: Question on REINDEX
Date: Mon, 18 Apr 2005 16:13:54 -0400
Message-ID: <25363.1113855234@sss.pgh.pa.us> (raw)
In-Reply-To: <200504181233.56784.josh@agliodbs.com>
References: <20050418192142.84419.qmail@web51405.mail.yahoo.com>
<200504181233.56784.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com, billybobc1210@yahoo.com
Subject: Re: Question on REINDEX
In-Reply-To: <25363.1113855234@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox