主題: PostgreSQL

慢查詢別先加 index：用 EXPLAIN、statistics 與 ANALYZE 看懂 planner 在猜什麼

PostgreSQL 的 planner 用統計資料估算 query 的成本。從 EXPLAIN 的估計與實際 rows 開始，才能判斷該更新統計、調整 query，還是真的需要新 index。

慢 query 出現時，很容易先補一個 index。看見 Seq Scan 更容易慌，彷彿 PostgreSQL 忽略了眼前的捷徑。

但 SQL 只描述要什麼結果，沒規定怎麼拿。planner 會比較可行路徑，依 table 大小、可用 index、設定與 statistics 估計成本，再挑它認為最便宜的一條。它選 sequential scan，有時是因為真的要讀走大半張 table；有時則是它把資料分布猜錯了。兩種情況的處理完全不同。

這篇不把 EXPLAIN 當成一串需要背的名詞，而是把它當成除錯順序：先確認 PostgreSQL 打算做什麼，再量實際做了什麼，最後才決定要改 query、更新 statistics，或加 index。

EXPLAIN 是計畫，EXPLAIN ANALYZE 是實際執行

先從不會執行 SQL 的版本開始：

EXPLAIN
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

它會列出 planner 選的 plan，以及每個 node 預估處理多少 row、預估成本多少。cost=... 不是毫秒，也不是使用者等待時間；那是 PostgreSQL 用來比較候選路徑的模型分數。

接著才是在允許執行的環境加入實測資料：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

ANALYZE 會真的跑這個 statement。對 SELECT，它可能仍然很慢，並且會讀取真實資料；對 INSERT、UPDATE、DELETE，則會真的改資料。要量寫入 statement 時，請只在受控環境執行，或用明確的 transaction 和 ROLLBACK 包住測試。

先看 rows，不要先審判 Seq Scan

閱讀 EXPLAIN (ANALYZE, BUFFERS) 時，我會先盯這幾件事：

• node 做了什麼，例如 Seq Scan、Index Scan、Sort 或 join。它是線索，不是好壞判決。
• rows 的 estimate 與 actual rows 是否相差很大。這是最常見的起點。
• loops。一個 node 每次只回傳少量 row，卻被重複呼叫很多次，總工作量仍可能很大。
• 最下方的 Execution Time，以及 BUFFERS 顯示的 shared hit、read、temp。它們能幫我分辨 CPU、cache、磁碟或排序溢寫等方向，但單次結果仍會受 cache 狀態影響。

例如 planner 以為某個 filter 只會留下 10 row，實際卻留下 100,000 row。它可能因此選了適合「很少結果」的 nested loop 或 index path，最後反覆做太多工作。這不等於「statistics 一定壞了」；資料偏斜、彼此相關的條件、範圍查詢或 query 寫法，也都可能讓估計失真。重點是先找出第一個 estimate 明顯偏離的 node，再回頭看它依賴什麼條件。

ANALYZE 更新統計；autovacuum 處理日常維護

ANALYZE orders; 會抽樣 table，把欄位值的分布、distinct 值數量與常見值等資訊寫入 planner 使用的 statistics。它不會重寫 table，也不會自動修好 query。

autovacuum 是背景維護機制。PostgreSQL 的 MVCC 讓 UPDATE 和 DELETE 留下舊的 row version；一般 VACUUM 讓那些空間可重複使用，也處理 transaction ID 的長期安全問題。自動維護系統也會依 table 的變更量與門檻安排 auto-analyze，讓 planner 有較新的統計資料。

這裡有兩個常見誤解。

• autovacuum 不是「有它就不用想」。它看的是變更量與門檻，不會理解某一欄的資料分布已經變得不適合某個 range query。大量匯入、回填或大規模重新分類完成後，若需要立刻跑關鍵 query，手動 ANALYZE 可以把更新 statistics 變成明確的部署後步驟。
• ANALYZE 和 VACUUM 不是同一件事。前者改善 planner 的估計資訊，後者主要處理 dead tuples 與 transaction ID 維護。VACUUM FULL 會重寫 table 並取得很強的鎖，不能當成慢 query 的日常解法，也不該因為估計偏差就直接使用。

