Re: When/if to Reindex

On Jul 18, 2007, at 1:08 PM, Steven Flatt wrote:

> Some background: we make extensive use of partitioned tables.  In
> fact, I'm
> really only considering reindexing partitions that have "just
> closed".  In
> our simplest/most general case, we have a table partitioned by a
> timestamp
> column, each partition 24 hours wide.  The partition will have an
> index on
> the timestamp column as well as a few other indexes including a
> primary key

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...