Hello Ive been running into an issue with postgresql not hitting index on select queries.
Below is the sql query I used to test my issue.
CREATE TABLE idxtbl (
id BIGINT,
aint BIGINT,
btime TIMESTAMPTZ,
ctext TEXT,
dbool BOOLEAN,
PRIMARY KEY(id)
);
--Inserted 10 mil random data
/* TEST TIMESTAMP INDEX */
CREATE INDEX ON idxtbl(btime)
--Query 1, Hit Index
SELECT * from idxtbl
WHERE btime < current_timestamp
AND btime > current_timestamp - INTERVAL '7 DAYS'
--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')
/*
Since query 2 does not hit index
So I tested it again with another column (aint), which you can see below
*/
/* TEST INTEGER INDEX */
CREATE INDEX ON idxtbl(aint)
--Query 3, Hit Index
SELECT * from idxtbl
WHERE aint < 10
AND aint > 20
--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.
As to why my query is designed like this, it's because I have a stored procedure that sort of similar with query 2 .You can replace CURRENT_TIMESTAMP with my variable which will passed from the application side. The general idea is that if the variable is null then the filter for that column will be omitted. At first I used dynamic sql (IF ELSE) and EXECUTE but later changes the query to something similar to my example because it's a bit more cleaner (easier to read) than the dynamic sql approach.
Why does query 2 not hit any indexes ?
Do I need to go back to doing dynamic sql to resolve this ?