初識EXPLAIN的困惑

每個新晉DBA接觸PostgreSQL時學到的第一個命令就是"EXPLAIN"。但第一次嘗試理解它的輸出時，往往會遇到令人困惑的情況：

Sort  (cost=238.32..240.39 rows=826 width=961)
   Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
   ->  Hash Join  (cost=1.25..198.30 rows=826 width=961)
         Hash Cond: (p.pronamespace = n.oid)
         ->  Seq Scan on pg_proc p  (cost=0.00..182.94 rows=1009 width=692)
               Filter: pg_function_is_visible(oid)
         ->  Hash  (cost=1.14..1.14 rows=9 width=117)
               ->  Seq Scan on pg_namespace n  (cost=0.00..1.14 rows=9 width=117)
                     Filter: (nspname <> ALL ('{pg_catalog,information_schema}'::name[]))
(9 rows)

這個復雜的執行計劃輸出對初學者來說簡直像天書一般。讓我們從基礎開始，逐步揭開EXPLAIN的神秘面紗。

PostgreSQL的智能之處

首先需要理解一個關鍵概念：PostgreSQL"知道"你的數據。它會維護詳盡的元信息：

• 表行數統計
• 不同值的分布
• 最常見值
• 數據分布直方圖

對于大型表，這些統計基于隨機抽樣，但總體而言，PostgreSQL對數據特性的把握相當準確。正是基于這些統計信息，查詢規劃器才能做出明智的執行計劃決策。

從簡單案例開始

讓我們從一個最簡單的查詢開始：

EXPLAIN SELECT * FROM test WHERE i = 1;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan ON test  (cost=0.00..40.00 ROWS=12 width=4)
   FILTER: (i = 1)
(2 ROWS)

這個執行計劃包含幾個關鍵元素：

1. 操作類型：這里是順序掃描(Seq Scan)
2. 成本估算：0.00..40.00
3. 行數估算：12行
4. 行寬估算：4字節

理解執行計劃的結構

執行計劃是一個樹形結構，每個節點代表一個操作：

• 上層節點依賴下層節點的數據
• 每個節點顯示其特有的信息
• 縮進表示操作間的層級關系

在我們的簡單例子中，只有一個操作節點（順序掃描）及其過濾條件。

成本估算的奧秘

成本估算值(cost)可能是最令人困惑的部分。需要明確幾點：

1. 成本不是時間單位，而是一個抽象的相對值

2. 基于postgresql.conf中的成本參數計算：

   seq_page_cost = 1.0      # 順序頁讀取成本
   random_page_cost = 4.0   # 隨機頁讀取成本 
   cpu_tuple_cost = 0.01    # 處理每行的CPU成本
   cpu_index_tuple_cost = 0.005  # 索引掃描的CPU成本
   cpu_operator_cost = 0.0025   # 操作符執行的CPU成本
   

成本值以啟動成本..總成本的形式呈現。啟動成本是該操作返回第一行前需要的工作量，總成本是返回所有行的總工作量。

索引使用的決策邏輯

考慮這個表和查詢：

CREATE TABLE t (
    id serial PRIMARY KEY,
    some_column integer,
    something text
);
CREATE INDEX q ON t(some_column);

EXPLAIN SELECT * FROM t WHERE some_column = 123;

PostgreSQL會根據統計信息決定是否使用索引：

• 對于小表（如只有幾行），順序掃描通常更快
• 對于大表且匹配行很少的情況，索引掃描更優
• 對于大表且匹配行很多的情況，索引掃描反而更慢

執行計劃的三種形態

讓我們觀察同一個查詢在不同設置下的執行計劃變化：

1.默認情況（使用索引掃描）：

EXPLAIN SELECT * FROM test WHERE id = 50;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 INDEX Scan USING test_pkey ON test  (cost=0.28..8.29 ROWS=1 width=36)

2.禁用索引掃描（使用位圖掃描）：

SET enable_indexscan = false;
EXPLAIN SELECT * FROM test WHERE id = 50;

                               QUERY PLAN                               
------------------------------------------------------------------------
 Bitmap Heap Scan ON test  (cost=4.28..8.30 ROWS=1 width=13)
   ->  Bitmap INDEX Scan ON test_pkey  (cost=0.00..4.28 ROWS=1 width=0)

3. 禁用所有索引相關掃描（退化為順序掃描）：

SET enable_bitmapscan = false;
EXPLAIN SELECT * FROM test WHERE id = 50;

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan ON test  (cost=0.00..18.50 ROWS=1 width=13)

比較這三種情況的成本估算，可以清楚地看到為什么PostgreSQL默認選擇索引掃描——它的總成本最低(8.29)。

實際執行分析

EXPLAIN ANALYZE會實際執行查詢并提供真實數據：

EXPLAIN ANALYZE SELECT * FROM t LIMIT 100;

                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 LIMIT  (cost=0.00..9.33 ROWS=100 width=608) (actual TIME=0.008..0.152 ROWS=100 loops=1)
   ->  Seq Scan ON t  (cost=0.00..93333.86 ROWS=999986 width=608) (actual TIME=0.007..0.133 ROWS=100 loops=1)
 Total runtime: 0.181 ms

這里新增了四組實際數據：

1. actual time：實際執行時間(啟動..完成)
2. rows：實際返回行數
3. loops：該操作被執行次數
4. Total runtime：查詢總耗時

執行計劃中的關鍵陷阱

1. 多次循環執行：某些操作可能被執行多次，導致總時間遠超單次執行時間
2. 行數估算錯誤：統計信息不準確會導致糟糕的計劃選擇
3. 函數執行特性：某些函數(如PL/pgSQL函數)必須完全執行才能返回結果

總結與展望

理解EXPLAIN輸出是優化PostgreSQL查詢的基礎。本文涵蓋了：

• 執行計劃的基本結構
• 成本估算的含義
• 索引使用決策邏輯
• 實際執行數據的解讀

在后續文章中，我們將深入探討：

1. 各種執行操作（連接、排序等）的工作原理
2. PostgreSQL統計系統的工作機制
3. 高級執行計劃分析技巧

掌握這些知識將幫助你有效診斷和解決查詢性能問題。
關注以下公眾號，獲取更多PG技術文章。