若要判斷是否真的落後，先在有權限的營運環境查看 pg_stat_user_tables 的 last_analyze、last_autoanalyze 與 n_mod_since_analyze，再配合實際 plan 做決定。不要因為一個頁面慢，就先全域調高 autovacuum 或重設統計資料。

兩個欄位有關聯時，可能需要 extended statistics

一般 column statistics 假設條件彼此相當獨立。真實資料經常不是這樣，例如某個國家幾乎只用一種貨幣，或某種狀態只會在少數類別出現。若 query 同時篩選這些欄位，planner 可能把兩個比例相乘，得到不合理的 rows estimate。

這時才考慮 CREATE STATISTICS。它不是 index，不會讓查詢直接變快；它是讓 planner 多知道一些資料關係。

CREATE STATISTICS orders_region_status_mcv (mcv)
ON region, status
FROM orders;

ANALYZE orders;

• ndistinct 適合描述多欄組合有多少 distinct 值。
• dependencies 描述欄位之間的功能相依。它主要幫助簡單的等值條件，不能期待它理解 lower(email) LIKE 'a%' 這類 expression 或 pattern。
• mcv 記錄常見的欄位值組合，適合少數組合很熱門、卻不符合獨立假設的情況。

先用 actual rows 的落差證明問題，再挑合適類型。沒有落差時先建立 extended statistics，只是多一個未證實的維護項目。

JSON 讓 plan 可以比較，不會讓 plan 自己變好

文字格式適合人讀；要把 plan 存進內部工具、比對兩次部署或做監控時，可以改用 machine-readable format：

EXPLAIN (FORMAT JSON)
SELECT id, created_at
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 20;

JSON 讓程式能以固定結構讀取 Node Type、estimate rows 等資訊；若同時指定 ANALYZE，還會包含 actual rows、loops 與 buffer 資訊。它本身不會執行 query。加上 ANALYZE 時，仍然要遵守前面的執行風險。

另一個安全邊界也很實際：plan 可能含有 table、column、filter 或參數值。不要把 production plan 原封不動貼到公開 visualizer 或聊天工具。先移除敏感內容，或只在受權限的內部監控系統處理。

一個可重複的排查順序

1. 先用 EXPLAIN 看 planner 打算做什麼。
2. 在可接受的成本下，用代表性的參數執行 EXPLAIN (ANALYZE, BUFFERS)。
3. 從最早發生的大幅 rows 落差開始找，而不是先對最外層 node 下結論。
4. 檢查 query predicate、資料分布、最近的資料變更與 statistics 狀態。
5. 只有證據指向它時，才選擇 ANALYZE、extended statistics、query 改寫或 index 變更，然後再量一次。

這個順序比較慢一點，但它避免了「每次都加 index」的假解法。planner 不是神祕黑箱，它只是拿著不完整資訊做成本估算。把估計和實際攤開，就知道下一步該改哪裡。

我學到什麼

• 我會先比較 estimate rows 和 actual rows，再判斷問題比較像 statistics、資料關係，還是 query 與 index 的不匹配。
• EXPLAIN 不會執行 SQL，EXPLAIN ANALYZE 會。量寫入 query 前，要先安排安全的 transaction 邊界。
• ANALYZE 更新 planner 的資料分布資訊；autovacuum 和一般 VACUUM 處理的是另一部分的日常維護。兩者都不能取代 query 設計。
• extended statistics 是針對已觀察到的估計誤差使用的工具，不是新的預設 index。

外部參考資料

• PostgreSQL：Using EXPLAIN
• PostgreSQL：EXPLAIN
• PostgreSQL：Planner Statistics
• PostgreSQL：Routine Vacuuming
• pganalyze：The Basics of Postgres Query Planning
• Reddit：PostgreSQL 使用者討論如何調查慢 query

延伸學習

• PostgreSQL：Using EXPLAIN：用官方範例練習讀 scan、join、sort 與 rows estimate。
• PostgreSQL：Planner Statistics：在需要時查 pg_stats 與 extended statistics 的精確範圍。
• PostgreSQL：Routine Vacuuming：建立 VACUUM、autovacuum 與 transaction ID 維護的正確分工。
• pganalyze：The Basics of Postgres Query Planning：用更直白的圖解理解 plan node、成本與實際執行資料。