Thread: Slow DROP INDEX

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?

Rod Taylor <pg@rbt.ca> writes:
> The real question is why does DROP INDEX take more than a couple of
> seconds to complete? It is not held up by locked.

AFAICS it shouldn't take any time to complete.  I think you're mistaken
and it is blocking on a lock (it will want exclusive lock on the table).
Everyone else will then queue up behind it.

IIRC there is not any easy way to check this in 7.2 unfortunately :-(
but you could try looking for idle-in-transaction backends.
        regards, tom lane

On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > The real question is why does DROP INDEX take more than a couple of
> > seconds to complete? It is not held up by locked.
> 
> AFAICS it shouldn't take any time to complete.  I think you're mistaken
> and it is blocking on a lock (it will want exclusive lock on the table).
> Everyone else will then queue up behind it.

I not convinced it is waiting on a lock. The queries on that table are
very short (couple of milliseconds) -- but there are a ton of them. All
backends appear to be idle (pg_stat_activity with command shown) when we
start the drop and shortly after huge backups occur.

Based on my experience, "drop index" is waiting for a chance to place the
lock. If all those queries are within the same connection, "drop index" has
to wait until that connection disconnected.

John Li

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Rod Taylor
Sent: Monday, February 16, 2004 10:56 AM
To: Tom Lane
Cc: PostgreSQL Development
Subject: Re: [HACKERS] Slow DROP INDEX

On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > The real question is why does DROP INDEX take more than a couple of
> > seconds to complete? It is not held up by locked.
>
> AFAICS it shouldn't take any time to complete.  I think you're mistaken
> and it is blocking on a lock (it will want exclusive lock on the table).
> Everyone else will then queue up behind it.

I not convinced it is waiting on a lock. The queries on that table are
very short (couple of milliseconds) -- but there are a ton of them. All
backends appear to be idle (pg_stat_activity with command shown) when we
start the drop and shortly after huge backups occur.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Rod Taylor <pg@rbt.ca> writes:
> I not convinced it is waiting on a lock. The queries on that table are
> very short (couple of milliseconds) -- but there are a ton of them. All
> backends appear to be idle (pg_stat_activity with command shown) when we
> start the drop and shortly after huge backups occur.

Well, you could prove the point by attaching to the backend with a
debugger and getting a stack trace ... if it's not waiting for a lock,
that would give us some idea what it is doing ...
        regards, tom lane

>Rod Taylor
> On Mon, 2004-02-16 at 13:03, Tom Lane wrote:
> > Rod Taylor <pg@rbt.ca> writes:
> > > The real question is why does DROP INDEX take more than a couple
of
> > > seconds to complete? It is not held up by locked.
> >
> > AFAICS it shouldn't take any time to complete.  I think you're
mistaken
> > and it is blocking on a lock (it will want exclusive lock on the
table).
> > Everyone else will then queue up behind it.
> 
> I not convinced it is waiting on a lock. The queries on that table are
> very short (couple of milliseconds) -- but there are a ton of them.
All
> backends appear to be idle (pg_stat_activity with command shown) when
we
> start the drop and shortly after huge backups occur.

Could it be waiting on a lock held on something other than the target
table itself?

A system catalog table, either for access right or optimization? Maybe
the drop index lock is the same as the drop table lock (and perhaps it
shouldn't be).

May not be the case...but the answer should be interesting.

Hope it helps, Simon Riggs