跳至主要內容
日沐 SunBathe

Appearance

EXPLAIN 完全解讀 ​

EXPLAIN 是 PostgreSQL 最強大的效能診斷工具。理解執行計畫，才能寫出高效的查詢。本篇將教你如何讀懂 EXPLAIN 輸出並找出問題。

一、 基本用法 ​

sql
-- 顯示執行計畫（不實際執行）
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 實際執行並顯示真實時間
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- 顯示更多資訊
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM users WHERE email = 'alice@example.com';

-- 格式化輸出
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE email = 'alice@example.com';
EXPLAIN (FORMAT YAML) SELECT * FROM users WHERE email = 'alice@example.com';

二、 讀懂執行計畫 ​

基本結構 ​

sql
EXPLAIN SELECT * FROM users WHERE id = 1;

輸出：

Index Scan using users_pkey on users  (cost=0.29..8.30 rows=1 width=100)
  Index Cond: (id = 1)

解析：

項目說明
Index Scan操作類型（使用索引掃描）
users_pkey使用的索引名稱
cost=0.29..8.30估算成本（啟動成本..總成本）
rows=1估計返回列數
width=100每列平均寬度（bytes）
Index Cond索引條件

EXPLAIN ANALYZE 輸出 ​

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

輸出：

Index Scan using users_pkey on users  (cost=0.29..8.30 rows=1 width=100) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (id = 1)
Planning Time: 0.082 ms
Execution Time: 0.035 ms

新增資訊：

項目說明
actual time=0.015..0.016實際耗時（毫秒）
rows=1實際返回列數
loops=1執行次數
Planning Time規劃耗時
Execution Time執行總耗時

三、 常見掃描類型 ​

Seq Scan（全表掃描） ​

Seq Scan on users  (cost=0.00..10000.00 rows=500000 width=100)
  Filter: (age > 18)

何時發生：

  • 沒有適合的索引
  • 查詢返回大量資料（優化器認為全表掃描更快）
  • 小表（索引反而更慢）

Index Scan（索引掃描） ​

Index Scan using idx_users_email on users
  Index Cond: (email = 'alice@example.com')

特點：直接使用索引定位，再回表取資料。

Index Only Scan（僅索引掃描） ​

Index Only Scan using idx_users_email_name on users
  Index Cond: (email = 'alice@example.com')

特點：所有需要的欄位都在索引中，完全不用回表。是最快的掃描方式。

Bitmap Scan（位圖掃描） ​

Bitmap Heap Scan on users
  Recheck Cond: (age > 18 AND age < 30)
  ->  Bitmap Index Scan on idx_users_age
        Index Cond: (age > 18 AND age < 30)

特點：

  1. 先用索引建立「位圖」標記符合條件的列
  2. 再按位圖順序掃描表
  3. 適合結果分散但數量中等的查詢

四、 常見 JOIN 類型 ​

Nested Loop Join ​

Nested Loop  (cost=0.29..16.35 rows=1 width=200)
  ->  Index Scan using idx_orders_customer on orders
  ->  Index Scan using users_pkey on users

原理：對外層的每一列，逐一查找內層匹配。

適合：內層表有索引、外層結果少。

Hash Join ​

Hash Join  (cost=10.00..130.00 rows=100 width=200)
  Hash Cond: (orders.customer_id = customers.id)
  ->  Seq Scan on orders
  ->  Hash
        ->  Seq Scan on customers

原理：

  1. 把較小的表載入記憶體建立 hash table
  2. 掃描較大的表，用 hash 查找匹配

適合：無索引的大表 JOIN。

Merge Join ​

Merge Join  (cost=100.00..200.00 rows=1000 width=200)
  Merge Cond: (orders.id = order_items.order_id)
  ->  Sort
        ->  Seq Scan on orders
  ->  Sort
        ->  Seq Scan on order_items

原理：兩邊都先排序，然後同步前進比對。

適合：兩邊都很大且已排序（或有排序索引）。

五、 成本解讀 ​

cost=10.00..150.00
     ↑        ↑
   啟動成本  總成本
  • 啟動成本：返回第一列前的成本
  • 總成本：返回所有列的成本

成本單位：不是時間，是相對單位。預設 seq_page_cost=1、random_page_cost=4。

成本計算因素 ​

