Dave Page wrote: > REINDEX is similar to a drop and recreate of the index in that the index > contents are rebuilt from scratch. However, the locking considerations > are rather different. REINDEX locks out writes but not reads of the > index's parent table. It also takes an exclusive lock on the specific > index being processed, which will block reads that attempt to use that > index. In contrast, DROP INDEX momentarily takes exclusive lock on the > parent table, blocking both writes and reads. The subsequent CREATE > INDEX locks out writes but not reads; since the index is not there, no > read will attempt to use it, meaning that there will be no blocking but > reads may be forced into expensive sequential scans. Another important > point is that the drop/create approach invalidates any cached query > plans that use the index, while REINDEX does not.
So the advantage is that drop+create will allow all reads to run concurrently, though they might have to use sequential scans. Hmm, I wonder if a CREATE+DROP+rename would be even better. Could use CONCURRENT-mode in the create as well to allow concurrent writes... I know I know, no new features at this point :). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
