你確定讀懂了PostgreSQL執(zhí)行計劃嗎？
在執(zhí)行任何 SQL 語句之前，PostgreSQL 優(yōu)化器都會為它創(chuàng)建一個執(zhí)行計劃（Query Plan）。執(zhí)行計劃描述了 SQL 語句的具體實現(xiàn)步驟，例如使用全表掃描還是索引查找的方式獲取表中的數(shù)據(jù)，連接查詢使用 Nested Loop Join、Hash Join 還是 Sort Merge Join 算法，以及連接的順序等等。
當(dāng)我們遇到慢查詢等性能問題時，通?？梢韵炔榭?SQL 語句的執(zhí)行計劃，因此本文給大家詳細介紹一下如何獲取并解讀 PostgreSQL 執(zhí)行計劃。
獲取執(zhí)行計劃
PostgreSQL 提供了 EXPLAIN 語句，可以很方便地獲取 SQL 語句的執(zhí)行計劃。EXPLAIN 語句的基本語法如下：
EXPLAIN statement;我們首先創(chuàng)建初始化數(shù)據(jù)：
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 命令是為了收集表的統(tǒng)計信息，幫助查詢優(yōu)化器做出合理的選擇。
提示：PostgreSQL 優(yōu)化器需要知道最新的數(shù)據(jù)庫統(tǒng)計信息（pg_statistic）才能選擇合適的執(zhí)行計劃，通常 autovacuum 后臺守護進程會定期更新統(tǒng)計信息。但是，如果某個表近期執(zhí)行了大量數(shù)據(jù)更新，我們可以執(zhí)行 ANALYZE 命令更新它的統(tǒng)計信息。
以下是一個簡單的 EXPLAIN 示例：
EXPLAIN SELECT * FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|PostgreSQL 執(zhí)行計劃結(jié)果包含幾部分內(nèi)容：操作（Seq Scan on test）、成本（cost）、預(yù)估返回的行數(shù)（rows）以及預(yù)估每行數(shù)據(jù)的平均寬度（width），單位為字節(jié)。
其中，最重要的信息是成本，它的單位一般是磁盤頁讀取次數(shù)。成本包含兩個數(shù)字，分別代表返回第一行數(shù)據(jù)之前的啟動成本和返回全部結(jié)果的總成本。對于大多數(shù)查詢而言，我們需要關(guān)注總成本；但是某些情況下（例如 EXISTS 子查詢），查詢計劃器會選擇最小的啟動成本，因為執(zhí)行器只需要獲取一行數(shù)據(jù)。另外，如果我們使用了 LIMIT 子句限制返回的行數(shù)，查詢計劃器會基于兩個成本計算一個合適的中間值。
EXPLAIN 語句還支持一些選項，其中需要重點注意的一個選項就是 ANALYZE，因為它不僅顯示預(yù)估的執(zhí)行計劃，還會實際執(zhí)行相應(yīng)的語句并且返回執(zhí)行時間統(tǒng)計。例如：
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 |可以看出，執(zhí)行計劃結(jié)果中增加了實際運行時間（actual time）統(tǒng)計，包括每個操作節(jié)點消耗的時間（毫秒）、返回的數(shù)據(jù)行數(shù)以及執(zhí)行的次數(shù)。Planning Time 是生成執(zhí)行計劃的時間；Execution Time 是執(zhí)行語句的實際時間，不包括 Planning Time。ANALYZE 選項通?？梢杂糜跈z查查詢計劃器的評估是否準(zhǔn)確。
雖然 ANALYZE 選項忽略了 SELECT 語句返回的結(jié)果，但是對于 INSERT、UPDATE、DELETE 等語句，它仍然會修改表中的數(shù)據(jù)，為了避免這種副作用，我們可以在事務(wù)內(nèi)部獲取執(zhí)行計劃，然后回滾事務(wù)：
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;其他 EXPLAIN 選項的介紹可以參考下文。
解讀執(zhí)行計劃
PostgreSQL 執(zhí)行計劃的結(jié)構(gòu)是一棵由計劃節(jié)點組成的樹，EXPLAIN 命令的每一行對應(yīng)一個節(jié)點。
每一行節(jié)點除了匯總信息之外，還可能包含縮進行，顯示了完成該節(jié)點的底層操作。節(jié)點的執(zhí)行順序按照縮進來判斷，縮進越多的越先執(zhí)行，同樣縮進的從上至下執(zhí)行。第一行顯示了預(yù)估的總成本，它也是優(yōu)化器最小化的目標(biāo)。
執(zhí)行計劃最底層的節(jié)點是掃描節(jié)點，通常用于從表中返回原始數(shù)據(jù)。我們就從簡單的單表訪問開始。
單表訪問
對于不同的表訪問方法，存在以下不同的掃描節(jié)點：
- 順序掃描（適用于返回大部分?jǐn)?shù)據(jù)行）
- 索引掃描（適用于返回很少數(shù)據(jù)行）
- 位圖索引掃描（適用于返回較多數(shù)據(jù)行）
順序掃描就是全表掃描，它會依次讀取整個表中的數(shù)據(jù)。如果查詢條件字段沒有索引，一般需要執(zhí)行順序掃描，例如：
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) |順序掃描對應(yīng)的操作名稱為 Seq Scan，通常意味著我們需要基于查詢條件字段創(chuàng)建索引，從而通過索引優(yōu)化查詢。
索引掃描意味著遍歷索引的 B-樹葉子節(jié)點，找到所有滿足條件的索引項，然后通過索引指針讀取表中的數(shù)據(jù)。例如：
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）技術(shù)優(yōu)化查詢。例如：
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 字段，查詢語句不需要訪問表中的數(shù)據(jù)即可返回查詢結(jié)果。
提示：PostgreSQL 提供了覆蓋索引（Covering Index），可以進一步實現(xiàn) Index-Only Scan 優(yōu)化。另外，Index-Only Scan 優(yōu)化需要滿足一個條件：MVCC 可見性，因為索引中并沒有存儲數(shù)據(jù)的可見性信息，只有表的元組中存儲了該信息。
索引掃描每次找到一個滿足條件的索引項時，都會基于元組指針再次訪問表中的數(shù)據(jù)（回表），這是一種隨機 IO。如果索引掃描只返回很少的數(shù)據(jù)行，它是一個很好的訪問方法。但是如果掃描索引返回的數(shù)據(jù)行比較多，大量的隨機回表會導(dǎo)致性能下降；一個優(yōu)化的方法就是把回表的隨機 IO 變成順序 IO，為此 PostgreSQL 引入了位圖索引掃描。
位圖索引掃描（Bitmap Index Scan）的原理是一次掃描索引獲取所有滿足條件的元組指針，然后在內(nèi)存中基于“位圖”數(shù)據(jù)結(jié)構(gòu)進行排序，最后按照元組指針的物理順序訪問表（Bitmap Heap Scan）中的數(shù)據(jù)。例如：
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 行數(shù)據(jù)，使用索引掃描的話，還需要 60 次回表。因此，PostgreSQL 選擇了位圖索引的訪問方法。
Recheck Cond 發(fā)生在回表階段，因為如果基于元組構(gòu)建位圖導(dǎo)致位圖過大，就會基于數(shù)據(jù)頁（Page）構(gòu)建位圖（有損方式），也就是只記錄了哪些數(shù)據(jù)頁包含了所需的數(shù)據(jù)行，所以在讀取數(shù)據(jù)頁之后需要再次檢查具體的元組。對于無損方式構(gòu)建的位圖，也會出現(xiàn) Recheck Cond 節(jié)點，但是并不執(zhí)行檢查操作。
位圖索引掃描更常見的一種情況是查詢條件組合使用了多個索引時，例如：
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），最后基于位圖結(jié)果進行回表。
位圖索引掃描存在一個副作用，就是查詢結(jié)果不再按照索引順序返回，無法通過索引優(yōu)化 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))|查詢計劃中增加了額外的排序節(jié)點（Sort）。
提示：位圖索引掃描增加了內(nèi)存和 CPU 的消耗，但是會減少磁盤 IO。
除了表之外，還有一些特殊的數(shù)據(jù)源（例如 VALUES 子句和 FROM 子句中的集合函數(shù)）擁有特殊的掃描類型。例如：
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)|多表連接
如果查詢涉及多表連接操作，執(zhí)行計劃中的掃描節(jié)點之上將會顯示額外的 Join 節(jié)點。通常連接操作一次連接兩個表，如果查詢包含多個連接操作，按照順序進行連接，前兩個表連接的中間結(jié)果和下一個表進行連接。
PostgreSQL 實現(xiàn)了以下三種連接算法：
- 嵌套循環(huán)（Nested Loop）
- 哈希連接（Hash Join）
- 排序合并（Merge Join）
嵌套循環(huán)連接類似于編程語言中的嵌套 for 循環(huán)，首先從外部表（驅(qū)動表）中獲取滿足條件的數(shù)據(jù)，然后為每一行數(shù)據(jù)遍歷一次內(nèi)部表（被驅(qū)動表），獲取所有匹配的數(shù)據(jù)。下圖演示了嵌套循環(huán)連接的執(zhí)行過程：
以下查詢將 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 選擇了嵌套循環(huán)算法實現(xiàn)以上連接查詢，其中 Materialize 說明 t2 的掃描結(jié)果進行了緩存，極大地減少了磁盤訪問次數(shù)。
哈希連接使用其中一個表中滿足條件的記錄創(chuàng)建哈希表，然后掃描另一個表進行匹配。哈希連接的執(zhí)行過程如下圖所示：
以下查詢?nèi)匀皇褂?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 選擇了哈希連接算法實現(xiàn)以上連接查詢，并且使用 t2 表的數(shù)據(jù)創(chuàng)建哈希表。
排序合并連接先將兩個數(shù)據(jù)源按照連接字段進行排序（Sort），然后合并兩個已經(jīng)排序的集合，返回滿足連接條件的結(jié)果。排序合并連接的執(zhí)行過程如下圖所示：
以下查詢使用主鍵 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 選擇了排序合并連接算法實現(xiàn)以上連接查詢，它可以避免額外的排序操作。
集合運算
集合運算符（UNION、INTERSECT、EXCEPT）用于將多個查詢語句的結(jié)果進行并集、交集、差集運算，它們也會在執(zhí)行計劃中顯示單獨的節(jié)點。例如：
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 節(jié)點意味著將兩個查詢的結(jié)果追加合并成一個結(jié)果。
以下是一個 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 節(jié)點代表了并集運算，它由一個 Append 節(jié)點和 Sort 節(jié)點組成，因為 INTERSECT 運算符需要去除重復(fù)記錄。
最后是一個 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 節(jié)點表示差集運算，同樣由一個 Append 節(jié)點和 Sort 節(jié)點組成。
排序分組
排序（ORDER BY）和分組（GROUP BY）也是查詢語句中常見的操作，它們都有專門的節(jié)點類型。例如：
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 字段存在索引，但是查詢需要返回全部數(shù)據(jù)，PostgreSQL 還是選擇了全表掃描加排序（Sort）的方式。
如果索引能夠同時完成數(shù)據(jù)過濾（WHERE）和排序，執(zhí)行計劃中就不會出現(xiàn) Sort 節(jié)點。例如：
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 實現(xiàn)了兩種分組算法：哈希聚合算法以及排序聚合算法。
哈希聚合算法使用一個臨時哈希表對數(shù)據(jù)進行分組聚合，完成數(shù)據(jù)哈希之后的結(jié)果就是分組結(jié)果。例如：
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）。
排序聚合算法首先將數(shù)據(jù)按照分組字段進行排序，將每個組內(nèi)的數(shù)據(jù)都排列到一起，然后進行聚合操作。例如：
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)|考慮到查詢結(jié)果還需要進行排序，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 字段存在索引，因此執(zhí)行計劃中只有 GroupAggregate 節(jié)點，而沒有 Sort 節(jié)點。
限制結(jié)果
Top-N 查詢和分頁查詢通常只需要返回有限數(shù)量的結(jié)果，例如：
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 |執(zhí)行計劃中的 Limit 節(jié)點表示 PostgreSQL 在獲取足夠數(shù)據(jù)行之后停止底層操作，索引掃描（Index Scan）不僅避免了排序操作，而且只需要掃描 5 個索引條目（actual time=0.012…0.015 rows=5 loops=1）就可以終止掃描，這種優(yōu)化技術(shù)被稱為管道（pipelined）操作。
Limit 操作的性能依賴于底層操作的實現(xiàn)，如果底層執(zhí)行的是非管道操作，例如：
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 字段沒有索引，所以需要執(zhí)行額外的排序（Sort）。排序可能導(dǎo)致明顯的性能問題，因為 Limit 節(jié)點需要等待所有數(shù)據(jù)（actual time=0.015…0.529 rows=10000 loops=1）完成排序之后才能返回數(shù)據(jù)。
訪問謂詞與過濾謂詞
對于 WHERE 子句（謂詞），PostgreSQL 提供了三種不同的實現(xiàn)方法：
- 索引訪問謂詞
- 索引過濾謂詞
- 表級過濾謂詞
索引訪問謂詞（Index Access Predicate）指定了索引葉子節(jié)點遍歷的開始和結(jié)束條件。例如：
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 表示索引掃描時基于該條件開始和結(jié)束，所以它屬于訪問謂詞。
索引過濾謂詞（Index Filter Predicate）在遍歷索引葉子節(jié)點時用于判斷是否返回該索引項，但是不會用于判斷遍歷的開始和結(jié)束條件，也就不會縮小索引掃描的范圍。例如：
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 用于決定索引遍歷的開始條件和結(jié)束條件，vc 字段只能用于判斷是否返回該索引項。因為 vd 是范圍條件，導(dǎo)致索引節(jié)點中的 vc 字段不再具體順序性。PostgreSQL 執(zhí)行計劃沒有區(qū)分索引訪問謂詞和索引過濾謂詞，統(tǒng)一顯示為 Index Cond。
注意：索引過濾謂詞看似使用了索引，但是隨著數(shù)據(jù)量的增長可能導(dǎo)致性能明顯下降，因為它沒有減少索引掃描的范圍。
對于以上查詢語句，如果我們創(chuàng)建 idx_test_vdvc 索引時把 vc 字段放在最前面，就可以充分利用索引優(yōu)化性能，因為此時所有的謂詞都是所有訪問謂詞。
表級過濾謂詞（Table Level Filter Predicate）是指謂詞中的非索引字段在表級別進行判斷，這意味著數(shù)據(jù)庫需要讀取表中的數(shù)據(jù)然后再應(yīng)用該條件。例如：
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）。
提示：一般來說，對于相同的查詢語句，訪問謂詞的性能好于索引過濾謂詞，索引過濾謂詞的性能好于表級過濾謂詞。
輸出參數(shù)
最后我們介紹一下 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 用于啟用或者禁用相關(guān)選項。TRUE、ON 或者 1 表示啟用，F(xiàn)ALSE、OFF 或者 0 表示禁用。如果忽略了 boolean 設(shè)置，默認(rèn)為啟用。
ANALYZE
ANALYZE 選項不僅顯示預(yù)估的執(zhí)行計劃，還會實際執(zhí)行相應(yīng)的語句，并且返回執(zhí)行時間和其他信息統(tǒng)計。該選項默認(rèn)為 FALSE。
一方面，為了測量執(zhí)行計劃中每個節(jié)點的執(zhí)行時成本，當(dāng)前 EXPLAIN ANALYZE 的實現(xiàn)在執(zhí)行計劃中增加了一些分析開銷，因此執(zhí)行 EXPLAIN ANALYZE 命令有時候會導(dǎo)致查詢比正常運行花費的時間明顯更長。具體的分析開銷取決于查詢語句以及數(shù)據(jù)庫運行的平臺，有可能查詢節(jié)點每次執(zhí)行只需要很短的時間，但是操作系統(tǒng)獲取時間的調(diào)用反而更慢，可以使用 pg_test_timing 工具測量系統(tǒng)的計時開銷。
另一方面， EXPLAIN ANALYZE 不需要將查詢結(jié)果發(fā)送到客戶端，因此沒有包含網(wǎng)絡(luò)傳輸和轉(zhuǎn)換成本。
VERBOSE
VERBOSE 選項用于在執(zhí)行計劃中顯示額外的信息。例如：
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 顯示了順序掃描節(jié)點輸出的字段列表（Output），以及包含模式名限定的表名（emerald.test）。
對于不同的操作節(jié)點，VERBOSE 選項還會顯示其他額外信息。該選項默認(rèn)禁用。
COSTS
COSTS 選項用于輸出每個計劃節(jié)點的預(yù)估啟動成本和總成本，以及預(yù)估行數(shù)和平均長度。該選項默認(rèn)啟用。例如：
EXPLAIN (COSTS)
SELECT *
FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|SETTINGS
SETTINGS 選項用于顯示配置參數(shù)，尤其是影響查詢計劃的非默認(rèn)設(shè)置的參數(shù)。該選項默認(rèn)禁用。例如：
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 選項，可以為預(yù)編譯語句 生成通用執(zhí)行計劃，這種執(zhí)行計劃不依賴于綁定變量（例如 $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 選項默認(rèn)禁用，而且不能和 ANALYZE 選項一起使用，因為 ANALYZE 需要執(zhí)行語句。
另外，預(yù)編譯語句也可能使用定制執(zhí)行計劃，也就是使用綁定變量的具體值創(chuàng)建執(zhí)行計劃。例如：
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;索引掃描的訪問謂詞中使用了具體的參數(shù)值（10）。
提示：運行時參數(shù) plan_cache_mode 決定了預(yù)編譯語句使用通用執(zhí)行計劃還是定制執(zhí)行計劃。
BUFFERS
BUFFERS 選項用于顯示緩沖區(qū)使用情況，默認(rèn)禁用。例如：
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 選項顯示的信息包括共享內(nèi)存塊命中（hit）、讀?。╮ead）、標(biāo)記臟塊（dirtied）以及寫入（written）數(shù)量，本地內(nèi)存塊命中（hit）、讀?。╮ead）、標(biāo)記臟塊（dirtied）以及寫入（written）數(shù)量，臨時內(nèi)存塊的讀取（read）和寫入（written）數(shù)量。如果啟用了服務(wù)器參數(shù) track_io_timing ，還會顯示讀寫數(shù)據(jù)文件塊和臨時文件塊的時間（毫秒）。
其中，一次命中意味著避免了一次磁盤讀取，因為所需數(shù)據(jù)塊已經(jīng)存在緩存中。共享內(nèi)存塊包含了普通表和索引的緩存數(shù)據(jù)，本地內(nèi)存塊包含了臨時表和索引的緩存數(shù)據(jù)；臨時內(nèi)存塊包含了排序、哈希、物化節(jié)點等操作使用的臨時數(shù)據(jù)。
臟塊的數(shù)量表示之前未改動，但是當(dāng)前查詢修改的數(shù)據(jù)塊；寫入塊的數(shù)量表示之前被標(biāo)記為臟塊，同時在當(dāng)前查詢處理過程總被后臺進程刷新到磁盤的數(shù)據(jù)塊。上層節(jié)點顯示的數(shù)量包含了子節(jié)點的數(shù)量，對于 TEXT 輸出格式，只顯示非零數(shù)據(jù)值。
WAL
WAL 選項用于顯示有關(guān)預(yù)寫式日志記錄生成的信息。具體來說，包括記錄數(shù)、全頁鏡像數(shù)（fpi）以及生成的 WAL（字節(jié)）。如果 FORMAT 選項的值為 TEXT（默認(rèn)值），只顯示非零信息。該選項只能在啟用 ANALYZE 選項時使用，默認(rèn)為禁用。
TIMING
TIMING 選項用于顯示每個計劃節(jié)點的啟用時間和完成時間（毫秒），該選項只能在啟用 ANALYZE 選項時使用，默認(rèn)為啟用。
某些平臺上重復(fù)讀取系統(tǒng)時間可能會明顯影響查詢性能，如果只關(guān)注實際返回的行數(shù)，可以在啟用 ANALYZE 選項時將該選項禁用。即使關(guān)閉了節(jié)點的計時功能，整個語句的運行時間仍然會統(tǒng)計并顯示。
SUMMARY
SUMMARY 選項用于在執(zhí)行計劃之后顯示匯總信息（例如總的時間消耗）。如果啟用了 ANALYZE 選項，默認(rèn)顯示匯總信息；否則默認(rèn)不會顯示匯總信息。
對于 EXPLAIN EXECUTE 語句，Planning time 包含了從緩存中獲取執(zhí)行計劃或者重新計劃消耗的時間。
FORMAT
FORMAT 選項用于指定執(zhí)行計劃的輸出格式，可以使用 TEXT、XML、JSON 或者 YAML 格式。默認(rèn)輸出格式為 TEXT，其他格式輸出的內(nèi)容和 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
}
}
]
