Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 56F832E0043 for ; Tue, 26 Feb 2008 18:22:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87255-09 for ; Tue, 26 Feb 2008 18:22:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 81D442E0032 for ; Tue, 26 Feb 2008 18:22:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1QMMegA009051; Tue, 26 Feb 2008 17:22:40 -0500 (EST) To: "Joshua D. Drake" cc: "Scott Marlowe" , "Peter Koczan" , pgsql-performance Subject: Re: disabling an index without deleting it? In-reply-to: <20080226130339.64bd4ec3@commandprompt.com> References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> <20080226130339.64bd4ec3@commandprompt.com> Comments: In-reply-to "Joshua D. Drake" message dated "Tue, 26 Feb 2008 13:03:39 -0800" Date: Tue, 26 Feb 2008 17:22:40 -0500 Message-ID: <9050.1204064560@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/356 X-Sequence-Number: 28898 "Joshua D. Drake" writes: > "Scott Marlowe" 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. regards, tom lane