Re: When/if to Reindex - Mailing list pgsql-performance

From Tom Lane
Subject Re: When/if to Reindex
Date
Msg-id 11801.1186624308@sss.pgh.pa.us
Whole thread Raw
In response to Re: When/if to Reindex  (Bill Moran <wmoran@collaborativefusion.com>)
Responses Re: When/if to Reindex  ("Steven Flatt" <steven.flatt@gmail.com>)
List pgsql-performance
Bill Moran <wmoran@collaborativefusion.com> writes:
> In response to "Steven Flatt" <steven.flatt@gmail.com>:
>> What's interesting is that an insert-only table can benefit significantly
>> from reindexing after the table is fully loaded.

> I've had similar experience.  One thing you didn't mention that I've noticed
> is that VACUUM FULL often bloats indexes.  I've made it SOP that
> after application upgrades (which usually includes lots of ALTER TABLES and
> other massive schema and data changes) I VACUUM FULL and REINDEX (in that
> order).

Actually, if that is your intent then the best plan is: drop indexes,
VACUUM FULL, create indexes from scratch.  A huge proportion of VACUUM
FULL's time goes into updating the indexes, and that work is basically
wasted if you are going to reindex afterwards.

CLUSTER is a good substitute for V.F. partly because it doesn't try to
update the indexes incrementally, but just does the equivalent of
REINDEX after it's reordered the heap.

I'd make the same remark about Steven's case: if possible, don't create
the indexes at all until you've loaded the table fully.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Bryan Murphy"
Date:
Subject: Re: How to ENABLE SQL capturing???
Next
From: justin
Date:
Subject: mid 2007 "best bang for the buck" hardware opinions