Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 292472E0057 for ; Wed, 27 Feb 2008 19:33:53 -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 33640-02 for ; Wed, 27 Feb 2008 19:33:47 -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 2AF792E004F for ; Wed, 27 Feb 2008 19:33:47 -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 m1RNX1NX012279; Wed, 27 Feb 2008 18:33:01 -0500 (EST) To: "Kevin Grittner" , "Markus Bertheau" , "Joshua D. Drake" , "Peter Koczan" , "Scott Marlowe" , "pgsql-performance" Subject: Re: disabling an index without deleting it? In-reply-to: <10385.1204153217@sss.pgh.pa.us> References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com> <20080226130339.64bd4ec3@commandprompt.com> <9050.1204064560@sss.pgh.pa.us> <684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com><684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com> <13604.1204087729@sss.pgh.pa.us> <47C578A1.EE98.0025.0@wicourts.gov> <10385.1204153217@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 27 Feb 2008 18:00:17 -0500" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <12273.1204155150.0@sss.pgh.pa.us> Date: Wed, 27 Feb 2008 18:33:01 -0500 Message-ID: <12278.1204155181@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/387 X-Sequence-Number: 28929 ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <12273.1204155150.1@sss.pgh.pa.us> I wrote: > In the particular case at hand, a planner hook to make it ignore the > index is a far better solution anyway... Just as proof of concept, a quick-and-dirty version of this is attached. It works in 8.3 and up. Sample (after compiling the .so): regression=# load '/home/tgl/pgsql/planignoreindex.so'; LOAD regression=# explain select * from tenk1 where unique1 = 42; QUERY PLAN ----------------------------------------------------------------------------- Index Scan using tenk1_unique1 on tenk1 (cost=0.00..8.27 rows=1 width=244) Index Cond: (unique1 = 42) (2 rows) regression=# set ignore_index TO 'tenk1_unique1'; SET regression=# explain select * from tenk1 where unique1 = 42; QUERY PLAN --------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..483.00 rows=1 width=244) Filter: (unique1 = 42) (2 rows) regression=# regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: application/octet-stream Content-ID: <12273.1204155150.2@sss.pgh.pa.us> Content-Description: planignoreindex.c Content-Transfer-Encoding: base64 I2luY2x1ZGUgInBvc3RncmVzLmgiCgojaW5jbHVkZSAiZm1nci5oIgojaW5jbHVkZSAiY2F0YWxv Zy9uYW1lc3BhY2UuaCIKI2luY2x1ZGUgIm9wdGltaXplci9wbGFuY2F0LmgiCiNpbmNsdWRlICJv cHRpbWl6ZXIvcGxhbm5lci5oIgojaW5jbHVkZSAidXRpbHMvYnVpbHRpbnMuaCIKI2luY2x1ZGUg InV0aWxzL2d1Yy5oIgoKClBHX01PRFVMRV9NQUdJQzsKCnZvaWQJCV9QR19pbml0KHZvaWQpOwp2 b2lkCQlfUEdfZmluaSh2b2lkKTsKCnN0YXRpYyBjaGFyICppZ25vcmVfaW5kZXggPSBOVUxMOwoK c3RhdGljIHZvaWQgbXlfZ2V0X3JlbGF0aW9uX2luZm8oUGxhbm5lckluZm8gKnJvb3QsIE9pZCBy ZWxhdGlvbk9iamVjdElkLAoJCQkJCQkJCSBib29sIGluaHBhcmVudCwgUmVsT3B0SW5mbyAqcmVs KTsKCgovKgogKiBHZXQgY29udHJvbCBkdXJpbmcgcGxhbm5lcidzIGdldF9yZWxhdGlvbl9pbmZv KCkgZnVuY3Rpb24sIHdoaWNoIHNldHMgdXAKICogYSBSZWxPcHRJbmZvIHN0cnVjdCBiYXNlZCBv biB0aGUgc3lzdGVtIGNhdGFsb2cgY29udGVudHMuICBXZSBjYW4gbW9kaWZ5CiAqIHRoZSBzdHJ1 Y3QgY29udGVudHMgdG8gY2F1c2UgdGhlIHBsYW5uZXIgdG8gd29yayB3aXRoIGEgaHlwb3RoZXRp Y2FsCiAqIHNpdHVhdGlvbiByYXRoZXIgdGhhbiB3aGF0J3MgYWN0dWFsbHkgaW4gdGhlIGNhdGFs b2dzLgogKgogKiBUaGlzIHNpbXBsaXN0aWMgZXhhbXBsZSBqdXN0IHJlbW92ZXMgYW55IGluZGV4 IHRoYXQgbWF0Y2hlcyBpZ25vcmVfaW5kZXgKICogYnkgbmFtZS4KICovCnN0YXRpYyB2b2lkCm15 X2dldF9yZWxhdGlvbl9pbmZvKFBsYW5uZXJJbmZvICpyb290LCBPaWQgcmVsYXRpb25PYmplY3RJ ZCwgYm9vbCBpbmhwYXJlbnQsCgkJCQkJIFJlbE9wdEluZm8gKnJlbCkKewoJUmFuZ2VWYXIgICAq cmVscnY7CglPaWQJCQlpbmRleG9pZDsKCUxpc3RDZWxsICAgKmlsaXN0OwoJTGlzdENlbGwgICAq cHJldjsKCgkvKiBEbyBub3RoaW5nIHVubGVzcyBpZ25vcmVfaW5kZXggaGFzIGJlZW4gc2V0ICov CglpZiAoaWdub3JlX2luZGV4ID09IE5VTEwgfHwgKmlnbm9yZV9pbmRleCA9PSAnXDAnKQoJCXJl dHVybjsKCgkvKgoJICogTG9vayB1cCB0aGUgaW5kZXguICBObyBjb21wbGFpbnQgaWYgbm90IGZv dW5kLgoJICoKCSAqIE5vdGU6IGRvaW5nIHRoaXMgbG9va3VwIGFnYWluIGVhY2ggdGltZSBpcyBh IGJpdCBpbmVmZmljaWVudCwgYnV0CgkgKiBmb3IgdGhlIGNvbnRleHRzIGluIHdoaWNoIHRoaXMg cGx1Z2luIHdvdWxkIGJlIHVzZWQsIGl0IHByb2JhYmx5CgkgKiBpc24ndCB3b3J0aCBtYWtpbmcg dGhpcyBmYXN0ZXIuCgkgKi8KCXJlbHJ2ID0gbWFrZVJhbmdlVmFyRnJvbU5hbWVMaXN0KHN0cmlu Z1RvUXVhbGlmaWVkTmFtZUxpc3QoaWdub3JlX2luZGV4KSk7CglpbmRleG9pZCA9IFJhbmdlVmFy R2V0UmVsaWQocmVscnYsIHRydWUpOwoJaWYgKCFPaWRJc1ZhbGlkKGluZGV4b2lkKSkKCQlyZXR1 cm47CgoJLyogSWYgaXQncyBpbiB0aGUgcmVsJ3MgbGlzdCBvZiBpbmRleGVzLCBkZWxldGUgaXQg Ki8KCXByZXYgPSBOVUxMOwoJZm9yZWFjaChpbGlzdCwgcmVsLT5pbmRleGxpc3QpCgl7CgkJSW5k ZXhPcHRJbmZvICppbmQgPSAoSW5kZXhPcHRJbmZvICopIGxmaXJzdChpbGlzdCk7CgoJCWlmIChp bmQtPmluZGV4b2lkID09IGluZGV4b2lkKQoJCXsKCQkJcmVsLT5pbmRleGxpc3QgPSBsaXN0X2Rl bGV0ZV9jZWxsKHJlbC0+aW5kZXhsaXN0LCBpbGlzdCwgcHJldik7CgkJCWJyZWFrOwkJCQkvKiBj YW4ndCBiZSBtb3JlIHRoYW4gMSBtYXRjaCAqLwoJCX0KCQlwcmV2ID0gaWxpc3Q7Cgl9Cn0KCgov KgogKiBfcGdfaW5pdCgpCQkJLSBsaWJyYXJ5IGxvYWQtdGltZSBpbml0aWFsaXphdGlvbgogKgog KiBETyBOT1QgbWFrZSB0aGlzIHN0YXRpYyBub3IgY2hhbmdlIGl0cyBuYW1lIQogKi8Kdm9pZApf UEdfaW5pdCh2b2lkKQp7CgkvKiBHZXQgaW50byB0aGUgaG9va3Mgd2UgbmVlZCB0byBiZSBpbiBh bGwgdGhlIHRpbWUgKi8KCWdldF9yZWxhdGlvbl9pbmZvX2hvb2sgPSBteV9nZXRfcmVsYXRpb25f aW5mbzsKCS8qIE1ha2UgaWdub3JlX2luZGV4IGFjY2Vzc2libGUgdGhyb3VnaCBHVUMgKi8KCURl ZmluZUN1c3RvbVN0cmluZ1ZhcmlhYmxlKCJpZ25vcmVfaW5kZXgiLAoJCQkJCQkJICAgIk5hbWUg b2YgYW4gaW5kZXggdG8gaWdub3JlIiwKCQkJCQkJCSAgICIiLAoJCQkJCQkJICAgJmlnbm9yZV9p bmRleCwKCQkJCQkJCSAgIFBHQ19VU0VSU0VULAoJCQkJCQkJICAgTlVMTCwKCQkJCQkJCSAgIE5V TEwpOwp9CgoKLyoKICogX1BHX2ZpbmkoKQkJCS0gbGlicmFyeSB1bmxvYWQtdGltZSBmaW5hbGl6 YXRpb24KICoKICogRE8gTk9UIG1ha2UgdGhpcyBzdGF0aWMgbm9yIGNoYW5nZSBpdHMgbmFtZSEK ICovCnZvaWQKX1BHX2Zpbmkodm9pZCkKewoJLyogR2V0IG91dCBvZiBhbGwgdGhlIGhvb2tzIChq dXN0IHRvIGJlIHN1cmUpICovCglnZXRfcmVsYXRpb25faW5mb19ob29rID0gTlVMTDsKfQo= ------- =_aaaaaaaaaa0--