Re: When/if to Reindex

On 8/8/07, Vivek Khera <vivek(at)khera(dot)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