Andreas Terrius <andreas.terrius@gmail.com> writes:
> --Query 2, Does not hit index
> SELECT * FROM idxtbl
> where ( current_timestamp is null or btime < current_timestamp)
> AND ( current_timestamp - INTERVAL '7 DAYS' is null or btime >
> current_timestamp - INTERVAL '7 DAYS')
> --Query 4, Hit Index
> SELECT * FROM idxtbl
> where (10 is null or aint < 10)
> AND (20 is null or aint > 20)
> Surprisingly query 4 hits "aint" index while query 2 does not hit "btime"
> index.
The conditions "current_timestamp is null" and "current_timestamp -
INTERVAL '7 DAYS' is null" are not indexable, so it's impossible
to build an indexscan plan for query 2.
"10 is null" and "20 is null" are not indexable either, but in that case
the planner is able to fold those conditions to constant FALSE and then
drop them, leaving just "aint < 10 AND aint > 20" which is indexable.
> As to why my query is designed like this, it's because I have a stored
> procedure that sort of similar with query 2 .
It'd be a mistake to draw any conclusions about what's happening inside
a stored procedure from these examples ... especially if the procedure's
query is only "sort of similar". Parameter references don't act quite
like either constants or CURRENT_TIMESTAMP so far as the planner is
concerned.
But I think pulling out the is-null tests into procedural logic choosing
which query to run would be wise. Those are contorting the queries
completely in the service of corner cases.
regards, tom lane