Re: Drop big index

2012/2/15 Vojtěch Rylko <vojta.rylko@seznam.cz>:
> this query performed so long and blocked table so I had to interrupt it. Is
> there any way how to drop large indexes in non-blocking or /faster/ way?

Usually the problem is not with the size of the index -- but some
other running transactions that hold a read lock on the table, and
preventing the DROP INDEX from getting an exclusive lock. If the
exclusive lock is granted, the drop index is usually very fast.

Run 'select * from pg_stat_activity' and see if there are any "<IDLE>
in transaction" connections. It's normal to have these for a second or
few, but longer idle transactions usually indicate an application bug
-- it started a transaction, but "forgot" to rollback or commit. These
are problematic for this exact reason -- locks can't be released until
the transaction finishes.

Regards,
Marti