PostgreSQL 對每一個(gè)收到的 SQL 查詢都會(huì)生成一個(gè)查詢計(jì)劃。選擇合適的計(jì)劃，是決定查詢性能的關(guān)鍵因素之一。PostgreSQL 內(nèi)置了一個(gè)復(fù)雜的查詢優(yōu)化器（planner），負(fù)責(zé)在眾多可行計(jì)劃中挑選“代價(jià)最低”的一個(gè)。

要查看優(yōu)化器為某個(gè)查詢生成的計(jì)劃，可以使用 EXPLAIN 命令。讀懂查詢計(jì)劃是一項(xiàng)需要經(jīng)驗(yàn)的技能，本文將帶你了解 EXPLAIN 的基礎(chǔ)用法和常見計(jì)劃節(jié)點(diǎn)的含義。

本文示例基于 PostgreSQL 9.3 開發(fā)版的回歸測試數(shù)據(jù)庫，且已事先執(zhí)行過 VACUUM ANALYZE。你在自己環(huán)境中運(yùn)行時(shí)，代價(jià)和行數(shù)估計(jì)可能會(huì)略有不同，這是正?，F(xiàn)象。

一、EXPLAIN 基礎(chǔ)

查詢計(jì)劃本質(zhì)上是一棵“計(jì)劃節(jié)點(diǎn)樹”。

• 最底層通常是掃描節(jié)點(diǎn)：負(fù)責(zé)從表中讀取原始數(shù)據(jù)，如順序掃描、索引掃描、位圖索引掃描等。
• 上層節(jié)點(diǎn)則負(fù)責(zé)對數(shù)據(jù)進(jìn)行進(jìn)一步處理：連接（join）、排序（sort）、聚集（aggregate）、過濾（filter）等。

EXPLAIN 會(huì)為每個(gè)節(jié)點(diǎn)輸出一行，顯示節(jié)點(diǎn)類型和優(yōu)化器對該節(jié)點(diǎn)執(zhí)行代價(jià)的估計(jì)。最上面一行是整個(gè)計(jì)劃的總代價(jià)估計(jì)，優(yōu)化器的目標(biāo)就是最小化這個(gè)值。

1. 最簡單的例子

EXPLAIN SELECT * FROM tenk1;

典型輸出：

Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

這表示優(yōu)化器選擇了**順序掃描（Seq Scan）**整個(gè)表。括號中的四個(gè)數(shù)字含義是：

1. 啟動(dòng)代價(jià)（start-up cost） ：在開始輸出第一行之前需要花費(fèi)的代價(jià)，比如排序節(jié)點(diǎn)需要先完成排序。
2. 總代價(jià)（total cost） ：假設(shè)該節(jié)點(diǎn)執(zhí)行到完成（讀取所有可用行）的總代價(jià)。
3. 輸出行數(shù)（rows） ：預(yù)計(jì)該節(jié)點(diǎn)會(huì)輸出的行數(shù)。
4. 行寬度（width） ：預(yù)計(jì)每行的平均寬度（字節(jié)）。

代價(jià)單位是 PostgreSQL 內(nèi)部的“虛構(gòu)單位”，默認(rèn)以“磁盤頁面讀取”為基準(zhǔn)：seq_page_cost = 1.0，其他代價(jià)參數(shù)都相對它來設(shè)定。

2. 代價(jià)是如何計(jì)算的？

以 tenk1 為例，假設(shè)：

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

得到：

• relpages = 358（表占用 358 個(gè)磁盤頁面）
• reltuples = 10000（表中有 10000 行）

順序掃描的代價(jià)計(jì)算公式大致為：

總代價(jià) ≈ 頁面讀取代價(jià) + 處理每行的 CPU 代價(jià)

= relpages * seq_page_cost + reltuples * cpu_tuple_cost

使用默認(rèn)參數(shù)（seq_page_cost = 1.0, cpu_tuple_cost = 0.01）：

