From: | Dave Page <dpage(at)postgresql(dot)org> |
---|---|
To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
Cc: | pgadmin-hackers(at)postgresql(dot)org, Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Subject: | Re: A bunch of minor issues |
Date: | 2007-09-26 13:21:18 |
Message-ID: | 46FA5CCE.2010408@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2007-09-26 13:39:34 | Re: A bunch of minor issues |
Previous Message | Heikki Linnakangas | 2007-09-26 13:16:40 | Re: A bunch of minor issues |