Andreas Terrius <andreas.terrius@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
