PostgreSQL：在timestamp::DATE上创建索引

Question

我正在创建一个汇总表格，概括给定日期内所有事件。

INSERT INTO graph_6(
  day,
  event_type,
  (SELECT COUNT(*) FROM event e 
                  WHERE event_type = e.event_type 
                  AND creation_time::DATE = sq.day)
FROM event_type
CROSS JOIN
    (SELECT generate_series(
                (SELECT '2014-01-01'::DATE),
                (SELECT '2014-01-02'::DATE),
                '1 day') as day) sq;

creation_time列已创建索引：

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time);

然而，即使在查询仅涉及2014年1月1日至2日的少量事件时，查询的运行时间也相当长。

对该查询进行的EXPLAIN结果相当糟糕——它对event表执行了顺序扫描，根本没有利用索引：

->  Seq Scan on event e_1  (cost=0.00..12557.39 rows=531 width=38)
Filter: ... AND ((creation_time)::date = (generate_series(($12)::timestamp with time zone, ($13)::timestamp with time zone, '1 day'::interval))))

我认为这是因为我们比较的是转换后的值 - creation_time :: DATE，而不是 creation_time。我已经尝试对转换后的值建立索引:

我假设这是因为我们在比较一个强制转换后的值- creation_time::DATE，而不是creation_time本身。我尝试过对强制转换后的值进行索引：

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time::DATE);

但是出现了一个错误：

ERROR: syntax error at or near "::"

有没有一种方法可以利用PostgreSQL索引在时区列转换为日期后使用？

- Adam Matan

---

由于太多的SQL JOIN，我是否正在失去JOIN它？ 这是一个JOIN问题的重复，不到一个月前我已经问过了。度假中放松一下，好好读读JOIN书，愉快地搞JOIN！ - Adam Matan

Answer 1

在索引声明中，表达式应该用额外的括号括起来，尝试使用以下方式：

索引声明中的表达式应该被额外的括号包含，尝试：

CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));

- klin

---

仅适用于没有时区的时间戳。否则，您将收到此错误： ERROR：在索引表达式中的函数必须标记为不可变。 - jap1968

如果你选择一个时区并在索引和查询中都进行转换，就可以解决时区问题（在某种程度上）：creation_time AT TIME ZONE 'UTC'。 - thaddeusmt

如果您正在使用@thaddeusmt的解决方案，请确保在查询中也使用该语法（转换为UTC）-否则它将不使用索引。感谢这个技巧。;) - CaKa

使用thaddeusmt的建议创建索引很好用。这对我来说是解决方案的一半。在查询数据时，另一半的解决方案可以在这里找到：https://gist.github.com/cobusc/5875282。作为他人的评论，可能也需要这个建议。 - ssi-anik