Re: when to reindex?

At 2:02p -0400 on Fri, 06 Jun 2008, Jon Roberts wrote:
> Based on this, I have the fillfactor set lower than the default 90 but
> this will fill up and it will run slower over time. I want to automate
> the reindex process but only reindex when needed. I have a pretty large
> database so I can't reindex everything regardless if it needs it or not.

Not knowing the core Postgres internals, this is perhaps a naive
question: would it be possible to engineer REINDEX so that it could do
the job in increments? Rather than recreating the entire index at once,
say only create the index for 2% of the table? I suppose analogous to a
partial index somehow?

Or, assuming the REINDEX is for speed/bloat, not for corruption, perhaps
an option to use the old index as a basis, rather than scanning the
entire table multiple times as with a CREATE INDEX CONCURRENTLY.

I do see REINDEX CONCURRENTLY on the TODO list though. Not exactly the
same idea, but ...

Kevin