Обсуждение: [GENERAL] Timestamp index not being hit
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 ?
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