5

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?

5
  • 4
    The condition 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. Commented Jan 27, 2021 at 13:17
  • 3
    Totally unrelated, but timestamp is 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? Commented Jan 27, 2021 at 13:18
  • @a_horse_with_no_name even if i filter with conditions which result into 5% of rows, the index is not used Commented Jan 27, 2021 at 13:24
  • 1
    your second plan shows it returns about 5% of the rows and it does use the index Commented Jan 27, 2021 at 13:27
  • @a_horse_with_no_name it uses index for timestamp ranges which are more recent like 1 day ago, 1 week ago or 1 month ago but if you query for ranges 1 year ago, 2 years ago then it doesn't use irrespective of the % of results returned Commented Jan 27, 2021 at 15:05

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.