因素影響
掃描的頁數磁碟 I/O 成本
處理的列數CPU 成本
過濾條件複雜度CPU 成本
排序可能需要磁碟暫存

六、 問題診斷 ​

問題 1：估算值與實際值差異大 ​

(rows=10) vs (actual rows=10000)

原因：統計資訊過時。

解法：

sql
ANALYZE users;  -- 更新單張表統計
ANALYZE;        -- 更新所有表

問題 2：全表掃描但有索引 ​

Seq Scan on users
  Filter: (status = 'active')
  Rows Removed by Filter: 990000

可能原因：

  1. 條件返回大部分資料（索引無意義）
  2. 欄位沒有索引
  3. 條件使用了函數（如 LOWER(email) = ?）
  4. 隱式型別轉換

檢查：

sql
-- 確認索引存在
\di users

-- 如果使用函數，建立表達式索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

問題 3：排序太慢 ​

Sort  (cost=50000.00..55000.00 rows=500000 width=100)
  Sort Key: created_at
  Sort Method: external merge  Disk: 100MB

原因：work_mem 不足，排序用了磁碟。

解法：

sql
SET work_mem = '256MB';  -- 當前 session
-- 或調整 postgresql.conf

問題 4：Hash Join 溢出磁碟 ​

Hash  (cost=...)
  Buckets: 1024  Batches: 16  Memory Usage: 8MB

Batches > 1 表示記憶體不足，分批處理。

解法：增加 work_mem。

七、 BUFFERS 選項 ​

sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 1;

輸出：

Index Scan using users_pkey on users
  Buffers: shared hit=3
項目說明
shared hit從快取讀取的頁數（好）
shared read從磁碟讀取的頁數（慢）
shared dirtied弄髒的頁數
shared written寫入的頁數

高 hit 比例 = 快取效率好。

八、 執行計畫視覺化 ​

pgAdmin ​

pgAdmin 內建圖形化執行計畫檢視器。

explain.depesz.com ​

貼上 EXPLAIN ANALYZE 輸出，會產生彩色分析報告。

auto_explain 擴充 ​

自動記錄慢查詢的執行計畫：

sql
-- postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'  -- 超過 1 秒就記錄
auto_explain.log_analyze = on

九、 實戰範例 ​

慢查詢分析 ​

sql
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT o.id, c.name, SUM(oi.quantity * oi.price) as total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > '2024-01-01'
GROUP BY o.id, c.name
ORDER BY total DESC
LIMIT 10;

分析輸出：

  1. 看 Execution Time 確認總耗時
  2. 從下往上看，找出耗時最多的節點
  3. 比較 rows 估算值與實際值
  4. 檢查是否有 Seq Scan 可改用索引
  5. 檢查是否有 Disk Sort

優化步驟 ​

sql
-- 1. 確保連接欄位有索引
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- 2. 時間範圍查詢用索引
CREATE INDEX idx_orders_created ON orders(created_at);

-- 3. 增加 work_mem 避免磁碟排序
SET work_mem = '256MB';

-- 4. 再次 EXPLAIN 確認改善

十、 常用參數調整 ​

sql
-- 臨時禁用某種掃描（測試用）
SET enable_seqscan = off;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;

> **僅用於診斷**

這些設定是為了測試，不要用在生產環境。

總結 ​

掃描類型特點適合情況
Seq Scan全表掃描小表、返回大部分資料
Index Scan索引掃描 + 回表少量精準查詢
Index Only Scan純索引所有欄位都在索引中
Bitmap Scan位圖批量掃描中等結果量

診斷步驟：

  1. EXPLAIN ANALYZE 取得真實執行資料
  2. 比較估算 vs 實際列數
  3. 找出最耗時的節點
  4. 檢查掃描類型是否合理
  5. 加索引或調整參數

進階挑戰 ​

  1. 找出你專案中最慢的 5 個查詢（使用 pg_stat_statements），並用 EXPLAIN ANALYZE 分析原因
  2. 比較同一查詢在開啟/關閉 enable_seqscan 時的執行計畫差異
  3. 使用 auto_explain 模組自動記錄超過 1 秒的查詢計畫

延伸閱讀與資源 ​

← 上一章：進階索引類型 | 返回專題首頁 | 下一章：查詢調優實戰 →