你確定讀懂了PostgreSQL執行計劃嗎？
在執行任何 SQL 語句之前，PostgreSQL 優化器都會為它創建一個執行計劃（Query Plan）。執行計劃描述了 SQL 語句的具體實現步驟，例如使用全表掃描還是索引查找的方式獲取表中的數據，連接查詢使用 Nested Loop Join、Hash Join 還是 Sort Merge Join 算法，以及連接的順序等等。
當我們遇到慢查詢等性能問題時，通常可以先查看 SQL 語句的執行計劃，因此本文給大家詳細介紹一下如何獲取并解讀 PostgreSQL 執行計劃。
獲取執行計劃
PostgreSQL 提供了 EXPLAIN 語句，可以很方便地獲取 SQL 語句的執行計劃。EXPLAIN 語句的基本語法如下：
EXPLAIN statement;我們首先創建初始化數據：
CREATE TABLE test(
id INTEGER PRIMARY KEY,
vc VARCHAR(100),
vn NUMERIC,
vd DATE,
other char(100) DEFAULT 'N/A' NOT NULL
);
INSERT INTO test (id, vc, vn, vd)
SELECT id, 's'||random(), 100*random(),'2024-01-01'::date+(100*random())::int
FROM GENERATE_SERIES(1, 10000) id;
ANALYZE test;最后的 ANALYZE 命令是為了收集表的統計信息，幫助查詢優化器做出合理的選擇。
提示：PostgreSQL 優化器需要知道最新的數據庫統計信息（pg_statistic）才能選擇合適的執行計劃，通常 autovacuum 后臺守護進程會定期更新統計信息。但是，如果某個表近期執行了大量數據更新，我們可以執行 ANALYZE 命令更新它的統計信息。
以下是一個簡單的 EXPLAIN 示例：
EXPLAIN SELECT * FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|PostgreSQL 執行計劃結果包含幾部分內容：操作（Seq Scan on test）、成本（cost）、預估返回的行數（rows）以及預估每行數據的平均寬度（width），單位為字節。
其中，最重要的信息是成本，它的單位一般是磁盤頁讀取次數。成本包含兩個數字，分別代表返回第一行數據之前的啟動成本和返回全部結果的總成本。對于大多數查詢而言，我們需要關注總成本；但是某些情況下（例如 EXISTS 子查詢），查詢計劃器會選擇最小的啟動成本，因為執行器只需要獲取一行數據。另外，如果我們使用了 LIMIT 子句限制返回的行數，查詢計劃器會基于兩個成本計算一個合適的中間值。
EXPLAIN 語句還支持一些選項，其中需要重點注意的一個選項就是 ANALYZE，因為它不僅顯示預估的執行計劃，還會實際執行相應的語句并且返回執行時間統計。例如：
EXPLAIN ANALYZE
SELECT * FROM test;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141) (actual time=0.021..1.374 rows=10000 loops=1)|
Planning Time: 0.083 ms |
Execution Time: 1.890 ms |可以看出，執行計劃結果中增加了實際運行時間（actual time）統計，包括每個操作節點消耗的時間（毫秒）、返回的數據行數以及執行的次數。Planning Time 是生成執行計劃的時間；Execution Time 是執行語句的實際時間，不包括 Planning Time。ANALYZE 選項通常可以用于檢查查詢計劃器的評估是否準確。
雖然 ANALYZE 選項忽略了 SELECT 語句返回的結果，但是對于 INSERT、UPDATE、DELETE 等語句，它仍然會修改表中的數據，為了避免這種副作用，我們可以在事務內部獲取執行計劃，然后回滾事務：
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;其他 EXPLAIN 選項的介紹可以參考下文。
解讀執行計劃
PostgreSQL 執行計劃的結構是一棵由計劃節點組成的樹，EXPLAIN 命令的每一行對應一個節點。
每一行節點除了匯總信息之外，還可能包含縮進行，顯示了完成該節點的底層操作。節點的執行順序按照縮進來判斷，縮進越多的越先執行，同樣縮進的從上至下執行。第一行顯示了預估的總成本，它也是優化器最小化的目標。
執行計劃最底層的節點是掃描節點，通常用于從表中返回原始數據。我們就從簡單的單表訪問開始。
單表訪問
對于不同的表訪問方法，存在以下不同的掃描節點：
- 順序掃描（適用于返回大部分數據行）
- 索引掃描（適用于返回很少數據行）
- 位圖索引掃描（適用于返回較多數據行）
順序掃描就是全表掃描，它會依次讀取整個表中的數據。如果查詢條件字段沒有索引，一般需要執行順序掃描，例如：
EXPLAIN
SELECT *
FROM test
WHERE vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------+
Seq Scan on test (cost=0.00..348.00 rows=59 width=141)|
Filter: (vd = '2024-01-01'::date) |順序掃描對應的操作名稱為 Seq Scan，通常意味著我們需要基于查詢條件字段創建索引，從而通過索引優化查詢。
索引掃描意味著遍歷索引的 B-樹葉子節點，找到所有滿足條件的索引項，然后通過索引指針讀取表中的數據。例如：
EXPLAIN
SELECT *
FROM test
WHERE id = 1000;
QUERY PLAN |
----------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (id = 1000) |如果我們需要查詢的字段都可以通過索引獲取，PostgreSQL 可以使用僅索引掃描（Index-Only Scan）技術優化查詢。例如：
CREATE INDEX idx_test_vn ON test(vn,id);
EXPLAIN
SELECT vn, id
FROM test
WHERE vn = 1000;
QUERY PLAN |
----------------------------------------------------------------------------+
Index Only Scan using idx_test_vn on test (cost=0.29..4.30 rows=1 width=16)|
Index Cond: (vn = '1000'::numeric) |索引 idx_test_vn 包含了 vn 字段和 id 字段，查詢語句不需要訪問表中的數據即可返回查詢結果。
提示：PostgreSQL 提供了覆蓋索引（Covering Index），可以進一步實現 Index-Only Scan 優化。另外，Index-Only Scan 優化需要滿足一個條件：MVCC 可見性，因為索引中并沒有存儲數據的可見性信息，只有表的元組中存儲了該信息。
索引掃描每次找到一個滿足條件的索引項時，都會基于元組指針再次訪問表中的數據（回表），這是一種隨機 IO。如果索引掃描只返回很少的數據行，它是一個很好的訪問方法。但是如果掃描索引返回的數據行比較多，大量的隨機回表會導致性能下降；一個優化的方法就是把回表的隨機 IO 變成順序 IO，為此 PostgreSQL 引入了位圖索引掃描。
位圖索引掃描（Bitmap Index Scan）的原理是一次掃描索引獲取所有滿足條件的元組指針，然后在內存中基于“位圖”數據結構進行排序，最后按照元組指針的物理順序訪問表（Bitmap Heap Scan）中的數據。例如：
CREATE INDEX idx_test_vd ON test(vd);
EXPLAIN
SELECT *
FROM test
WHERE vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------------------------+
Bitmap Heap Scan on test (cost=4.75..139.99 rows=60 width=141) |
Recheck Cond: (vd = '2024-01-01'::date) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..4.74 rows=60 width=0)|
Index Cond: (vd = '2024-01-01'::date) |該查詢語句返回 60 行數據，使用索引掃描的話，還需要 60 次回表。因此，PostgreSQL 選擇了位圖索引的訪問方法。
Recheck Cond 發生在回表階段，因為如果基于元組構建位圖導致位圖過大，就會基于數據頁（Page）構建位圖（有損方式），也就是只記錄了哪些數據頁包含了所需的數據行，所以在讀取數據頁之后需要再次檢查具體的元組。對于無損方式構建的位圖，也會出現 Recheck Cond 節點，但是并不執行檢查操作。
位圖索引掃描更常見的一種情況是查詢條件組合使用了多個索引時，例如：
EXPLAIN
SELECT *
FROM test
WHERE vn = 1000 OR vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------------------------------+
Bitmap Heap Scan on test (cost=9.06..146.25 rows=61 width=141) |
Recheck Cond: ((vn = '1000'::numeric) OR (vd = '2024-01-01'::date)) |
-> BitmapOr (cost=9.06..9.06 rows=61 width=0) |
-> Bitmap Index Scan on idx_test_vn (cost=0.00..4.29 rows=1 width=0) |
Index Cond: (vn = '1000'::numeric) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..4.74 rows=60 width=0)|
Index Cond: (vd = '2024-01-01'::date) |查詢首先基于 idx_test_vn 以及 idx_test_vd 進行了位圖索引掃描，然后進行了位圖合并（BitmapOr），最后基于位圖結果進行回表。
位圖索引掃描存在一個副作用，就是查詢結果不再按照索引順序返回，無法通過索引優化 ORDER BY。例如：
EXPLAIN
SELECT *
FROM test
WHERE vd BETWEEN '2024-01-01'::date AND '2024-01-31'::date
ORDER BY vd;
QUERY PLAN |
-------------------------------------------------------------------------------------+
Sort (cost=485.23..492.65 rows=2966 width=141) |
Sort Key: vd |
-> Bitmap Heap Scan on test (cost=46.69..314.18 rows=2966 width=141) |
Recheck Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-31'::date)) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..45.94 rows=2966 width=0) |
Index Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-31'::date))|查詢計劃中增加了額外的排序節點（Sort）。
提示：位圖索引掃描增加了內存和 CPU 的消耗，但是會減少磁盤 IO。
除了表之外，還有一些特殊的數據源（例如 VALUES 子句和 FROM 子句中的集合函數）擁有特殊的掃描類型。例如：
EXPLAIN
SELECT *
FROM (VALUES(1,'sql')) t(id,v);
QUERY PLAN |
-----------------------------------------+
Result (cost=0.00..0.01 rows=1 width=36)|
EXPLAIN
SELECT *
FROM pg_catalog.generate_series(1, 100);
QUERY PLAN |
--------------------------------------------------------------------+
Function Scan on generate_series (cost=0.00..1.00 rows=100 width=4)|多表連接
如果查詢涉及多表連接操作，執行計劃中的掃描節點之上將會顯示額外的 Join 節點。通常連接操作一次連接兩個表，如果查詢包含多個連接操作，按照順序進行連接，前兩個表連接的中間結果和下一個表進行連接。
PostgreSQL 實現了以下三種連接算法：
- 嵌套循環（Nested Loop）
- 哈希連接（Hash Join）
- 排序合并（Merge Join）
嵌套循環連接類似于編程語言中的嵌套 for 循環，首先從外部表（驅動表）中獲取滿足條件的數據，然后為每一行數據遍歷一次內部表（被驅動表），獲取所有匹配的數據。下圖演示了嵌套循環連接的執行過程：
以下查詢將 test 和它自己進行交叉連接：
EXPLAIN
SELECT *
FROM test t1
CROSS JOIN test t2;
QUERY PLAN |
-------------------------------------------------------------------------+
Nested Loop (cost=0.00..1250671.00 rows=100000000 width=282) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Materialize (cost=0.00..373.00 rows=10000 width=141) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|PostgreSQL 選擇了嵌套循環算法實現以上連接查詢，其中 Materialize 說明 t2 的掃描結果進行了緩存，極大地減少了磁盤訪問次數。
哈希連接使用其中一個表中滿足條件的記錄創建哈希表，然后掃描另一個表進行匹配。哈希連接的執行過程如下圖所示：
以下查詢仍然使用 test 進行自連接，但是指定了連接條件：
EXPLAIN
SELECT *
FROM test t1
JOIN test t2 ON t1.vc=t2.vc;
QUERY PLAN |
-------------------------------------------------------------------------+
Hash Join (cost=448.00..908.50 rows=10000 width=282) |
Hash Cond: ((t1.vc)::text = (t2.vc)::text) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Hash (cost=323.00..323.00 rows=10000 width=141) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|PostgreSQL 選擇了哈希連接算法實現以上連接查詢，并且使用 t2 表的數據創建哈希表。
排序合并連接先將兩個數據源按照連接字段進行排序（Sort），然后合并兩個已經排序的集合，返回滿足連接條件的結果。排序合并連接的執行過程如下圖所示：
以下查詢使用主鍵 id 字段進行連接，并且按照 t1 的主鍵進行排序：
EXPLAIN
SELECT *
FROM test t1
JOIN test t2 ON t1.id=t2.id
ORDER BY t1.id;
QUERY PLAN |
-------------------------------------------------------------------------------------+
Merge Join (cost=0.57..1142.57 rows=10000 width=282) |
Merge Cond: (t1.id = t2.id) |
-> Index Scan using test_pkey on test t1 (cost=0.29..496.29 rows=10000 width=141)|
-> Index Scan using test_pkey on test t2 (cost=0.29..496.29 rows=10000 width=141)|PostgreSQL 選擇了排序合并連接算法實現以上連接查詢，它可以避免額外的排序操作。
集合運算
集合運算符（UNION、INTERSECT、EXCEPT）用于將多個查詢語句的結果進行并集、交集、差集運算，它們也會在執行計劃中顯示單獨的節點。例如：
EXPLAIN
SELECT *
FROM test t1
UNION ALL
SELECT *
FROM test t2;
QUERY PLAN |
-------------------------------------------------------------------+
Append (cost=0.00..746.00 rows=20000 width=141) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141)|
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|其中，Append 節點意味著將兩個查詢的結果追加合并成一個結果。
以下是一個 INTERSECT 示例：
EXPLAIN
SELECT *
FROM test t1
INTERSECT
SELECT *
FROM test t2;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
SetOp Intersect (cost=8324.77..8624.77 rows=10000 width=666) |
-> Sort (cost=8324.77..8374.77 rows=20000 width=666) |
Sort Key: "*SELECT* 1".id, "*SELECT* 1".vc, "*SELECT* 1".vn, "*SELECT* 1".vd, "*SELECT* 1".other|
-> Append (cost=0.00..946.00 rows=20000 width=666) |
-> Subquery Scan on "*SELECT* 1" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Subquery Scan on "*SELECT* 2" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141) |其中，SetOp Intersect 節點代表了并集運算，它由一個 Append 節點和 Sort 節點組成，因為 INTERSECT 運算符需要去除重復記錄。
最后是一個 EXCEPT 示例：
EXPLAIN
SELECT *
FROM test t1
EXCEPT
SELECT *
FROM test t2;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
SetOp Except (cost=8324.77..8624.77 rows=10000 width=666) |
-> Sort (cost=8324.77..8374.77 rows=20000 width=666) |
Sort Key: "*SELECT* 1".id, "*SELECT* 1".vc, "*SELECT* 1".vn, "*SELECT* 1".vd, "*SELECT* 1".other|
-> Append (cost=0.00..946.00 rows=20000 width=666) |
-> Subquery Scan on "*SELECT* 1" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Subquery Scan on "*SELECT* 2" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141) |其中，SetOp Except 節點表示差集運算，同樣由一個 Append 節點和 Sort 節點組成。
排序分組
排序（ORDER BY）和分組（GROUP BY）也是查詢語句中常見的操作，它們都有專門的節點類型。例如：
EXPLAIN
SELECT *
FROM test
ORDER BY vd;
QUERY PLAN |
----------------------------------------------------------------+
Sort (cost=987.39..1012.39 rows=10000 width=141) |
Sort Key: vd |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|雖然 vd 字段存在索引，但是查詢需要返回全部數據，PostgreSQL 還是選擇了全表掃描加排序（Sort）的方式。
如果索引能夠同時完成數據過濾（WHERE）和排序，執行計劃中就不會出現 Sort 節點。例如：
EXPLAIN
SELECT *
FROM test
WHERE vn = 1000
ORDER BY id;
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = '1000'::numeric) |索引 idx_test_vn 包含了 vn 以及 id 字段。
PostgreSQL 實現了兩種分組算法：哈希聚合算法以及排序聚合算法。
哈希聚合算法使用一個臨時哈希表對數據進行分組聚合，完成數據哈希之后的結果就是分組結果。例如：
EXPLAIN
SELECT vc,count(*)
FROM test
GROUP BY vc;
QUERY PLAN |
---------------------------------------------------------------+
HashAggregate (cost=373.00..473.00 rows=10000 width=28) |
Group Key: vc |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=20)|vc 字段沒有索引，PostgreSQL 選擇了哈希聚合算法（HashAggregate）。
排序聚合算法首先將數據按照分組字段進行排序，將每個組內的數據都排列到一起，然后進行聚合操作。例如：
EXPLAIN
SELECT vc,count(*)
FROM test
GROUP BY vc
ORDER BY vc;
QUERY PLAN |
---------------------------------------------------------------------+
GroupAggregate (cost=987.39..1162.39 rows=10000 width=28) |
Group Key: vc |
-> Sort (cost=987.39..1012.39 rows=10000 width=20) |
Sort Key: vc |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=20)|考慮到查詢結果還需要進行排序，PostgreSQL 選擇了排序聚合算法（Sort + GroupAggregate）。
排序聚合算法還可以基于索引避免排序操作，例如：
EXPLAIN
SELECT vn,count(*)
FROM test
GROUP BY vn
ORDER BY vn;
QUERY PLAN |
----------------------------------------------------------------------------------------+
GroupAggregate (cost=0.29..504.29 rows=10000 width=20) |
Group Key: vn |
-> Index Only Scan using idx_test_vn on test (cost=0.29..354.29 rows=10000 width=12)|vn 字段存在索引，因此執行計劃中只有 GroupAggregate 節點，而沒有 Sort 節點。
限制結果
Top-N 查詢和分頁查詢通常只需要返回有限數量的結果，例如：
EXPLAIN ANALYZE
SELECT *
FROM test
ORDER BY vn
FETCH FIRST 5 ROWS ONLY;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.29..0.91 rows=5 width=141) (actual time=0.013..0.017 rows=5 loops=1) |
-> Index Scan using idx_test_vn on test (cost=0.29..1246.20 rows=10000 width=141) (actual time=0.012..0.015 rows=5 loops=1)|
Planning Time: 0.084 ms |
Execution Time: 0.030 ms |執行計劃中的 Limit 節點表示 PostgreSQL 在獲取足夠數據行之后停止底層操作，索引掃描（Index Scan）不僅避免了排序操作，而且只需要掃描 5 個索引條目（actual time=0.012…0.015 rows=5 loops=1）就可以終止掃描，這種優化技術被稱為管道（pipelined）操作。
Limit 操作的性能依賴于底層操作的實現，如果底層執行的是非管道操作，例如：
EXPLAIN ANALYZE
SELECT *
FROM test
ORDER BY vc
FETCH FIRST 5 ROWS ONLY;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------+
Limit (cost=489.10..489.11 rows=5 width=141) (actual time=3.361..3.362 rows=5 loops=1) |
-> Sort (cost=489.10..514.10 rows=10000 width=141) (actual time=3.360..3.360 rows=5 loops=1) |
Sort Key: vc |
Sort Method: top-N heapsort Memory: 27kB |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=141) (actual time=0.015..0.529 rows=10000 loops=1)|
Planning Time: 0.124 ms |
Execution Time: 3.384 ms |vc 字段沒有索引，所以需要執行額外的排序（Sort）。排序可能導致明顯的性能問題，因為 Limit 節點需要等待所有數據（actual time=0.015…0.529 rows=10000 loops=1）完成排序之后才能返回數據。
訪問謂詞與過濾謂詞
對于 WHERE 子句（謂詞），PostgreSQL 提供了三種不同的實現方法：
- 索引訪問謂詞
- 索引過濾謂詞
- 表級過濾謂詞
索引訪問謂詞（Index Access Predicate）指定了索引葉子節點遍歷的開始和結束條件。例如：
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE id BETWEEN 100 AND 120;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.71 rows=21 width=141) (actual time=0.007..0.011 rows=21 loops=1)|
Index Cond: ((id >= 100) AND (id <= 120)) |
Planning Time: 0.133 ms |
Execution Time: 0.024 ms |其中，Index Cond 表示索引掃描時基于該條件開始和結束，所以它屬于訪問謂詞。
索引過濾謂詞（Index Filter Predicate）在遍歷索引葉子節點時用于判斷是否返回該索引項，但是不會用于判斷遍歷的開始和結束條件，也就不會縮小索引掃描的范圍。例如：
CREATE INDEX idx_test_vdvc ON test(vd, vc);
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE vd BETWEEN '2024-01-01'::date AND '2024-01-02'::date AND vc = 'xxx';
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------+
Index Scan using idx_test_vdvc on test (cost=0.29..9.36 rows=1 width=141) (actual time=0.024..0.024 rows=0 loops=1)|
Index Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-02'::date) AND ((vc)::text = 'xxx'::text)) |
Planning Time: 0.124 ms |
Execution Time: 0.040 ms |idx_test_vdvc 索引基于 vd 和 vc 兩個字段，但是查詢條件中只有 vd 用于決定索引遍歷的開始條件和結束條件，vc 字段只能用于判斷是否返回該索引項。因為 vd 是范圍條件，導致索引節點中的 vc 字段不再具體順序性。PostgreSQL 執行計劃沒有區分索引訪問謂詞和索引過濾謂詞，統一顯示為 Index Cond。
注意：索引過濾謂詞看似使用了索引，但是隨著數據量的增長可能導致性能明顯下降，因為它沒有減少索引掃描的范圍。
對于以上查詢語句，如果我們創建 idx_test_vdvc 索引時把 vc 字段放在最前面，就可以充分利用索引優化性能，因為此時所有的謂詞都是所有訪問謂詞。
表級過濾謂詞（Table Level Filter Predicate）是指謂詞中的非索引字段在表級別進行判斷，這意味著數據庫需要讀取表中的數據然后再應用該條件。例如：
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE id = 100 AND other = 'N/A';
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141) (actual time=0.019..0.020 rows=1 loops=1)|
Index Cond: (id = 100) |
Filter: (other = 'N/A'::bpchar) |
Planning Time: 0.103 ms |
Execution Time: 0.037 ms |查詢使用了主鍵索引掃描（Index Scan），其中 id 是索引訪問謂詞（Index Cond），other 是表級過濾謂詞（Filter）。
提示：一般來說，對于相同的查詢語句，訪問謂詞的性能好于索引過濾謂詞，索引過濾謂詞的性能好于表級過濾謂詞。
輸出參數
最后我們介紹一下 EXPLAIN 語句的完整語法：
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
其中 option 可以為以下選項之一:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
GENERIC_PLAN [ boolean ]
BUFFERS [ boolean ]
WAL [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }其中，ANALYZE 和 VERBOSE 選項支持兩種指定方法；其他選項需要使用括號包含，多個選項使用逗號進行分隔。
statement 可以是以下語句之一：SELECT、INSERT、UPDATE、DELETE、MERGE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS、CREATE MATERIALIZED VIEW AS。
boolean 用于啟用或者禁用相關選項。TRUE、ON 或者 1 表示啟用，FALSE、OFF 或者 0 表示禁用。如果忽略了 boolean 設置，默認為啟用。
ANALYZE
ANALYZE 選項不僅顯示預估的執行計劃，還會實際執行相應的語句，并且返回執行時間和其他信息統計。該選項默認為 FALSE。
一方面，為了測量執行計劃中每個節點的執行時成本，當前 EXPLAIN ANALYZE 的實現在執行計劃中增加了一些分析開銷，因此執行 EXPLAIN ANALYZE 命令有時候會導致查詢比正常運行花費的時間明顯更長。具體的分析開銷取決于查詢語句以及數據庫運行的平臺，有可能查詢節點每次執行只需要很短的時間，但是操作系統獲取時間的調用反而更慢，可以使用 pg_test_timing 工具測量系統的計時開銷。
另一方面， EXPLAIN ANALYZE 不需要將查詢結果發送到客戶端，因此沒有包含網絡傳輸和轉換成本。
VERBOSE
VERBOSE 選項用于在執行計劃中顯示額外的信息。例如：
EXPLAIN VERBOSE
SELECT *
FROM test;
QUERY PLAN |
------------------------------------------------------------------+
Seq Scan on emerald.test (cost=0.00..323.00 rows=10000 width=141)|
Output: id, vc, vn, vd, other |以上 EXPLAIN VERBOSE 顯示了順序掃描節點輸出的字段列表（Output），以及包含模式名限定的表名（emerald.test）。
對于不同的操作節點，VERBOSE 選項還會顯示其他額外信息。該選項默認禁用。
COSTS
COSTS 選項用于輸出每個計劃節點的預估啟動成本和總成本，以及預估行數和平均長度。該選項默認啟用。例如：
EXPLAIN (COSTS)
SELECT *
FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|SETTINGS
SETTINGS 選項用于顯示配置參數，尤其是影響查詢計劃的非默認設置的參數。該選項默認禁用。例如：
EXPLAIN (SETTINGS)
SELECT *
FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|
Settings: search_path = 'hrdb, public, "$user"' |GENERIC_PLAN
PostgreSQL 16 版本增加了 GENERIC_PLAN 選項，可以為預編譯語句 生成通用執行計劃，這種執行計劃不依賴于綁定變量（例如 $1、$2等）的值。例如：
EXPLAIN (GENERIC_PLAN)
SELECT *
FROM test
WHERE vn = $1;
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = $1) |GENERIC_PLAN 選項默認禁用，而且不能和 ANALYZE 選項一起使用，因為 ANALYZE 需要執行語句。
另外，預編譯語句也可能使用定制執行計劃，也就是使用綁定變量的具體值創建執行計劃。例如：
PREPARE query_test(numeric)
AS
SELECT *
FROM test
WHERE vn = $1;
EXPLAIN EXECUTE query_test(10);
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = '10'::numeric) |
DEALLOCATE query_test;索引掃描的訪問謂詞中使用了具體的參數值（10）。
提示：運行時參數 plan_cache_mode 決定了預編譯語句使用通用執行計劃還是定制執行計劃。
BUFFERS
BUFFERS 選項用于顯示緩沖區使用情況，默認禁用。例如：
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM test
WHERE id = 1000;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141) (actual time=0.030..0.032 rows=1 loops=1)|
Index Cond: (id = 1000) |
Buffers: shared hit=3 |
Planning Time: 0.266 ms |
Execution Time: 0.071 ms |其中，shared hit 表示共享塊命中。
具體來說，BUFFERS 選項顯示的信息包括共享內存塊命中（hit）、讀取（read）、標記臟塊（dirtied）以及寫入（written）數量，本地內存塊命中（hit）、讀取（read）、標記臟塊（dirtied）以及寫入（written）數量，臨時內存塊的讀取（read）和寫入（written）數量。如果啟用了服務器參數 track_io_timing ，還會顯示讀寫數據文件塊和臨時文件塊的時間（毫秒）。
其中，一次命中意味著避免了一次磁盤讀取，因為所需數據塊已經存在緩存中。共享內存塊包含了普通表和索引的緩存數據，本地內存塊包含了臨時表和索引的緩存數據；臨時內存塊包含了排序、哈希、物化節點等操作使用的臨時數據。
臟塊的數量表示之前未改動，但是當前查詢修改的數據塊；寫入塊的數量表示之前被標記為臟塊，同時在當前查詢處理過程總被后臺進程刷新到磁盤的數據塊。上層節點顯示的數量包含了子節點的數量，對于 TEXT 輸出格式，只顯示非零數據值。
WAL
WAL 選項用于顯示有關預寫式日志記錄生成的信息。具體來說，包括記錄數、全頁鏡像數（fpi）以及生成的 WAL（字節）。如果 FORMAT 選項的值為 TEXT（默認值），只顯示非零信息。該選項只能在啟用 ANALYZE 選項時使用，默認為禁用。
TIMING
TIMING 選項用于顯示每個計劃節點的啟用時間和完成時間（毫秒），該選項只能在啟用 ANALYZE 選項時使用，默認為啟用。
某些平臺上重復讀取系統時間可能會明顯影響查詢性能，如果只關注實際返回的行數，可以在啟用 ANALYZE 選項時將該選項禁用。即使關閉了節點的計時功能，整個語句的運行時間仍然會統計并顯示。
SUMMARY
SUMMARY 選項用于在執行計劃之后顯示匯總信息（例如總的時間消耗）。如果啟用了 ANALYZE 選項，默認顯示匯總信息；否則默認不會顯示匯總信息。
對于 EXPLAIN EXECUTE 語句，Planning time 包含了從緩存中獲取執行計劃或者重新計劃消耗的時間。
FORMAT
FORMAT 選項用于指定執行計劃的輸出格式，可以使用 TEXT、XML、JSON 或者 YAML 格式。默認輸出格式為 TEXT，其他格式輸出的內容和 TEXT 格式相同，只是更方便程序處理。例如：
EXPLAIN (FORMAT JSON)
SELECT *
FROM test;
[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "test",
"Alias": "test",
"Startup Cost": 0.00,
"Total Cost": 323.00,
"Plan Rows": 10000,
"Plan Width": 141
}
}
]