358 * 1.0 + 10000 * 0.01 = 358 + 100 = 458

這與 EXPLAIN 輸出中的 cost=0.00..458.00 相吻合。

二、WHERE 條件與過濾

當(dāng)查詢中加入 WHERE 條件時(shí)，計(jì)劃會(huì)發(fā)生變化。

1. 簡單過濾：仍然順序掃描

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

輸出：

Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 width=244)
  Filter: (unique1 < 7000)

• Filter: (unique1 < 7000) 表示：順序掃描每一行，然后用這個(gè)條件過濾。
• 預(yù)計(jì)輸出行數(shù)從 10000 降到 7001。
• 總代價(jià)略有上升（從 458 到 483），因?yàn)樾枰~外的 CPU 時(shí)間來檢查過濾條件。

2. 更嚴(yán)格的條件：使用索引

當(dāng)條件更嚴(yán)格時(shí)，優(yōu)化器可能會(huì)選擇索引掃描：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

輸出：

Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
  Recheck Cond: (unique1 < 100)
  ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
        Index Cond: (unique1 < 100)

這里使用了位圖索引掃描 + 位圖堆掃描的兩步計(jì)劃：

1. 子節(jié)點(diǎn) Bitmap Index Scan：在索引 tenk1_unique1 上找到滿足 unique1 < 100 的行的位置，生成一個(gè)位圖。
2. 父節(jié)點(diǎn) Bitmap Heap Scan：根據(jù)位圖去表中抓取對應(yīng)的行，并再次檢查條件（Recheck Cond）。

雖然隨機(jī)訪問表行比順序掃描更昂貴，但因?yàn)橹辉L問少量頁面，總體代價(jià)仍然低于全表掃描。

3. 多條件：索引條件 vs 過濾條件

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

輸出：

Bitmap Heap Scan on tenk1  (cost=5.04..229.43 rows=1 width=244)
  Recheck Cond: (unique1 < 100)
  Filter: (stringu1 = 'xxx'::name)
  ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
        Index Cond: (unique1 < 100)

• unique1 < 100 是索引條件（Index Cond） ，可以通過索引快速過濾。
• stringu1 = 'xxx' 不是索引的一部分，只能作為過濾條件（Filter） ，在從表中取出行后再檢查。
• 預(yù)計(jì)輸出行數(shù)進(jìn)一步減少到 1，但代價(jià)變化不大，因?yàn)槿匀恍枰L問相同的一組行，只是多了一次過濾。

三、索引掃描 vs 位圖掃描

PostgreSQL 有多種索引訪問方式，常見的有：

1. 索引掃描（Index Scan）

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

輸出：

Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
  Index Cond: (unique1 = 42)

• 直接通過唯一索引定位到單行，適合返回少量行的查詢。
• 如果查詢有 ORDER BY unique1，且索引順序與排序順序一致，優(yōu)化器可能會(huì)選擇索引掃描來避免額外排序。

2. 位圖掃描（Bitmap Scan）

位圖掃描適合需要返回中等數(shù)量行的場景：

• 先通過索引構(gòu)建位圖（滿足條件的行號集合）。
• 再按物理位置排序后批量訪問表數(shù)據(jù)，減少隨機(jī) I/O。

一般來說：

• 返回行數(shù)很少 → 傾向 Index Scan
• 返回行數(shù)中等 → 傾向 Bitmap Heap Scan + Bitmap Index Scan
• 返回行數(shù)很多 → 傾向 Seq Scan（全表掃描）

四、排序與 LIMIT

1. 顯式排序

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

輸出：

Sort  (cost=1109.39..1134.39 rows=10000 width=244)
  Sort Key: unique1
  ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

• 優(yōu)化器選擇先全表掃描，再進(jìn)行排序。
• Sort 節(jié)點(diǎn)的代價(jià)主要來自內(nèi)存排序的 CPU 開銷。

