Re: Should I implement DROP INDEX CONCURRENTLY?

On Wed, Aug 24, 2011 at 1:04 PM, Daniel Farina <daniel(at)heroku(dot)com> wrote:
> On Wed, Aug 24, 2011 at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Assuming the issue really is the physical unlinks (which I agree I'd
>> like to see some evidence for), I wonder whether the problem could be
>> addressed by moving smgrDoPendingDeletes() to after locks are released,
>> instead of before, in CommitTransaction/AbortTransaction.  There does
>> not seem to be any strong reason why we have to do that before lock
>> release, since incoming potential users of a table should not be trying
>> to access the old physical storage after that anyway.
>
> Alright, since this concern about confirming the expensive part of
> index dropping has come up a few times but otherwise the waters are
> warm, I'll go ahead and do some work to pin things down a bit before
> we continue working on those assumptions.
>

This suspicion seems to be proven correct; there came an opportunity
where we were removing some indexes on a live system and I took the
opportunity to carefully control and time the process. There's not
much relationship between size of the index and the delay, but the
pauses are still very real. On the other hand, the first time this was
noticed there was significantly higher load.

I'd still like to do something to solve this problem, though: even if
the time-consuming part of the process is not file unlinking, it's
clearly something after the AccessExclusiveLock is acquired based on
our other measurements.

Back to the drawing board...

--
fdr