To: Bill Moran <wmoran@collaborativefusion.com>
cc: "Steven Flatt" <steven.flatt@gmail.com>, "Vivek Khera" <vivek@khera.org>,
	"Pgsql performance" <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex 
In-reply-to: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com> 
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
	<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
	<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
	<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
Comments: In-reply-to Bill Moran <wmoran@collaborativefusion.com>
	message dated "Wed, 08 Aug 2007 15:27:57 -0400"
Date: Wed, 08 Aug 2007 21:51:48 -0400
Message-ID: <11801.1186624308@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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