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.

Answer 1

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

You have two options:

1. Use this index:

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

2. Rewrite the query:

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