Re: Feature request: smarter use of conditional indexes

On 3/3/04 6:53 PM, Tom Lane wrote:
> John Siracusa <siracusa(at)mindspring(dot)com> 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