Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX

Wes <wespvp(at)msg(dot)bt(dot)com> writes:
> I'm running 8.1.4. Assume I have exclusive access to the DB.

You really ought to update to 8.1.something-newer, but I digress.

> 1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
> DATABASE.

No, not if you don't mind exclusive locks. DROP together with CREATE
INDEX CONCURRENTLY might be nicer if you were trying to do this without
completely shutting down the DB, but if you aren't running normal
operations then just use REINDEX.

> 2. I'm assuming REINDEX would avoid the time involved in recreating the
> foreign key constraints?

Right, that's one reason to do it that way.

> 3. With a REINDEX DATABASE, how can I monitor progress?

It should give you a NOTICE after each table.

BTW, what have you got maintenance_work_mem set to?

regards, tom lane