I have a database with a column called timestamp of type timestamp without timezone, there is a btree index on this column.
I have a query as below
SELECT
*
FROM
employee
WHERE
timestamp >= timestamp '2020-01-27 13:24:09'
However, the explain analyze shows that the index on the column timestamp is not used:
Seq Scan on employee (cost=0.00..5498.73 rows=34377 width=381) (actual time=0.016..37.944 rows=34251 loops=1)
Filter: ("timestamp" >= '2020-01-27 13:24:09'::timestamp without time zone)
Rows Removed by Filter: 21167
Planning Time: 0.255 ms
Execution Time: 40.277 ms
If i change the query with filter condition including a timestamp which is 1 month instead of 1 year (as above) then the index on the column is used.
Bitmap Heap Scan on employee (cost=59.51..4510.25 rows=2996 width=381) (actual time=2.164..5.204 rows=2958 loops=1)
Recheck Cond: ("timestamp" >= '2020-12-27 13:24:09'::timestamp without time zone)
Heap Blocks: exact=208
-> Bitmap Index Scan on timestamp_salary_idx (cost=0.00..58.76 rows=2996 width=0) (actual time=2.113..2.114 rows=2958 loops=1)
Index Cond: ("timestamp" >= '2020-12-27 13:24:09'::timestamp without time zone)
Planning Time: 0.195 ms
Execution Time: 5.485 ms
Why is the index not used for queries with a range past 1 year?
timestamp '2020-01-27 13:24:09'returns more than 50% of the rows of the table. A Seq Scan is more efficient in that case.timestampis quite a bad name for a column. For one, because it's also a keyword. But more importantly: it doesn't document anything. Is that a "created at" timestamp? A "due at" timestamp? An "expiration" timestamp? A "last updated at" timestamp?