2. 增量排序（Incremental Sort）

如果排序鍵有前綴已經(jīng)有序，PostgreSQL 可能使用增量排序：

EXPLAIN SELECT * FROM tenk1 ORDER BY four, ten LIMIT 100;

輸出：

Limit  (cost=521.06..538.05 rows=100 width=244)
  ->  Incremental Sort  (cost=521.06..2220.95 rows=10000 width=244)
        Sort Key: four, ten
        Presorted Key: four
        ->  Index Scan using index_tenk1_on_four on tenk1  (cost=0.29..1510.08 rows=10000 width=244)

• Presorted Key: four 表示 four 列已經(jīng)通過索引有序。
• 增量排序只需對 ten 列進(jìn)行“分段排序”，適合有 LIMIT 的場景，可以提前返回部分結(jié)果。

3. LIMIT 的影響

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

輸出：

Limit  (cost=0.29..14.48 rows=2 width=244)
  ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..71.27 rows=10 width=244)
        Index Cond: (unique2 > 9000)
        Filter: (unique1 < 100)

• 沒有 LIMIT 時(shí)，優(yōu)化器可能選擇位圖掃描（Bitmap Scan）。
• 有了 LIMIT 2，優(yōu)化器改為使用 Index Scan，因?yàn)橐坏┱业?2 行就可以停止，避免位圖掃描的啟動(dòng)代價(jià)。

注意：

• 索引掃描節(jié)點(diǎn)的 cost 和 rows 仍然是按“執(zhí)行到完成”來估計(jì)的。
• 實(shí)際執(zhí)行時(shí)，Limit 節(jié)點(diǎn)會(huì)提前終止，因此真實(shí)執(zhí)行時(shí)間會(huì)遠(yuǎn)低于估計(jì)的總代價(jià)。

五、連接（Join）計(jì)劃

連接是復(fù)雜查詢中最關(guān)鍵的部分之一，PostgreSQL 支持多種連接算法：嵌套循環(huán)（Nested Loop）、哈希連接（Hash Join）、歸并連接（Merge Join）。

1. 嵌套循環(huán)連接（Nested Loop）

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

輸出：

Nested Loop  (cost=4.65..118.62 rows=10 width=488)
  ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244)
        Recheck Cond: (unique1 < 10)
        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
              Index Cond: (unique1 < 10)
  ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244)
        Index Cond: (unique2 = t1.unique2)

結(jié)構(gòu)說明：

• 外層（outer）：tenk1 t1 的位圖掃描，返回約 10 行。
• 內(nèi)層（inner）：對 tenk2 t2 的索引掃描，每次使用外層行的 t1.unique2 作為條件。
• 嵌套循環(huán)的總代價(jià) ≈ 外層代價(jià) + 外層行數(shù) × 內(nèi)層單次代價(jià) + 連接處理的 CPU 代價(jià)。

適合場景：

• 外層結(jié)果集很小。
• 內(nèi)層可以通過索引快速查找匹配行。

2. 哈希連接（Hash Join）

當(dāng)內(nèi)層表較大時(shí)，哈希連接往往更高效：

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

輸出：

Hash Join  (cost=230.47..713.98 rows=101 width=488)
  Hash Cond: (t2.unique2 = t1.unique2)
  ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
  ->  Hash  (cost=229.20..229.20 rows=101 width=244)
        ->  Bitmap Heap Scan on tenk1 t1  (cost=5.07..229.20 rows=101 width=244)
              Recheck Cond: (unique1 < 100)
              ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
                    Index Cond: (unique1 < 100)

執(zhí)行步驟：

1. 構(gòu)建階段：掃描 tenk1 t1，構(gòu)建以 unique2 為鍵的哈希表。
2. 探查階段：掃描 tenk2 t2，對每一行在哈希表中查找匹配的 unique2。

適合場景：

• 內(nèi)層表可以放入內(nèi)存。
• 連接條件是等值連接（=）。

