Re: Feature request: smarter use of conditional indexes - Mailing list pgsql-performance

John Siracusa <siracusa@mindspring.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