Re: disabling an index without deleting it?

On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan <pjkoczan(at)gmail(dot)com> wrote:
> This might be a weird question...is there any way to disable a
> particular index without dropping it?
>
> There are a few queries I run where I'd like to test out the effects
> of having (and not having) different indexes on particular query plans
> and performance. I'd really prefer not to have to drop and ultimately
> recreate a particular index, as some of the data sets are quite large.
>
> So, is there any way to do this, or at least mimic this sort of behavior?

The brick to the head method would use set enable_indexscan = off;
However, you can delete an index without actually deleting it like so:

begin;
drop index abc_dx;
select ....
rollback;

and viola, your index is still there. note that there are likely some
locking issues with this, so be careful with it in production. But on
a test box it's a very easy way to test various indexes.