Re: Fastest way to drop an index?

2009/2/3 Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>

> 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.)
>
> 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?
>
> Would appreciate any thoughts on this. Thanks!
>

is the system very write-intensive? Under high writes some DDL operations
are really a pain...
if so, you could try to make up a script which will rewrite data to another
table and switch tables after the operation. but this will require minimal
application downtime.

btw, what's your pg version and basic memory-related settings? is it the
same system that you describe in your other email?

and what is full definition of this table? only two fields? what's the
primary key?

cheers,
Filip

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com