Date: Wed, 8 Aug 2007 15:27:57 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: "Steven Flatt" <steven.flatt@gmail.com>
Cc: "Vivek Khera" <vivek@khera.org>,
	"Pgsql performance" <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Message-Id: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
In-Reply-To: <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
	<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
	<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
Organization: Collaborative Fusion
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

In response to "Steven Flatt" <steven.flatt@gmail.com>:

> On 8/8/07, Vivek Khera <vivek@khera.org> wrote:
> >
> > If all you ever did was insert into that table, then you probably
> > don't need to reindex.  If you did mass updates/deletes mixed with
> > your inserts, then perhaps you do.
> >
> > Do some experiments comparing pg_class.relpages for your table and
> > its indexes before and after a reindex.  Decide if the number of
> > pages you save on the index is worth the trouble.  If it shaves off
> > just a handful of pages, I'd vote no...
> 
> 
> What's interesting is that an insert-only table can benefit significantly
> from reindexing after the table is fully loaded.  I had done experiments
> exactly as you suggest (looking at pg_class.relpages), and determined that
> reindexing results in about a 30% space savings for all indexes except the
> PK index.  The PK index (integer based on a sequence) does not benefit at
> all.  By setting fillfactor=100 on the index prior to reindexing, I get
> another 10% space savings on all the indexes.
> 
> Not to mention the general performance improvements when reading from the
> table...
> 
> So, we decided that reindexing partitions after they're fully loaded *was*
> worth it.

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).

Lots of ALTER TABLEs seem to bloat the database size considerably, beyond
what normal VACUUM seems to fix.  A FULL seems to fix that, but it appears
to bloat the indexes, thus a REINDEX helps.

I would expect that setting fillfactor to 100 will encourage indexs to bloat
faster, and would only be recommended if you didn't expect the index contents
to change?

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023