From: Scott Marlowe <scott.marlowe@gmail.com>
To: Peter Koczan <pjkoczan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: disabling an index without deleting it?
Date: Tue, 26 Feb 2008 14:57:51 -0600
Message-ID: <dcc563d10802261257h6abfbc92x62a7569b8610ecc4@mail.gmail.com> (raw)
In-Reply-To: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com>
References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com>
On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan <pjkoczan@gmail.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, pjkoczan@gmail.com
Subject: Re: disabling an index without deleting it?
In-Reply-To: <dcc563d10802261257h6abfbc92x62a7569b8610ecc4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox