From: Bill Moran <wmoran@collaborativefusion.com>
To: 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, 8 Aug 2007 15:27:57 -0400
Message-ID: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
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
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: wmoran@collaborativefusion.com, steven.flatt@gmail.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
* 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