How to add index on extract() in postgresql

user_519333 0
Viewed 948

I'm using a query with following WHERE condition in postgres database:

WHERE extract(EPOCH FROM created_at) BETWEEN 1511132400 AND 1511737199

How can I add the index so query with this condition uses it? Creating index on created_at does not help and the index is not used.

Currently the EXPLAIN on a query like that gives:

Filter: (date_part('epoch'::text, created_at) >= '1511132400'::double precision) AND (date_part('epoch'::text, created_at) <= '1511737199'::double precision)

(The extract() statement is generated by Grafana so that's the reason I'm (or Grafana is) using it

postgresql
1 Answers

You need a function based index:

CREATE INDEX my_table_epoch_idx ON my_table (extract(EPOCH FROM created_at));
user_79253660
Related