Tom Lane wrote:
postgresql.org@xxxxxxxxx writes:
I would submit that in that situation, it would be
reasonable for a user to expect my suggested syntax to still use the
indicated indexes.
The only thing that will make that work is if "indexed_col IS NULL" were
an indexable condition, which it isn't because the PG index API only
supports "indexed_col operator something" as an indexable condition
(IS NULL is not an operator, and even if it were, there's no "something"
on its righthand side). Fixing this has been on the radar screen for
awhile, but it's not done, largely for lack of agreement about a
reasonably clean way to change that API.
Sorry to keep this issue alive even longer, Tom, but I think I may've
been unclear with my example.
I was referring to the situation where one has this in a WHERE clause:
((vConstant IS NULL) OR (Table.IndexedCol = vConstant))
where vConstant is a *constant* parameter in a pl/pgsql function.
In the latest versions (8.1 *or* 8.2), would you expect this to
successfully use the index on Table.IndexedCol and not have PG be
confused (into a sequential scan) by the (vConstant IS NULL) expression?
As I indicated, I'm currently running 8.0.x, and am wondering whether it
would be worth the effort to upgrade to 8.1 or 8.2 (Gentoo doesn't yet
have PG at 8.2, and I'm a bit lazy with installing things outside of
Portage) to solve this issue or whether I should just enable a
workaround for now and keep an eye on future releases for a better
solution to this problem.
Thanks again,
John
