EXPLAIN ANALYSE 完整指南（Victor Gardini）

Medium｜Victor Gardini｜2025-09-10｜〈EXPLAIN ANALYSE — The Complete Guide〉同一天還有一篇姐妹作〈SQL Explain EXPLAIN ANALYSE Laboratory〉（14 分鐘後發），是純實作版。本檔整理的是前者。

這篇有兩處基本讀法是錯的

cost=0.00..14.30 和 actual time=0.016..0.021 的兩個數字，不是「範圍」，而是「啟動 .. 總計」，而且 actual time 的單位是毫秒不是秒。原文兩處都解釋錯了。這是整份計畫最先學到的東西，所以我把它獨立標在〈矛盾點〉第 1 條，讀〈統整〉時請帶著這個修正。

媒體

1 張圖（五階段流程圖），用原始 source 放在對應位置。

統整

為什麼需要它：問題定位到最後一層

作者的開場是一個很具體的除錯路徑——電商查詢變慢，你沿著 stack 往下找：

Front-End  → 沒問題，只是打 API 然後顯示
Back-End   → 也沒問題，程式碼是對的，它在「等資料庫回來」
Database   → 慢在這裡。但看 log 和資源也看不出什麼異常

到了這一層要怎麼分析？ 這就是 EXPLAIN ANALYSE 存在的位置。

它讓你檢視 SQL 的執行計畫：PostgreSQL 打算怎麼執行這個查詢，包括操作順序、資料表存取方式、成本估計。

一個查詢在 PostgreSQL 裡經過的五個階段

每個連線的查詢都由後端 process 處理，而每個 process 走同樣的五步：

階段	做什麼
Parser	收到純文字 SQL，轉成 parse tree（語法結構）
Analyzer	對 parse tree 做語意分析，產生 query tree（意義與關聯）
Rewriter	若 rule system 有相關規則，套用它們改寫 query tree
Planner	產生 plan tree——把 query tree 轉成一連串有效率的執行步驟
Executor	實際執行：依 plan tree 決定的順序存取資料表與索引

|757x174

（推論）值得注意的是 Planner 和 Executor 是分開的兩步——EXPLAIN 只跑到 Planner 停下來給你計畫，EXPLAIN ANALYSE 才會真的走完 Executor。文章沒有把這個差別講出來，但它是兩個指令的全部差異。⚠️ 見矛盾點 2。

掃描方式（scan types）

Planner 會挑它認為最有效率的那一種：

類型	什麼時候用
Sequential Scan	逐列讀整張表。表很小、或大部分列都要用到時反而有效
Index Scan	用索引找符合條件的列。有合適索引、而且查詢有選擇性時才有效率
Index Only Scan	同上，但所需資訊全部在索引裡，不用回主表。要的欄位都在索引裡時非常有效
Bitmap Index Scan	多個條件用 AND/OR 組合時。為每個索引建 bitmap，再把 bitmap 合併找出對應的列
Nested Loop Join	兩表 join 時用巢狀迴圈配對。其中一表很小、或有合適索引加速 join 時有效

選擇的依據：

PostgreSQL 依據它內部持有的資料表與索引統計資訊來估計計畫的成本，成本是「執行所需 I/O 操作次數」的估計值，然後選成本最低的那個。

統計資訊從哪來：

執行 ANALYZE 指令時收集（見 pg_statistic catalog）
每次 VACUUM 也會更新

讀懂最基本的一份輸出

EXPLAIN ANALYSE SELECT * FROM example;

 Seq Scan on example  (cost=0.00..14.30 rows=430 width=159) (actual time=0.016..0.021 rows=19 loops=1)
 Planning Time: 0.166 ms
 Execution Time: 0.040 ms

原文的逐項解釋（⚠️ 前兩項有誤，正確版見矛盾點 1）：

欄位	原文的說法
`Seq Scan on example`	對 example 表做循序掃描，走過所有列找符合的
`cost=0.00..14.30`	估計成本「介於 0.00 到 14.30 之間」⚠️
`rows=430`	估計會回傳的列數
`width=159`	每列的估計寬度（bytes）
`actual time=0.016..0.021`	「實際執行花了 0.016 到 0.021 秒」⚠️
`rows=19 loops=1`	實際回傳 19 列，迴圈執行 1 次
`Planning Time`	規劃執行計畫花的時間
`Execution Time`	執行的總時間

估計 430 vs 實際 19 —— 這個落差作者在這裡沒解釋，到後面 TIMESTAMP 那段才說「estimates are off; run ANALYZE」。（推論）第一個例子就該講。

