PostgreSQL timestamp with timezone does not use index

Question

I have created an index on a table in following way:-

CREATE INDEX pages_timestamp_idx ON mySchema.pages(date("timestamp" at time zone 'UTC'));

When I try to run the query

EXPLAIN ANALYSE
SELECT *
FROM mySchema.pages
WHERE DATE (pages."timestamp" at TIME zone 'UTC' +INTERVAL '8 hours') >= DATE ('2019-05-08')

I get the following output

Seq Scan on pages  (cost=0.00..4050358.12 rows=10013919 width=1946) (actual time=215758.903..440677.734 rows=225596 loops=1)
   Filter: (date((timezone('utc'::text, "timestamp") + '08:00:00'::interval)) >= '2019-05-08'::date)
   Rows Removed by Filter: 29816159
Planning time: 0.106 ms
Execution time: 440721.718 ms

As we can see, It does not use the index while filtering the rows. I have gone through a few stackoverflow answers, but I didn't find the required answers.

My pages.timestamp column is of type timestamp with time zone.

While filtering, 2019-05-08 is generated dynamically every day, based on the current date (a separate program generates it). I have around 12 text columns in the SELECT statement, but just for simplicity I have written * here.

The pages table contains the records which are inserted on hourly basis, but I extract it only once a day. Currently it contains around 50 million records and is increasing every day.

How do I use the index here efficiently? I am using AWS RDS 9.6.

Laurenz Albe · Accepted Answer · 2019-05-10 04:48:16Z

2

The indexed expression must match one side of the WHERE condition exactly.

You have two options:

Use this index:

CREATE INDEX ON myschema.pages
   ((date(pages."timestamp" AT TIME ZONE 'UTC' + INTERVAL '8 hours')));

Rewrite the query:

WHERE date(pages."timestamp" AT TIME ZONE 'UTC')
      >= date(('2019-05-08'::timestamp) AT TIME ZONE 'UTC' - INTERVAL '8 hours')

answered May 10, 2019 at 4:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neil Over a year ago

I tried the first approach and it worked! Thank you.

Collectives™ on Stack Overflow

PostgreSQL timestamp with timezone does not use index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related