Re: BUG #14237: Terrible performance after accidentally running 'drop index' for index still being created

dwaller(at)microsoft(dot)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