On 8/8/07, Vivek Khera <[EMAIL PROTECTED]> 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
