Re: Index bloat problem?

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index bloat problem?
Date: 2005-04-21 17:47:24
Message-ID: 60fyxk2f9v.fsf@dba2.int.libertyrms.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

josh(at)agliodbs(dot)com (Josh Berkus) writes:
> Bill,
>
>> What about if an out-of-the-ordinary number of rows
>> were deleted (say 75% of rows in the table, as opposed
>> to normal 5%) followed by a 'VACUUM ANALYZE'?  Could
>> things get out of whack because of that situation?
>
> Yes. You'd want to run REINDEX after and event like that. As you should now.

Based on Tom's recent comments, I'd be inclined to handle this via
doing a CLUSTER, which has the "triple heroism effect" of:

a) Reorganizing the entire table to conform with the relevant index order,
b) Having the effect of VACUUM FULL, and
c) Having the effect of REINDEX

all in one command.

It has all of the "oops, that blocked me for 20 minutes" effect of
REINDEX and VACUUM FULL, but at least it doesn't have the effect
twice...
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Josh Berkus 2005-04-21 17:50:29 Re: Index bloat problem?
Previous Message Josh Berkus 2005-04-21 17:44:48 Re: Index bloat problem?