依資料型別逐個看計畫

作者的核心免責聲明擺在這一節開頭，整篇的範例都要帶著它讀：

你拿到什麼計畫，取決於資料分布、索引、述詞、表大小。 在很小的表上（就像這篇用的這些），Seq Scan 可能比碰索引還便宜；在大表上，好的索引／篩選條件才是主導。

主鍵（Index Only Scan）

EXPLAIN ANALYSE SELECT id FROM example WHERE id = 1;

 Index Only Scan using example_pkey on example  (cost=0.15..8.17 rows=1 width=4) (actual ... rows=1 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 1

Index Only Scan → 好事，SELECT id 只需要索引就夠了
Heap Fetches: 1 → visibility map 對那一頁不是 all-visible，所以還是回主表檢查了一次
處方：VACUUM (ANALYZE) 和健康的 autovacuum 會改善 visibility map，讓 index-only scan 更常避免回表
另一條：把 select 清單限制在索引涵蓋的欄位

VARCHAR（Seq Scan ＋ Filter）

 Seq Scan on example  (cost=0.00..13.50 rows=1 width=259) (actual ... rows=1 loops=1)
   Filter: ((name)::text = 'Ana'::text)
   Rows Removed by Filter: 18

Rows Removed by Filter: 18 → 讀了 18 列、留下 1 列。它顯示的是選擇性。

加速的兩條，都是「把函數移進索引」：

-- 不分大小寫搜尋 → 函數索引
CREATE INDEX ON example (lower(name));
SELECT * FROM example WHERE lower(name) = 'ana';
 
-- 前綴／子字串搜尋 → trigram 索引
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON example USING gin (name gin_trgm_ops);
-- WHERE name ILIKE '%ana%'

INTEGER（低基數欄位）

   Filter: (age = 25)
   Rows Removed by Filter: 17

等值查詢打在低基數欄位上通常沒有選擇性。 如果大多數列的 age 都一樣，即使有索引，planner 仍會選 Seq Scan。

索引只在述詞有選擇性、或搭配其他有選擇性的條件時才有幫助：
CREATE INDEX CONCURRENTLY ON example (age, created_date);
-- 幫得上 WHERE age=25 AND created_date>=...

DECIMAL / NUMERIC

兩個 gotcha：

欄位若是 DOUBLE PRECISION（float）避免用 =，因為捨入誤差；改用容差區間：
```
WHERE height BETWEEN 1.56 - 1e-6 AND 1.56 + 1e-6
```
型別要對齊，避免在「欄位上」發生隱含轉型（轉型發生在「常數上」沒關係）。例如 WHERE height::text = '1.56' 會讓 numeric 索引失效。

TIMESTAMP

 Seq Scan on example  (cost=0.00..13.50 rows=93 width=259) (actual ... rows=19 loops=1)

rows=93（估計）vs actual rows=19 → 估計不準，去跑 ANALYZE。 這類輸出正是用來偵測異常的。

BETWEEN 兩端都是閉區間。整天範圍建議改用半開區間避開時分秒的邊界問題：
```
WHERE created_date >= '2019-01-01' AND created_date < '2020-01-01'
```
時區要一致就用 timestamptz，必要時明確轉型字面值

很常用的固定起點 → partial index 可以又小又快：

CREATE INDEX CONCURRENTLY ON example (created_date)
WHERE created_date >= '2019-01-01';

BOOLEAN

低選擇性的布林欄位通常偏好 Seq Scan。

-- 資料高度傾斜時用 partial index（只有極少數列符合才划算）
CREATE INDEX ON example (id) WHERE active;
-- 或和其他有選擇性的欄位組成複合索引
CREATE INDEX CONCURRENTLY ON example (active, created_date);

兩個參數

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM example;   -- 顯示 buffer 使用
SET track_io_timing = ON;                            -- 顯示 I/O 讀寫時間

複雜查詢：InitPlan 與 One-Time Filter

EXPLAIN ANALYSE SELECT * FROM public.example
WHERE ((select count(*) from public.example where created_date > '2019-01-01') > 0);

 Result  (cost=15.61..29.81 rows=420 width=163) (actual ... rows=35 loops=1)
   One-Time Filter: ($0 > 0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=15.60..15.61 rows=1 width=8) (actual ... rows=1 loops=1)
           ->  Seq Scan on example example_1  (cost=0.00..15.25 rows=140 width=0) (actual ... rows=35 loops=1)
                 Filter: (created_date > '2019-01-01 00:00:00'::timestamp ...)
   ->  Seq Scan on example  (cost=15.61..29.81 rows=420 width=163) (actual ... rows=35 loops=1)

讀法：

InitPlan 1 (returns $0) → 子查詢被算成一個值存進 $0
One-Time Filter: ($0 > 0) → 那個條件只被求值一次，不是每列都算
之後才是主表的 Seq Scan

（推論）這個例子真正的教學點是**「子查詢和外層無關時，PostgreSQL 會把它提升成 InitPlan 只算一次」**——但文章只是逐行翻譯輸出，沒有把這句話說出來。

作者的收尾

我大量使用它搭配 PostgreSQL，但最近在做圖資料庫（Aura Neo4j）時，這套知識也很有用，幫我理解並優化了一些查詢。

另預告會再寫一篇含 Dead Tuples 分析的完整 lab。

反覆出現的關鍵字

選擇性（selective / selectivity）：整篇挑索引的唯一判準。VARCHAR、INTEGER、BOOLEAN 三節的結論都是同一句——不夠有選擇性，索引就贏不了 Seq Scan。
估計 vs 實際（estimate vs actual）：rows=430 對 rows=19、rows=93 對 rows=19。作者明說「這類輸出正是用來偵測異常的」——（推論）這其實是整個工具最大的價值，但被埋在 TIMESTAMP 那一節。
ANALYZE / autovacuum：出現在四個不同段落（統計資訊來源、估計不準的處方、visibility map、Heap Fetches）。幾乎每個問題的收尾都是「統計資訊要新鮮」。
partial / functional / composite index：三種「不是直接對欄位建索引」的索引，分別解掉布林傾斜、大小寫搜尋、低基數欄位。
「取決於資料分布與表大小」：3.4 節的免責聲明，而且必須套用到整篇每一個範例上。
成本＝I/O 次數的估計：成本模型的定義只講了這一句，但它決定了後面所有「為什麼選這個計畫」。

矛盾點

文章內部

cost=a..b 和 actual time=a..b 的兩個數字被解釋成「範圍」，這是錯的，而且單位也錯。（推論）正確的讀法是：
```
cost=0.00..14.30        → 0.00 是「啟動成本」（吐出第一列前的成本）
                           14.30 是「總成本」
actual time=0.016..0.021 → 0.016 ms 是「拿到第一列的時間」
                           0.021 ms 是「拿到最後一列的時間」
                           單位是【毫秒】，原文寫「秒」
```
為什麼要緊：啟動成本高但總成本低的計畫，和反過來的計畫，適用場景完全不同（有 LIMIT 時 planner 會偏好啟動成本低的）。把它讀成「範圍」就完全看不出這件事。
從頭到尾沒有說 EXPLAIN 和 EXPLAIN ANALYSE 差在哪，也沒有警告後者會「真的執行」。（推論）這是實務上最危險的缺漏：
```
EXPLAIN ANALYZE DELETE FROM orders WHERE ...;   -- 資料真的會被刪掉
```
安全做法是包在交易裡 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;。一篇教人跑 EXPLAIN ANALYSE 的文章不講這個，風險很高。
loops=1 只解釋成「迴圈執行了一次」，沒講 loops > 1 時的陷阱。（推論）Nested Loop 底下的節點 loops 會大於 1，而那時 actual time 和 rows 都是「每次迴圈的平均值」，要乘以 loops 才是總量。這是讀執行計畫最常見的誤判，而文章剛好列出了 Nested Loop Join 卻沒接上。
文章裡的資料庫狀態前後不一致。 INSERT 明明放了 35 列，但前半部所有輸出都顯示 19 列（rows=19、Rows Removed by Filter: 18），到最後兩個複雜查詢才變成 35。（推論）作者應該是分次做的，輸出來自不同時間點。照著文章做的人會對不上數字。
所有範例都得到 Seq Scan，而原因是表太小——作者講了但講得太晚也太輕。 3.4 節的「在很小的表上 Seq Scan 可能比碰索引還便宜」是關鍵前提，但它夾在一段過場文字裡，而後面六個型別的範例全部都是 Seq Scan。（推論）讀者很容易得到「看到 Seq Scan 就是有問題」的錯誤直覺，而這正好和作者自己的免責聲明相反。
ANALYSE / ANALYZE 兩種拼法混用（標題和內文用 S，EXPLAIN (ANALYZE, BUFFERS) 用 Z）。（推論）PostgreSQL 兩個拼法都接受，但混用會讓人以為有差別。

與 vault 既有筆記

Rows Removed by Filter 是 WHERE 決定回傳幾列，索引才決定要看過幾列的實測證據。 那篇的主張是「WHERE 決定回傳幾列，索引決定看過幾列」——而執行計畫把兩個數字並排印出來：rows=1（回傳）＋ Rows Removed by Filter: 18（讀了但丟掉）。（推論）值得補進那篇：這兩個量不只是概念上分開，它們在 EXPLAIN ANALYZE 的輸出裡就是分開的兩個欄位，可以直接看。
WHERE height::text = '1.56' 正是把欄位包進函數，WHERE 就用不到那個欄位的索引講的東西，而且這篇給了原文沒有的措辭。 作者的講法是：「轉型發生在欄位上會出事，發生在常數上沒關係」。（推論）這比「不要包函數」精確——判準是哪一邊被包，而那篇目前寫成「欄位要光禿禿地站在比較運算子一邊」，意思對但這個「常數那邊隨便你」的補充可以加上去。
反正規化要排在索引與物化視圖後面的第 2 步就是這篇。 那篇寫「量到真的慢——看執行計畫，不是憑感覺」。（推論）這篇是那一步的操作手冊，兩篇可以互鏈——那篇給順序，這篇給工具。
索引是拿寫入換查詢，寫多讀少的表加索引是純虧在這裡多了一個「讀那側也未必划算」的條件。 那篇算的是寫入成本 vs 查詢收益。這篇補的是：即使你願意付寫入成本，低基數欄位上的索引 planner 根本不會用。（推論）合起來的判準是：索引要划算需要兩個條件同時成立——寫入夠少，而且述詞夠有選擇性。
JOIN 貴在每一列都要去另一張表配對和這篇的 Nested Loop 可以對接，但這篇沒走到那裡。 那篇講三種 join 演算法與列數放大；這篇只在掃描類型清單裡列了 Nested Loop Join 一行，範例全是單表。（推論）這篇缺的正好是那篇有的，反過來那篇缺的是「怎麼從計畫裡把它看出來」。

待確認

cost 的單位是什麼？ 文章說「成本是 I/O 操作次數的估計」，但沒說 14.30 這個數字怎麼來的（seq_page_cost、cpu_tuple_cost 這些參數的加權），也沒說它為什麼不能拿來跨查詢比較。
width=159 和 width=259 在同一張表上不同查詢裡不一樣（SELECT * vs SELECT id 可以理解，但兩個 SELECT * 也不同）。沒有解釋。
BUFFERS 開了之後要看什麼？ 只說「顯示 buffer 使用」就結束了，沒有一個範例輸出。shared hit vs read 的差別才是這個選項的價值。
Bitmap Index Scan 完全沒有範例，只有清單裡一行定義。它是實務上最常見的計畫之一。
姐妹作〈Laboratory〉 有沒有補上這些缺口？（同一天發布，純實作版。）
作者預告的 Dead Tuples 分析那篇出了沒？

抽出的原子筆記

cost 和 actual time 的兩個數字是「啟動..總計」，不是範圍 — 修正原文的錯誤，含單位是毫秒
估計和實際差很多，代表資料庫對這張表的認識壞了 — quiz 的主線：修好猜測之前，計畫好壞無從判斷
EXPLAIN ANALYZE 會真的執行查詢 — 原文完全沒警告的安全問題
loops 大於 1 時，actual time 和 rows 是每次迴圈的平均 — 讀巢狀計畫最常見的誤判

併進既有筆記的：低選擇性欄位的索引處方（partial / 複合 / 函數 / trigram）→ 索引是拿寫入換查詢，寫多讀少的表加索引是純虧。

樹狀結構怎麼讀在另一份材料裡：執行計畫是一棵樹，從下往上讀。

知識冒險者

探索

EXPLAIN ANALYSE 完整指南（Victor Gardini）

EXPLAIN ANALYSE 完整指南（Victor Gardini）

統整

為什麼需要它：問題定位到最後一層

一個查詢在 PostgreSQL 裡經過的五個階段

掃描方式（scan types）

讀懂最基本的一份輸出

依資料型別逐個看計畫

主鍵（Index Only Scan）

VARCHAR（Seq Scan ＋ Filter）

INTEGER（低基數欄位）

DECIMAL / NUMERIC

TIMESTAMP

BOOLEAN

兩個參數

複雜查詢：InitPlan 與 One-Time Filter

作者的收尾

反覆出現的關鍵字

矛盾點

文章內部

與 vault 既有筆記

待確認

抽出的原子筆記

關係圖譜

目錄