From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: Steven Flatt <steven.flatt@gmail.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Wed, 08 Aug 2007 21:51:48 -0400
Message-ID: <11801.1186624308@sss.pgh.pa.us> (raw)
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>
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
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, wmoran@collaborativefusion.com, steven.flatt@gmail.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <11801.1186624308@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