Heikki Linnakangas wrote:
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...
Would be even nicer to include in the server... maybe REINDEX
CONCURRENTLY...
I know I know, no new features at this point :).
Indeed :-)
/D
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
