Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C86532E004C for ; Tue, 26 Feb 2008 16:57:58 -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 59727-10 for ; Tue, 26 Feb 2008 16:57:53 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.178]) by postgresql.org (Postfix) with ESMTP id 15DE62E0030 for ; Tue, 26 Feb 2008 16:57:52 -0400 (AST) Received: by el-out-1112.google.com with SMTP id r27so2180237ele.5 for ; Tue, 26 Feb 2008 12:57:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=QP3rq7fQOBFtf7vQQm7UE5v0BBi4yR8q+00ngBn8Fd8=; b=N2wFKY2VBO9WOWbeaEXxepTBX3uUOkRoXYZi3IXKMdwMkHX0kn3oCxtG5IA8W/pTuRxwDaKrFrPUQJJo0Yst8c/mbHBxVSNAU1493JJFZSf7fRXTaV8MhwVNmOPpnZ73OOqZbPum3E4UnoxPg+QBCan1Lx/8cuYPW4ncgSU2wC0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=SmhM7sUuTiwSyemQZEf2WZH5xmnl6+xXK+TBiCkSXtu2Yu4E8tS5o+hXjGm4lVOHUEfk3l5u2LMmMdJahsMM6IfHKE2QXMobSHc3uMFryn+fwr/FoNZ1/acuC1diKKeDqUOyMCP6pAbPG2Eu2VKp2o279AnxX3tD57U97NnOBEw= Received: by 10.114.156.1 with SMTP id d1mr6145459wae.120.1204059471290; Tue, 26 Feb 2008 12:57:51 -0800 (PST) Received: by 10.114.133.6 with HTTP; Tue, 26 Feb 2008 12:57:51 -0800 (PST) Message-ID: Date: Tue, 26 Feb 2008 14:57:51 -0600 From: "Scott Marlowe" To: "Peter Koczan" Subject: Re: disabling an index without deleting it? Cc: pgsql-performance In-Reply-To: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/351 X-Sequence-Number: 28893 On Tue, Feb 26, 2008 at 2:46 PM, Peter Koczan 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.