優化對一系列時間戳（兩列）的查詢
我在 Ubuntu 12.04 上使用 PostgreSQL 9.1。
我需要在一段時間內選擇記錄：我的表
time_limits有兩個timestamp欄位和一個integer屬性。我的實際表中有其他列與此查詢無關。create table ( start_date_time timestamp, end_date_time timestamp, id_phi integer, primary key(start_date_time, end_date_time,id_phi);該表包含大約 2M 條記錄。
像下面這樣的查詢花費了大量的時間：
select * from time_limits as t where t.id_phi=0 and t.start_date_time <= timestamp'2010-08-08 00:00:00' and t.end_date_time >= timestamp'2010-08-08 00:05:00';所以我嘗試添加另一個索引 - PK的倒數：
create index idx_inversed on time_limits(id_phi, start_date_time, end_date_time);我的印像是性能提高了：訪問表中間記錄的時間似乎更合理：大約在 40 到 90 秒之間。
但是對於時間範圍中間的值，它仍然是幾十秒。當目標是表的末尾時（按時間順序），還有兩倍。
我
explain analyze第一次嘗試得到這個查詢計劃：Bitmap Heap Scan on time_limits (cost=4730.38..22465.32 rows=62682 width=36) (actual time=44.446..44.446 rows=0 loops=1) Recheck Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone)) -> Bitmap Index Scan on idx_time_limits_phi_start_end (cost=0.00..4714.71 rows=62682 width=0) (actual time=44.437..44.437 rows=0 loops=1) Index Cond: ((id_phi = 0) AND (start_date_time <= '2011-08-08 00:00:00'::timestamp without time zone) AND (end_date_time >= '2011-08-08 00:05:00'::timestamp without time zone)) Total runtime: 44.507 ms我可以做些什麼來優化搜尋？
id_phi您可以看到一旦設置為 ，掃描兩個時間戳列所花費的所有時間0。而且我不理解時間戳上的大掃描（60K 行！）。他們不是由主鍵索引並且idx_inversed我添加的嗎？我應該從時間戳類型更改為其他類型嗎？
我已經閱讀了一些關於 GIST 和 GIN 索引的內容。我收集它們可以在自定義類型的某些條件下更有效。對於我的案例來說，這是一個可行的選擇嗎？
對於 Postgres 9.1 或更高版本：
CREATE INDEX idx_time_limits_ts_inverse ON time_limits (id_phi, start_date_time, end_date_time **DESC**);在大多數情況下，索引的排序順序幾乎不相關。Postgres 幾乎可以以同樣快的速度向後掃描。但是對於多列的範圍查詢，它可以產生巨大的差異。密切相關：
考慮您的查詢：
SELECT * FROM time_limits WHERE id_phi = 0 AND start_date_time <= '2010-08-08 00:00' AND end_date_time >= '2010-08-08 00:05';
id_phi索引中第一列的排序順序無關緊要。由於已檢查是否相等(=)，因此它應該排在第一位。*你說對了。*更多相關答案：Postgres 可以立即跳到
id_phi = 0並考慮匹配索引的以下兩列。這些是使用反向排序順序（，）的範圍條件查詢的。在我的索引中，符合條件的行排在第一位。應該是使用 B-Tree 索引1的最快方法：<=``>=
- 您想要
start_date_time <= something：索引首先具有最早的時間戳。- 如果符合條件，還要檢查第 3 列。
遞歸直到第一行不符合條件（超快）。
- 您想要
end_date_time >= something：索引首先具有最新的時間戳。- 如果符合條件，請繼續獲取行，直到第一個沒有（超快）。
繼續第 2 列的下一個值 ..
Postgres 可以向前或向後掃描。你有索引的方式，它必須讀取前兩列匹配的所有行，然後過濾第三列。請務必閱讀索引章節和
ORDER BY手冊中的內容。它非常適合您的問題。前兩列有多少行匹配？
只有少數具有
start_date_time接近表的開始時間範圍。但幾乎所有行都id_phi = 0在表的時間順序末尾！因此，性能會隨著開始時間的延遲而下降。規劃師估計
規劃器估計
rows=62682您的範例查詢。其中，沒有一個符合（rows=0）。如果您增加表的統計目標，您可能會得到更好的估計。對於 2.000.000 行…ALTER TABLE time_limits ALTER start_date_time SET STATISTICS 1000; ALTER TABLE time_limits ALTER end_date_time SET STATISTICS 1000;……可能會付出。甚至更高。更多相關答案：
我猜你不需要它
id_phi（只有幾個不同的值，均勻分佈），而是時間戳（很多不同的值，分佈不均勻）。我也認為改進後的索引並不重要。
CLUSTER/ pg_repack / pg_squeeze但是，如果您希望它更快，您可以簡化表中行的物理順序。如果您有能力專門鎖定您的表（例如在下班時間），請重寫您的表並根據索引對行進行排序
CLUSTER：CLUSTER time_limits USING idx_time_limits_inversed;或者考慮pg_repack或後來的pg_squeeze，它可以在沒有排他鎖的情況下做同樣的事情。
無論哪種方式，效果都是需要從表中讀取的塊更少，並且所有內容都是預先排序的。這是一種一次性效應，隨著時間的推移而惡化，表上的寫入會使物理排序順序碎片化。
Postgres 9.2+ 中的 GiST 索引
1對於 pg 9.2+，還有另一個可能更快的選項：範圍列的 GiST 索引。
timestamp和timestamp with time zone:tsrange,tstzrange有內置的範圍類型。對於integer像id_phi. 更小，維護成本也更低。但是使用組合索引，查詢總體上可能仍然會更快。- 更改表定義或使用表達式索引。
- 對於手頭的多列 GiST 索引，您還需要
btree_gist安裝附加模組（每個數據庫一次），該模組提供運算符類以包含integer.三連冠！多列功能 GiST 索引：
CREATE EXTENSION IF NOT EXISTS btree_gist; -- if not installed, yet CREATE INDEX idx_time_limits_funky ON time_limits USING gist (id_phi, tsrange(start_date_time, end_date_time, '[]'));現在在查詢中使用“包含範圍”運算符：
@>SELECT * FROM time_limits WHERE id_phi = 0 AND tsrange(start_date_time, end_date_time, '[]') @> tsrange('2010-08-08 00:00', '2010-08-08 00:05', '[]')Postgres 9.3+ 中的 SP-GiST 索引
對於這種查詢，SP-GiST 索引可能會更快-**除了**引用手冊：
目前，只有 B-tree、GiST、GIN 和 BRIN 索引類型支持多列索引。
在 Postgres 12 中仍然如此。
您必須將一個
spgist索引 on just(tsrange(...))與第二個btree索引 on結合起來(id_phi)。由於增加了成本，我不確定這是否可以競爭。僅針對
tsrange列的基準的相關答案：