PostgreSQL中带有时区的时间戳不使用索引。

Question

我已以以下方式在表上创建了一个索引：

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

当我尝试运行查询时

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

我得到了以下输出结果。

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

从我们可以看到的情况来看，它在过滤行时没有使用索引。我已经查阅了一些stackoverflow的答案，但是没有找到所需的答案。

我的pages.timestamp列的类型为timestamp with time zone。

在过滤时，每天都会基于当前日期（由单独的程序生成）动态生成2019-05-08。我在SELECT语句中有大约12个text列，但为了简单起见，我在这里写了*。

pages表包含按小时插入的记录，但我每天只提取一次。目前它包含大约5000万条记录，并且每天都在增加。

如何有效地使用索引？我正在使用AWS RDS 9.6。

- Neil

Answer 1

索引表达式必须与WHERE条件的一侧完全匹配。

你有两个选择：

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')
   

- Laurenz Albe

---

我尝试了第一种方法，它起作用了！谢谢。 - Neil