Timestamp index not being hit

From: Andreas Terrius <andreas(dot)terrius(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Timestamp index not being hit
Date: 2017-01-13 09:52:59
Message-ID: CAHZG=res3aP9+Te5-cXias625ZpfB2Ga7nF=5CmokH__A6r13A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2017-01-13 15:42:07 Re: temporarily disable autovacuum on a database or server ?
Previous Message Merlin Moncure 2017-01-12 22:52:05 Re: efficiently migrating 'old' data from one table to another