sql - 将索引添加到带时区的时间戳

Question

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

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。

注意:有了欧文的建议，查询运行得更快了一点，但我认为还不够快。

"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"

Answer 1

首先，修复您的查询以创建谓词 "sargable" :

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再次。见:

• Ignoring time zones altogether in Rails and PostgreSQL

逐步解释

1. now()
   .. 是 SQL 标准的 Postgres 实现 CURRENT_TIMESTAMP .两者都是 100% 等效的，您可以使用任何一个。它将当前时间点返回为 timestamptz - 值的显示考虑了当前 session 的时区，但这与值无关。

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值(value)。

现在，dtt 上的普通(默认)btree 索引 会做。当然，只有当谓词足够有选择性时，才会使用索引。

CREATE INDEX events_dtt_idx ON events (dtt);

如果您的重要查询只考虑最近的行，部分索引 可能会有更多帮助。详情:

• Get latest child per parent from big table - query is too slow