什麼是 EXPLAIN，以及怎麼用它來分析查詢

想像一下，你正在開發一個應用程式，結果某個查詢突然變成資料庫裡最貴的那一個。你開始發現 app 會卡住或出現小當機。這時候 EXPLAIN 就是你的救星，能幫你搞清楚到底是哪裡出問題。根據 EXPLAIN 的分析來優化查詢，可以幫你省下資源、節省時間，還能讓使用者對你的 app 印象更好。

EXPLAIN 就像讓你能窺探 PostgreSQL 裡面到底怎麼執行查詢。它會告訴你會不會用到 index，還是會整張表都掃過一遍（full table scan），optimizer 會做哪些步驟、順序怎麼排，還有中間結果大概多大。

換句話說，EXPLAIN 讓你知道查詢執行時會發生什麼事：這個查詢「重不重」、大概會處理幾筆資料、會用到哪些資源。當查詢突然變慢時，這就是你找出原因的神兵利器。

EXPLAIN 就像黑暗中的手電筒：有了它你就能看到底下發生什麼事，還有哪裡出包了。

EXPLAIN 指令語法

來看看 EXPLAIN 的基本語法：

EXPLAIN 你的_SQL_查詢;

查詢範例：

EXPLAIN SELECT * FROM students WHERE age > 20;

當你執行這個指令時，PostgreSQL 不會真的執行查詢。它只會給你一份執行計畫。就像蓋房子前先看設計圖一樣——先知道會做什麼，才不會一不小心搞砸。

這是個輸出範例：

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

這個輸出看起來可能有點嚇人，不過別擔心——我們馬上來拆解它的重點。

基本執行計畫解析

我們來拆解一下這個結果：

1. Seq Scan on students —— 這代表 PostgreSQL 會把 students 這張表整個掃過一遍（sequential scan）。這不一定是壞事，但如果表很大，Seq Scan 可能會很慢。

2. cost=0.00..35.00 —— 這是這個操作的成本估算：

   • Startup Cost：這個操作一開始的成本（這裡是 0.00）。
   • Total Cost：整個操作跑完的總成本（這裡是 35.00）。

3. rows=7 —— PostgreSQL 預估 age > 20 這個條件會回傳 7 筆資料。這叫「基數」（cardinality）。如果你看到很奇怪的預估值，可能是你的表統計資料過時了。

4. width=37 —— 這是一筆資料的平均大小（byte）。

5. Filter: (age > 20) —— 這表示 PostgreSQL 會對每一筆資料都檢查這個條件。

所以 EXPLAIN 的輸出讓你知道 PostgreSQL 的策略和假設。你可以用這些資訊來優化查詢。

EXPLAIN 指令選項

雖然 EXPLAIN 的基本輸出已經很有用，你還可以用下面這些選項讓它更強大：

ANALYZE

加上這個選項，PostgreSQL 不只會給你執行計畫，還會真的執行查詢，然後給你實際的數據。範例：

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

這樣你就能比較 PostgreSQL 的預估和實際執行結果，看看準不準。

VERBOSE

會顯示更多細節，適合想深入分析的時候用。範例：

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

會顯示查詢執行時用到的記憶體 buffer。要跟 ANALYZE 一起用：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

如果你想隱藏或顯示成本資訊（cost），可以用這個選項：

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

執行計畫可以用不同格式輸出，比如 JSON 或 XML。範例：

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

EXPLAIN 使用範例

假設你有一個 university 資料庫，裡面有 students 這張表。你想找出所有年齡大於 20 歲的學生：

EXPLAIN SELECT * FROM students WHERE age > 20;

可能會得到這樣的輸出：

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

就像前面說的，這是 sequential scan（Seq Scan），對大表來說可能不太有效率。

現在我們來對 age 欄位建立一個 index，看看計畫會不會變：

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

輸出：

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

這時 PostgreSQL 就會用 index scan（Index Scan），通常對大表來說會快很多。

常見問題與錯誤

為什麼我的查詢有 index 還是很慢？

可能是查詢回傳的資料太多，這樣用 index 反而沒什麼好處。也有可能 index 品質不好或沒更新。

如果 EXPLAIN 的輸出看不懂怎麼辦？

先從簡單的查詢開始，一次看一個執行計畫的節點。

怎麼知道表的統計資料是不是過時了？

執行 ANALYZE students

什麼時候要用沒有 ANALYZE 的 EXPLAIN？

如果你只想看計畫，不想真的執行查詢（像是會改資料的查詢）。