On 3/3/04 6:53 PM, Tom Lane wrote:
> John Siracusa <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
