From: Steven Flatt <steven.flatt@gmail.com>
To: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Wed, 8 Aug 2007 15:12:44 -0400
Message-ID: <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com> (raw)
In-Reply-To: <38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
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.
Steve
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: steven.flatt@gmail.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.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