SQL PostgreSQL：在时间戳::日期上创建索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34724403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Create an index on timestamp::DATE
提问by Adam Matan
I am creating a summary table that sums up all events in a given day.
我正在创建一个汇总表，汇总给定日期内的所有事件。
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;
The creation_timecolumn is indexed:
该creation_time列已编入索引：
CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time);
However, the query runs a pretty long time even when only querying two days of data with a handful of events (January 1-2 2014).
但是，即使仅查询具有少量事件的两天数据（2014 年 1 月 1 日至 2 日），查询也会运行相当长的时间。
The EXPLAINon the query is pretty grim - it runs a sequential scan on the eventtable, not utilizing the index at all:
在EXPLAIN上查询是相当严峻-它运行一个顺序扫描的event表，而不是利用指数都：
-> Seq Scan on event e_1 (cost=0.00..12557.39 rows=531 width=38)
Filter: ... AND ((creation_time)::date = (generate_series(()::timestamp with time zone, ()::timestamp with time zone, '1 day'::interval))))
I assume this is because we compare a casted value - creation_time::DATE, not creation_time. I have tried indexing the cast:
我认为这是因为我们比较了一个铸造值 - creation_time::DATE，而不是creation_time。我试过索引演员表：
CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time::DATE);
But got an error:
但是得到了一个错误：
ERROR: syntax error at or near "::"
Is there a way to utilize PostgreSQL indices on a timezone column casted to DATE?
有没有办法在转换为 DATE 的时区列上使用 PostgreSQL 索引？
回答by klin
An expression in an index declaration should be enclosed in additional brackets, try:
索引声明中的表达式应包含在额外的括号中，请尝试：
CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));
