I have an IO congested database (PostgreSQL 7.2) with too many
(overlapping) indexes, so the obvious solution is to drop them.
DROP INDEX seems to want to take several minutes to complete, which
causes a backup of clients and me to eventually abort the process to let
all the backed up queries go through.
The real question is why does DROP INDEX take more than a couple of
seconds to complete? It is not held up by locked. The indexes are about
5GB in size. I assume PostgreSQL is trying to read the entire index off
disk prior to removal or something equally strange. Is this necessary?