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 79A1DD1D6CC for ; Sat, 6 Mar 2004 20:51:50 +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 23891-01 for ; Sat, 6 Mar 2004 16:51:49 -0400 (AST) Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56]) by svr1.postgresql.org (Postfix) with ESMTP id D0BB1D1D550 for ; Sat, 6 Mar 2004 16:51:46 -0400 (AST) Received: from [10.0.1.2] (h000393e3ce55.ne.client2.attbi.com[24.91.235.158]) by comcast.net (sccrmhc12) with SMTP id <2004030620514801200fcqlpe>; Sat, 6 Mar 2004 20:51:48 +0000 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Sat, 06 Mar 2004 15:51:47 -0500 Subject: Re: Feature request: smarter use of conditional indexes From: John Siracusa To: Postgres Performance Message-ID: In-Reply-To: <19341.1078358036@sss.pgh.pa.us> Mime-version: 1.0 Reply-To: siracusa@mindspring.com Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/74 X-Sequence-Number: 5930 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 ;-) > > The routine you want to teach about this is pred_test_simple_clause() in > src/backend/optimizer/path/indxpath.c. ISTM that it's legitimate to > conclude that "foo IS NOT NULL" is implied by "foo op anything" or > "anything op foo" if the operator is marked strict. Gack, C is not my forte... So...I'm noodling around in pred_test_simple_clause() and my test query of: SELECT * FROM t1 WHERE c1 = 123; lands me in pred_test_simple_clause() with a "predicate" with a NodeTag of NullTest, and a "clause" with a NodeTag of OpExpr. The clause "rightop" IsA() Const. So far, it seems to make sense. It's comparing the clause "c1 = 123" with the predicate on the "i1" index ("IS NOT NULL") to see if one implies the other. But now I'm stuck, because IsA(predicate, NullTest) is *also* true if the index i1 is dropped and index i2 is created like this: CREATE UNIQUE INDEX i2 ON t1 (c1) WHERE c1 IS NOT NULL; IOW, both "IS NOT NULL" and "IS NULL" lead to IsA(predicate, NullTest) being true. I found this, which looked promising: typedef enum BoolTestType { IS_TRUE, IS_NOT_TRUE, IS_FALSE, IS_NOT_FALSE, IS_UNKNOWN, IS_NOT_UNKNOWN } BoolTestType; typedef struct BooleanTest { Expr xpr; Expr *arg; /* input expression */ BoolTestType booltesttype; /* test type */ } BooleanTest; But then I realized that "predicate" is "Expr *" inside the pred_test_simple_clause() function, and Expr seems only to have a single field, which is tested by IsA() typedef struct Expr { NodeTag type; } Expr; So apparently all I can do is find out if it's a null test, but not if it is specifically "IS NOT NULL" Now I'm stuck, and thinking that I'd have to modify more than pred_test_simple_clause() to make this work. Any additional pointers? :) -John