dwaller@microsoft.com writes:
> Summary: While running âcreate index concurrentlyâ on a very large table,
> running âdrop indexâ for the same index caused Postgres to perform terribly
> badly, until the âcreate indexâ server process was killed. I would expect
> that the âdrop indexâ would either fail immediately, or wait, without
> performance impact, until the âcreate index concurrentlyâ had completed.
Hmm, the DROP INDEX should have blocked waiting for an exclusive lock on
the table, and then other queries on the table should have queued up
behind that lock request. I think the fact that they didn't just stop
dead probably indicates that when the deadlock checker ran, it concluded
it needed to let them jump the queue and go in front of the DROP INDEX to
avoid a deadlock --- likely because the lock manager could see that the
DROP was blocked by the CREATE INDEX CONCURRENTLY while the latter was
waiting for the other queries to finish. So your slowdown corresponds to
an extra wait of deadlock_timeout ms per query. This is not a bug, but
designed behavior.
> I would expect that running âdrop indexâ while that index is still being
> created would either fail immediately, or wait, without performance impact,
> until the âcreate index concurrentlyâ had completed.
If the DROP's lock request is not allowed to block other incoming requests
for a table lock, the DROP could face indefinite lock starvation. That is
not better. Reducing deadlock_timeout to zero is not going to improve
your overall performance, either.
regards, tom lane
