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