3. 歸并連接（Merge Join）

歸并連接要求兩個(gè)輸入都按連接鍵排序：

EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

輸出：

Merge Join  (cost=198.11..268.19 rows=10 width=488)
  Merge Cond: (t1.unique2 = t2.unique2)
  ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..656.28 rows=101 width=244)
        Filter: (unique1 < 100)
  ->  Sort  (cost=197.83..200.33 rows=1000 width=244)
        Sort Key: t2.unique2
        ->  Seq Scan on onek t2  (cost=0.00..148.00 rows=1000 width=244)

• tenk1 t1 通過索引掃描自然按 unique2 排序。
• onek t2 先順序掃描，再排序。
• 兩個(gè)已排序的結(jié)果按連接鍵“合并”，類似歸并排序的合并階段。

適合場景：

• 連接鍵上有索引，或結(jié)果已經(jīng)有序。
• 連接條件是等值連接。

六、EXPLAIN ANALYZE：查看真實(shí)執(zhí)行情況

EXPLAIN ANALYZE 會(huì)實(shí)際執(zhí)行查詢，并在計(jì)劃中加入真實(shí)執(zhí)行時(shí)間和行數(shù)：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

典型輸出片段：

Nested Loop  (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
  ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
        Recheck Cond: (unique1 < 10)
        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
              Index Cond: (unique1 < 10)
  ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
        Index Cond: (unique2 = t1.unique2)
Planning time: 0.181 ms
Execution time: 0.501 ms

新增字段說明：

• actual time：實(shí)際執(zhí)行時(shí)間（毫秒），分為啟動(dòng)時(shí)間和總時(shí)間。
• rows：實(shí)際輸出行數(shù)。
• loops：該節(jié)點(diǎn)被執(zhí)行的次數(shù)。

對于被多次執(zhí)行的節(jié)點(diǎn)（如嵌套循環(huán)的內(nèi)層）：

• actual time 和 rows 是每次執(zhí)行的平均值。
• 總時(shí)間 ≈ actual time × loops。

1. 額外統(tǒng)計(jì)信息

某些節(jié)點(diǎn)會(huì)輸出額外信息，例如排序和哈希：

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

排序節(jié)點(diǎn)輸出：

Sort  (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
  Sort Key: t1.fivethous
  Sort Method: quicksort  Memory: 77kB

哈希節(jié)點(diǎn)輸出：

Hash  (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 28kB

這些信息有助于判斷：

• 排序是否在內(nèi)存中完成（是否溢出到磁盤）。
• 哈希表是否過大，是否需要分批次（Batches > 1）。

2. 過濾與索引重檢查

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

輸出：

Seq Scan on tenk1  (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
  Filter: (ten < 7)
  Rows Removed by Filter: 3000

• Rows Removed by Filter：被過濾條件丟棄的行數(shù)。

對于 GiST 等“有損”索引：

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

可能輸出：

Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
  Index Cond: (f1 @> '((0.5,2))'::polygon)
  Rows Removed by Index Recheck: 1

• 索引先返回候選行。
• 再通過“索引重檢查”（Index Recheck）進(jìn)行精確判斷，不滿足的會(huì)被丟棄。

3. BUFFERS 選項(xiàng)

EXPLAIN (ANALYZE, BUFFERS) 可以查看 I/O 相關(guān)統(tǒng)計(jì)：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

輸出中會(huì)包含：

Buffers: shared hit=15

• shared hit：從共享緩沖區(qū)（內(nèi)存）中命中的頁面數(shù)。
• shared read：從磁盤讀取的頁面數(shù)（未命中緩存）。

這有助于判斷查詢的性能瓶頸是否在磁盤 I/O。

七、數(shù)據(jù)修改語句的 EXPLAIN ANALYZE

EXPLAIN ANALYZE 也適用于 INSERT / UPDATE / DELETE / MERGE：

BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
ROLLBACK;

輸出結(jié)構(gòu)：

Update on tenk1  (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)
  ->  Bitmap Heap Scan on tenk1  (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)
        Recheck Cond: (unique1 < 100)
        Heap Blocks: exact=90
        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)
              Index Cond: (unique1 < 100)

• 頂層是 Update 節(jié)點(diǎn)，負(fù)責(zé)實(shí)際寫入數(shù)據(jù)。
• 子節(jié)點(diǎn)負(fù)責(zé)查找需要更新的行。
• rows=0 表示更新節(jié)點(diǎn)本身不輸出任何行（只修改數(shù)據(jù)）。

注意：

• EXPLAIN ANALYZE 會(huì)真正執(zhí)行數(shù)據(jù)修改，因此通常需要在事務(wù)中運(yùn)行并回滾。
• 觸發(fā)器（尤其是 AFTER 觸發(fā)器）的執(zhí)行時(shí)間可能不會(huì)完全體現(xiàn)在計(jì)劃節(jié)點(diǎn)時(shí)間中。

八、使用注意事項(xiàng)與常見誤區(qū)

• EXPLAIN ANALYZE 會(huì)真正執(zhí)行查詢
  • 對于 SELECT，結(jié)果被丟棄，但仍會(huì)產(chǎn)生副作用（如調(diào)用函數(shù)的副作用）。
  • 對于 INSERT / UPDATE / DELETE，會(huì)修改數(shù)據(jù)，需要謹(jǐn)慎使用（通常配合事務(wù) + ROLLBACK）。
• 計(jì)時(shí)開銷可能影響結(jié)果
  • EXPLAIN ANALYZE 會(huì)增加額外的計(jì)時(shí)開銷。
  • 在某些系統(tǒng)上，gettimeofday() 很慢，會(huì)導(dǎo)致執(zhí)行時(shí)間看起來比實(shí)際更長。
  • 可以用 pg_test_timing 工具評估系統(tǒng)計(jì)時(shí)開銷。
• 估計(jì)值 vs 實(shí)際值
  • 優(yōu)化器的代價(jià)和行數(shù)估計(jì)基于統(tǒng)計(jì)信息，是近似值。
  • 當(dāng)計(jì)劃被 LIMIT 或連接算法提前終止時(shí)，實(shí)際行數(shù)和時(shí)間會(huì)遠(yuǎn)小于估計(jì)值，這不是“錯(cuò)誤”，而是顯示方式的問題。
• 小表上的計(jì)劃不具有代表性
  • 小表通常會(huì)選擇順序掃描，即使有索引。
  • 因?yàn)樽x取一個(gè)頁面的索引 + 一個(gè)頁面的表，可能比直接順序掃描一個(gè)頁面更貴。
• 分區(qū)表與 Subplans Removed
  • 掃描分區(qū)表時(shí)，某些分區(qū)可能在運(yùn)行時(shí)被判定為不可能包含數(shù)據(jù)。
  • 這些子計(jì)劃會(huì)被省略，并顯示 Subplans Removed: N。

九、總結(jié)

讀懂 EXPLAIN 是優(yōu)化 PostgreSQL 查詢性能的基礎(chǔ)能力。本文介紹了：

• EXPLAIN 的基本輸出格式和代價(jià)含義。
• 常見掃描方式：順序掃描、索引掃描、位圖掃描。
• 排序、過濾、LIMIT 對計(jì)劃的影響。
• 三種主要連接算法：嵌套循環(huán)、哈希連接、歸并連接。
• EXPLAIN ANALYZE 的使用方法和真實(shí)執(zhí)行信息的解讀。
• 使用 EXPLAIN 時(shí)的注意事項(xiàng)和常見誤區(qū)。

到此這篇關(guān)于PostgreSQL EXPLAIN 的深入解析與應(yīng)用的文章就介紹到這了,更多相關(guān)PostgreSQL EXPLAIN使用內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論