Skip site navigation (1) Skip section navigation (2)

Re: disabling an index without deleting it?

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, Peter Koczan <pjkoczan(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: disabling an index without deleting it?
Date: 2008-02-27 19:16:58
Message-ID: 1204139818.16886.29.camel@dogma.ljc.laika.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 2008-02-26 at 17:22 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
> >> 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.
> 
> > Wouldn't you also bloat the index?
> 
> No, what makes you think that?  The index won't change at all in the
> above example.  The major problem is, as Scott says, that DROP INDEX
> takes exclusive lock on the table so any other sessions will be locked
> out of it for the duration of your test query.

It may cause catalog bloat though, right?

Regards,
	Jeff Davis


In response to

Responses

pgsql-performance by date

Next:From: Dan KaplanDate: 2008-02-27 19:19:22
Subject: t1.col like '%t2.col%'
Previous:From: Douglas J HunleyDate: 2008-02-27 18:45:11
Subject: Re: questions about CLUSTER

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group