Re: Fastest way to drop an index?

On Tue, Feb 3, 2009 at 5:18 AM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> I have a table with two fields:
>
> user_id
> col2
>
> There is quite a pivotal SQL for our site that issues the query:
>
> ...WHERE user_id = 'xyz' and col2 = 'xyz'
>
> Until now, I have had two indexes, one for user_id and one for col2.
>
> Now, I built a compound index concurrently (user_id, col2). After an
> error the previous, this has now built, but the query above is still
> using individual indexes of olden times.
>
> So I want to drop the index on col2 alone. (Col2 is never used by
> itself, so no use keeping the index. We only created it originally
> because an index on this field alone would be smaller than a compound
> index, and because PG manual said individual indexes tend to work
> better.)

PostgreSQL can use > 1 index at a time, so it's possible that this
index IS getting used. Or does explain rule that out? IF you have a
maintenance window, you can test how the db works with different
indexes by dropping them in a transaction then rolling back. Note
that this locks the table for most other users, so don't do it midday
for a half an hour or so.

begin
drop index index1;
explain analyze select query goes here
rollback;

> But when I try to drop the col2 index, it takes forever, and eats up a
> lot of memory to the extent that all other stuff stops. Why should
> dropping an index be so tedious?

It shouldn't. Is this database under a very heavy load when you're
trying to remove the index? Are you sure the drop index is what's
eating up the memory and you're not just looking at memory usage due
to shared memory allocations?