X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 408CDD1B511 for ; Sun, 7 Mar 2004 02:29:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 67081-10 for ; Sat, 6 Mar 2004 22:29:30 -0400 (AST) Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [204.127.198.35]) by svr1.postgresql.org (Postfix) with ESMTP id 090E6D1B4ED for ; Sat, 6 Mar 2004 22:29:26 -0400 (AST) Received: from [10.0.1.2] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (rwcrmhc11) with SMTP id <20040307022928013002eirpe>; Sun, 7 Mar 2004 02:29:28 +0000 Mime-Version: 1.0 (Apple Message framework v612) Content-Transfer-Encoding: 7bit Message-Id: <41C473B6-6FDF-11D8-9156-000A95BA4506@mindspring.com> Content-Type: text/plain; charset=US-ASCII; format=flowed To: Postgres Performance From: John Siracusa Subject: Re: Feature request: smarter use of conditional indexes Date: Sat, 6 Mar 2004 21:29:27 -0500 X-Mailer: Apple Mail (2.612) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/82 X-Sequence-Number: 5938 On 3/3/04 6:53 PM, Tom Lane wrote: > John Siracusa writes: >> Given an index like this: >> CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL; >> and a query like this: >> SELECT * FROM t1 WHERE c1 = 123; >> I'd like the planner to be smart enough to use an index scan using >> i1. > > Send a patch ;-) How does this look? It seems to do what I want without horribly breaking anything as far as I can tell. I ran "make check" and got the same result as I did before my changes (5 failures in OS X 10.3.2). But then, I also got the same result when I wasn't even checking to make sure that both clauses were looking at the same variable :) I'm not sure how to add a test for this particular change either. % cvs diff src/backend/optimizer/path/indxpath.c Index: src/backend/optimizer/path/indxpath.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/optimizer/path/indxpath.c,v retrieving revision 1.156 diff -r1.156 indxpath.c 1032a1033,1055 > { > /* One last chance: "var = const" or "const = var" implies "var is not null" */ > if (IsA(predicate, NullTest) && > ((NullTest *) predicate)->nulltesttype == IS_NOT_NULL && > is_opclause(clause) && op_strict(((OpExpr *) clause)->opno) && > length(((OpExpr *) clause)->args) == 2) > { > leftop = get_leftop((Expr *) clause); > rightop = get_rightop((Expr *) clause); > > /* One of the two arguments must be a constant */ > if (IsA(rightop, Const)) > clause_var = leftop; > else if (IsA(leftop, Const)) > clause_var = rightop; > else > return false; > > /* Finally, make sure "var" is the same var in both clauses */ > if (equal(((NullTest *) predicate)->arg, clause_var)) > return true; > } > 1033a1057 > }