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.
I've actually mentioned this one before in that of all the partial indexes I have, almost all of then are a WHERE x IS NOT NULL format. I don't know if that's a common use, but if it is, then maybe it's worth just adding the knowledge for IS NOT NULL...
The other thing is that at the moment, cascading foreign keys will not use partial indexes even if they match the predicate. Maybe an IS NOT NULL hack will help there...
Chris
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
