Re: Problem with index in OR'd expression

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

postgresql(dot)org(at)tgice(dot)com writes:
>    select * from t where c1 = 75000;
>    select * from t where ((0 is null) OR (c1 = 75000));

> The first one properly uses the index on c1, the second does not. 
> Obviously, a human looking at the second one would realize it's 
> essentially identical to the first and properly evaluate the (0 is null) 
> part once (since it's immutable) and then ignore it for the rest of the 
> searching.

Well, you could update --- 8.2 contains code to recognize that the IS
NULL expression is constant, but prior releases do not.

However, if you're hoping to do this:

>    ((vC1 IS NULL) OR (C1 = vC1)) AND
>    ((vC2 IS NULL) OR (C2 = vC2)) ...

you're still gonna lose because those are variables not constants ...

			regards, tom lane