Re: Problem with index in OR'd expression

Tom Lane wrote:
> postgresql(dot)org(at)tgice(dot)com 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