主页 / dba / 问题 / 231957
Accepted
raphael
Asked: 2019-03-13 12:03:24 +0800 CST

比较星期几和小时的日期时间索引优化

  • 772

我有一个传感器观测值表obs_ts timestamp, sensor_id text, sensor_val int，为了用数据填补空白，我们有按星期几和一天中的小时划分的模型：model_id int, hour_of_day int, model_val int。

为了填补缺失值，我们将通过交叉表连接这两个表，即：sensor_id text, day_of_week int, model_id int

如果我们的观察表很大，那么为加入isodow和建立索引的最佳方式是什么hour。索引时间戳是否也像索引函数一样EXTRACT(isodow FROM obs_ts)，或者我应该使这些函数索引明确，例如CREATE INDEX ON observations (EXTRACT isodow FROM obs_tx)。对于按小时加入，将 转换hour_of_day为 a会更好timerange吗？

postgresql index-tuning

1 个回答

  1. Best Answer

    根据 Verace 的建议，我创建了一个包含 10M 记录的测试表。TLDR：日期时间函数的索引需要是显式的，任何用于连接/过滤的日期时间列的转换都不会由索引提供。

    SELECT obs_ts AS weekday_indexed, obs_ts AS hour_indexed , * 
INTO index_test
FROM observations
LIMIT 10000000;

CREATE INDEX ON index_test(EXTRACT('isodow' from weekday_indexed));
CREATE INDEX ON index_test(EXTRACT('hour' FROM hour_indexed));
CREATE INDEX ON index_test(obs_ts);
ANALYSE index_test;

    表大小为 1.7 GB。所有索引都是 214MB。从我的测试中，obs_ts从未使用过索引（可能是因为这些比较涉及更改时间戳列的数据类型......）。使用特定于功能的索引，因此肯定存在性能索引大小权衡。

    星期几

    SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from obs_ts )=5
--3 secs 276 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('isodow' from weekday_indexed)=5
--1 secs 586 msec.

    一天中的小时

    SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from obs_ts )=5
-- Total query runtime: 2 secs 420 msec.

EXPLAIN ANALYZE SELECT COUNT(1)
FROM index_test
WHERE obs_ts::TIME >= '05:00' AND obs_ts::TIME < '06:00'
-- Total query runtime: 2 secs 391 msec.

SELECT COUNT(1)
FROM index_test
WHERE tx::TIME @> timerange('05:00', '06:00')
-- Total query runtime: 1 secs 928 msec.

SELECT COUNT(1)
FROM index_test
WHERE extract('hour' from hour_indexed)=5
-- 779 msec.
    • 0

相关问题