问题:为带时区的时间戳添加索引

我想改进这个慢查询,我想添加一个索引,但我不知道哪种索引类型更适合我的情况。

SELECT COUNT(*) ct FROM events
WHERE dtt AT TIME ZONE 'America/Santiago'
   >= date(now() AT TIME ZONE 'America/Santiago') + interval '1s'  

查询计划:

"Aggregate  (cost=128032.03..128032.04 rows=1 width=0) (actual time=3929.083..3929.083 rows=1 loops=1)"
"  ->  Seq Scan on events  (cost=0.00..125937.68 rows=837742 width=0) (actual time=113.080..3926.972 rows=25849 loops=1)"
"        Filter: (timezone('America/Santiago'::text, dtt) >= (date(timezone('America/Santiago'::text, now())) + '00:00:01'::interval))"
"        Rows Removed by Filter: 2487386"
"Planning time: 0.179 ms"
"Execution time: 3929.136 ms"

• 查询获取当天事件的计数。

• dtt 是带有时区列的时间戳。

• 我正在使用 Postgresql 9.4。

注意:使用 Erwin 建议,查询运行得更快一些,但我认为仍然不够快。

"Aggregate  (cost=119667.76..119667.77 rows=1 width=0) (actual time=3687.151..3687.152 rows=1 loops=1)"
"  ->  Seq Scan on vehicle_events  (cost=0.00..119667.14 rows=250 width=0) (actual time=104.635..3687.068 rows=469 loops=1)"
"        Filter: (dtt >= timezone('America/Santiago'::text, date_trunc('day'::text, timezone('America/Santiago'::text, now()))))"
"        Rows Removed by Filter: 2513337"
"Planning time: 0.164 ms"
"Execution time: 3687.204 ms"

解答

首先,修复您的查询以使谓词可搜索:

SELECT count(*) AS ct
FROM   events
WHERE  dtt >= date_trunc('day', now() AT TIME ZONE 'America/Santiago')
                                      AT TIME ZONE 'America/Santiago'

按原样使用列值并将所有计算移至参数。

没错,推导出本地的一天开始后,再次应用AT TIME ZONE,将timestamp再次转换回timestamptz。细节:

• 在 Rails 和 PostgreSQL 中完全忽略时区

分步说明

1. now()

.. 是 SQL 标准CURRENT_TIMESTAMP的 Postgres 实现。两者都是 100% 等效的,您可以使用其中任何一个。它将当前时间点返回为 timestamptz - 值的 display 考虑了当前会话的时区,但这与 value 无关。

2.now()AT TIME ZONE 'America/Santiago'

.. 计算给定时区的本地时间。生成的数据类型为 timestamp。我们这样做是为了:

3. date_trunc(now() AT TIME ZONE 'America/Santiago')

.. 截断时间组件以获取“美国/圣地亚哥”中当地一天的开始时间,与当前时区设置无关。

4.date_trunc('day', now() AT TIME ZONE 'America/Santiago')AT TIME ZONE 'America/Santiago'

.. 将timestamp输入到AT TIME ZONE构造中,我们得到相应的 timestamptz 值(内部为 UTC),以将timestamptz值dtt进行比较。

我删除了+ interval '1s',怀疑你刚刚滥用它来将date转换为timestamp。请改用date_trunc()来生成timestamp值。

现在,dtt** 上的 **plain(默认)btree 索引就可以了。当然,只有在谓词有足够的选择性时才会使用索引。

CREATE INDEX events_dtt_idx ON events (dtt);

如果您的重要查询只考虑最近的行,那么部分索引可能会有所帮助。细节:

• 从大表中获取每个父级的最新子级 - 查询太慢