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.
Note: please patch against CVS head, as that code got rewritten since
7.4.
regards, tom lane