大表中的慢速索引掃描

2020 年 8 月 4 日更新：

由於顯然仍在定期查看此答案，因此我想提供有關情況的更新。我們目前正在使用啟用表分區的 PG 11，timestamp並且可以輕鬆處理表中的數十億行。僅索引掃描是救命稻草，沒有它就不可能。

---

使用 PostgreSQL 9.2，我在相對較大的表（200+ 百萬行）上遇到慢查詢問題。我沒有嘗試任何瘋狂的事情，只是添加了歷史價值。下面是查詢和查詢計劃輸出。

我的表格佈局：

                                  Table "public.energy_energyentry"
 Column   |           Type           |                            Modifiers
-----------+--------------------------+-----------------------------------------------------------------
id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
prop_id   | integer                  | not null
timestamp | timestamp with time zone | not null
value     | double precision         | not null
Indexes:
   "energy_energyentry_pkey" PRIMARY KEY, btree (id)
   "energy_energyentry_prop_id" btree (prop_id)
   "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
   "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

數據范圍從2012-01-01到現在，不斷增加新數據。外鍵中有大約 2.2k 個不同的值prop_id，分佈均勻。

我注意到行估算並不遙遠，但成本估算似乎大了 4 倍。這可能不是問題，但我能做些什麼嗎？

我預計磁碟訪問可能是問題，因為該表並非一直在記憶體中。

EXPLAIN ANALYZE 
SELECT SUM("value") 
FROM "energy_energyentry" 
WHERE 
 "prop_id"=82411 
 AND "timestamp">'2014-06-11' 
 AND "timestamp"<'2014-11-11'
;

 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

有什麼建議可以加快速度嗎？

我也很高興聽到我沒有做任何奇怪的事情。

您的表很大，跨整個表的任何索引也是如此。假如說：

• 僅輸入新數據（帶timestamp = now()）
• 現有行既不會更改也不會刪除。
• 您有自 2012-01-01 以來的數據，但查詢主要針對當年（？）

我建議使用部分、多列（覆蓋！）索引：

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

在 Postgres 11 或更高版本中，請改用“覆蓋”索引：

CREATE INDEX ON energy_energyentry (prop_id, "timestamp") INCLUDE (value)
WHERE "timestamp" >= '2014-01-01 0:0';

看：

• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

只包括定期查詢的時間範圍。僅當您以這種方式切斷大部分大桌子時才有意義。隨著新條目的出現，效果會隨著時間的推移而惡化。不時重新創建索引。（您可能需要調整您的查詢。）請參閱下面的連結答案。

僅包含最後一列值以從中獲取僅索引掃描。積極的 autovacuum 設置可能有助於保持可見性地圖是最新的，就像 @jjanes 已經提到的那樣。

部分索引應該更容易放入 RAM 並保持更長的時間。

您可能需要WHERE在查詢中包含此條件，以使計劃者了解索引適用於查詢，例如：

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411 
AND   "timestamp" > '2014-06-11 0:0' 
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

由於您的查詢匯總了很多行 ( rows=13578)，因此即使使用僅索引掃描，這也需要一些時間。不過，它不應該接近 50 秒。在任何中等體面的硬體上不到一秒鐘。

相關（但忽略CLUSTERand FILLFACTOR，如果您可以從中獲得僅索引掃描，則兩者都無關緊要）：

• 日期索引優化

旁白：

由於您目前在 上有一個索引(prop_id, "timestamp")，因此在 just 上的附加索引(prop_id)可能會花費超過其價值：

• 複合索引是否也適用於第一個欄位的查詢？

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