Datetime 比較星期幾和小時的索引優化

我有一個感測器觀察表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嗎？

根據 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.

引用自：https://dba.stackexchange.com/questions/231957