PHP小码农

Appearance

EXPLAIN 执行计划 ​

EXPLAIN 是 PostgreSQL 中最重要也最常用的性能分析工具。它把一条 SQL 交给查询优化器，然后输出优化器选定的「执行计划（query plan）」：用哪些扫描方式读表、如何连接、用到哪些索引、每一步的估算代价与行数。学会读执行计划，是从「会写 SQL」到「能调优 SQL」的分水岭。

前置知识

阅读本节前，建议先了解：psql 元命令（\d / \dt / \du）

基础概念 ​

概念术语说明
执行计划Query Plan优化器为 SQL 选定的执行步骤树
计划树Plan Tree由节点组成的树，子节点先执行，结果向上传递
代价Cost估算的抽象开销，格式为 启动代价..总代价
行数估算Rows优化器预估该节点输出的行数
宽度估算Width预估每行平均字节数
实际执行ANALYZE真正执行语句并输出实际耗时与行数
缓冲区统计BUFFERS输出共享块命中/读盘次数
顺序扫描Seq Scan全表顺序读取
索引扫描Index Scan通过索引定位再回表
仅索引扫描Index Only Scan所需列都在索引中，无需回表
位图扫描Bitmap Scan先用索引构建位图再批量回表
嵌套循环Nested Loop外层每行驱动内层查找
哈希连接Hash Join构建哈希表后探测匹配
归并连接Merge Join两侧排序后归并匹配
估算偏差Estimation Error估算行数与实际行数差距过大

读计划的核心原则：

  • 自下而上：最内层（缩进最深）的节点最先执行。
  • 同层从左到右：同一缩进层按自上而下、从左到右的顺序执行。
  • (cost=start..total rows=N width=W)：start 是返回第一行前的代价，total 是返回全部行的代价，比较两个节点时主要看 total。
sql
-- 不看计划，先建立直观印象
EXPLAIN SELECT * FROM products WHERE sku = 'SKU-1001';
text
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using products_sku_key on products  (cost=0.29..8.30 rows=1 width=68)
   Index Cond: (sku = 'SKU-1001'::text)
(2 rows)

语法与用法 ​

sql
EXPLAIN [ ( option [, ...] ) ] statement

-- 常用选项
ANALYZE [ boolean ]        -- 真正执行并输出实际数据
VERBOSE [ boolean ]        -- 输出更多细节（列、schema 前缀）
COSTS [ boolean ]          -- 是否显示代价估算，默认 on
BUFFERS [ boolean ]        -- 显示缓冲区使用，需配合 ANALYZE
TIMING [ boolean ]         -- 显示每节点实际耗时，默认 on
SUMMARY [ boolean ]        -- 额外输出规划与执行耗时汇总
FORMAT { TEXT | JSON | YAML | XML }   -- 输出格式
WAL [ boolean ]            -- 显示 WAL 记录数（PostgreSQL 13+）
SETTINGS [ boolean ]       -- 显示影响计划的参数（PostgreSQL 12+）
sql
-- 最实用的组合：实际执行 + 缓冲区 + 关闭代价噪声
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM products WHERE stock > 100;

ANALYZE 会真正执行语句

EXPLAIN ANALYZE 会真正执行 SQL。对 INSERT / UPDATE / DELETE 使用它时，数据会被真实修改，务必用事务包裹并回滚。

详细说明 ​

基础用法与计划树结构 ​

sql
EXPLAIN
SELECT p.sku, p.name, o.order_no
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE p.price > 100
ORDER BY o.order_no;
text
                                  QUERY PLAN
------------------------------------------------------------------------------
 Sort  (cost=25.32..25.34 rows=8 width=72)
   Sort Key: o.order_no
   ->  Hash Join  (cost=16.90..25.20 rows=8 width=72)
         Hash Cond: (o.product_id = p.id)
         ->  Seq Scan on orders o  (cost=0.00..7.30 rows=230 width=36)
         ->  Hash  (cost=15.60..15.60 rows=104 width=44)
               ->  Seq Scan on products p  (cost=0.00..15.60 rows=104 width=44)
                     Filter: (price > 100)
(7 rows)

解读顺序（自下而上）：

  1. 两个 Seq Scan 分别读取 orders 与 products，并对 products 施加 Filter。
  2. Hash 节点把 products 的结果构建为哈希表。
  3. Hash Join 用 orders 逐行探测哈希表。
  4. Sort 对结果排序。

ANALYZE：实际执行数据 ​

ANALYZE 会输出 actual time、rows、loops 三个实际指标。

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products WHERE stock > 100;
text
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on products  (actual time=0.015..0.021 rows=3 loops=1)
   Filter: (stock > 100)
   Rows Removed by Filter: 4
 Planning Time: 0.120 ms
 Execution Time: 0.048 ms
(5 rows)

关键字段含义：

字段含义
actual time=第一行..全部行返回第一行与全部行的实际毫秒数（单位：毫秒）
rows=N该节点实际输出的平均行数（= 总行数 / loops）
loops=N该节点被执行的次数
Rows Removed by Filter被过滤条件排除的行数（估算偏差的重要线索）
Planning Time生成计划耗时
Execution Time执行总耗时（含所有子节点）

注意：上层节点的 actual time 已包含下层节点耗时，做性能归因时应看「本节点耗时 - 子节点耗时」。

BUFFERS：缓冲区与 I/O ​

BUFFERS 输出各节点的块访问情况，是判断「是否读盘」的关键。

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders;
text
                       QUERY PLAN
---------------------------------------------------------
 Aggregate  (actual time=1.120..1.121 rows=1 loops=1)
   Buffers: shared hit=12
   ->  Seq Scan on orders  (actual time=0.010..0.800 rows=5000 loops=1)
         Buffers: shared hit=12
(3 rows)
指标含义
shared hit在共享缓冲区命中，未读盘（快）
shared read从磁盘读取（慢，是 I/O 瓶颈来源）
shared dirtied被修改过的块数
shared written写出的块数
temp read/written临时文件读写（排序/哈希溢出到磁盘的信号）

若 read 很大而 hit 很小，说明缓存未命中，首次执行偏慢属正常；若反复执行仍大量 read，则需考虑索引或增大 shared_buffers。

扫描节点：Seq / Index / Index Only / Bitmap ​

节点适用场景特点
Seq Scan返回行占比大、表很小、无可用索引顺序读全表，大表上代价高
Index Scan返回行很少、需要回表取列索引定位 + 随机回表
Index Only Scan所需列都在索引里无需回表，但依赖可见性映射
Bitmap Index Scan返回行中等、多个索引条件组合先建位图，再按页顺序回表
Bitmap Heap Scan配合位图索引扫描批量按页访问，减少随机 I/O
Tid Scan直接按物理行号访问少见，用于 ctid 条件
sql
-- Seq Scan：无索引时
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products WHERE name LIKE '%线缆%';

-- Index Only Scan：所需列全在索引中
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku FROM products WHERE sku = 'SKU-1001';

Index Only Scan 依赖「可见性映射（visibility map）」。若表刚更新过、VACUUM 未跟上，会退化为频繁回表，输出中会出现 Heap Fetches: N。

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku, name FROM products WHERE sku LIKE 'SKU-1%';
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using products_sku_key on products
     (actual time=0.020..0.035 rows=3 loops=1)
   Index Cond: (sku >= 'SKU-1'::text AND sku < 'SKU-2'::text)
   Heap Fetches: 0
(3 rows)

连接节点：Nested Loop / Hash Join / Merge Join ​

节点算法最佳场景
Nested Loop外层每行驱动内层查找外层结果很小、内层有索引
Hash Join构建哈希表后探测两表都较大、等值连接
Merge Join两侧有序后归并两侧已有序或需排序输出
sql
-- Nested Loop：外层小、内层走索引
EXPLAIN (ANALYZE, COSTS OFF)
SELECT p.sku, o.order_no
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE p.sku = 'SKU-1001';

-- Hash Join：大表等值连接
EXPLAIN (ANALYZE, COSTS OFF)
SELECT p.sku, count(*)
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.sku;

Hash Join 输出中要重点关注：

  • Hash Cond：连接条件。
  • Hash 子节点：构建侧，通常选较小的表。
  • Batches / Memory Usage：若 Batches > 1，说明哈希表溢出到磁盘，应调大 work_mem。
text
 Hash Join  (actual time=1.2..3.4 rows=5000 loops=1)
   Hash Cond: (o.product_id = p.id)
   ->  Seq Scan on orders o  (...)
   ->  Hash  (actual time=0.5..0.9 rows=120 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 24kB

排序与聚合节点 ​

需要排序的节点常见的有 Sort、GroupAggregate、Merge Join、Incremental Sort（PostgreSQL 13+）。Sort 节点会显示排序方式与内存使用。

text
 Sort  (actual time=5.1..6.2 rows=5000 loops=1)
   Sort Key: created_at DESC
   Sort Method: quicksort  Memory: 512kB
Sort Method含义
quicksort全部在内存中完成
top-N heapsort配合 LIMIT 只维护前 N 个
external merge溢出到磁盘，性能差，需增大 work_mem

看到 Disk: N kB 说明发生了外排，这是排序性能问题最直接的信号。

并行查询节点 ​

PostgreSQL 9.6+ 支持并行查询，计划中会出现 Gather、Gather Merge、Parallel Seq Scan。

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders;
text
                              QUERY PLAN
----------------------------------------------------------------------
 Finalize Aggregate  (actual time=15.2..15.2 rows=1 loops=1)
   ->  Gather  (actual time=15.0..15.1 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (actual time=14.8..14.8 rows=1 loops=1)
               ->  Parallel Seq Scan on orders o  (...)
字段含义
Workers Planned计划启用的并行 worker 数
Workers Launched实际启动的 worker 数（为 0 说明未拿到 worker）
Gather汇总各 worker 结果
Gather Merge归并有序结果，保持排序

若 Workers Launched 少于 Workers Planned，通常是 max_parallel_workers 或并发负载导致的资源不足。

估算偏差：读计划的重点 ​

执行计划分析的真正目的，是找出「估算与实际差距大的节点」。差距大意味着优化器选错了方案。

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE amount > 1000;
text
                       QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders  (actual time=0.02..8.50 rows=2 loops=1)
   Filter: (amount > 1000)
   Rows Removed by Filter: 4998
 Planning Time: 0.100 ms
 Execution Time: 8.600 ms
(5 rows)

上例中，若估算行数与实际相差数量级，或 Rows Removed by Filter 极大，就说明统计信息不准或条件不可索引。

常见的偏差原因：

  • 统计信息过期 → 手动 ANALYZE。
  • 条件函数包裹索引列 → 索引不可用。
  • 列值相关性未被统计 → 用 CREATE STATISTICS 扩展统计。
  • 预估代价模型与实际 I/O 特性不符。

格式化输出与 SETTINGS ​

FORMAT JSON 便于程序解析；SETTINGS 会列出影响本次计划的参数。

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM products WHERE price > 100;

EXPLAIN (ANALYZE, SETTINGS, COSTS OFF)
SELECT * FROM products WHERE price > 100;
text
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on products  (actual time=0.01..0.02 rows=5 loops=1)
   Filter: (price > 100)
 Settings: work_mem = '4MB', search_path = 'public'

实战示例 ​

步骤 1：建表并造数据 ​

sql
CREATE DATABASE demo;

\c demo

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      text    NOT NULL UNIQUE,
  name     text    NOT NULL,
  category text,
  price    numeric(10, 2),
  stock    integer NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text    NOT NULL,
  product_id integer REFERENCES products (id),
  qty        integer NOT NULL,
  amount     numeric(10, 2),
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_product_id ON orders (product_id);
CREATE INDEX idx_products_price ON products (price);

-- 造 5000 行订单，其中 amount > 1000 的仅有几行
INSERT INTO products (sku, name, category, price, stock)
SELECT
  'SKU-' || lpad(g::text, 4, '0'),
  '商品-' || g,
  (ARRAY['键盘', '鼠标', '显示器', '线缆', '配件'])[1 + (g % 5)],
  (g % 50) * 29.90,
  (g % 120)
FROM generate_series(1, 120) AS g;

INSERT INTO orders (order_no, product_id, qty, amount, created_at)
SELECT
  'SO-' || lpad(g::text, 5, '0'),
  1 + (g % 120),
  1 + (g % 4),
  round((random() * 200)::numeric, 2),
  now() - (g || ' minutes')::interval
FROM generate_series(1, 5000) AS g;

ANALYZE products;
ANALYZE orders;

步骤 2：对比索引扫描与顺序扫描 ​

sql
-- 走索引：只取一行
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM products WHERE sku = 'SKU-0001';
text
                        QUERY PLAN
-----------------------------------------------------------
 Index Scan using products_sku_key on products
     (actual time=0.020..0.022 rows=1 loops=1)
   Index Cond: (sku = 'SKU-0001'::text)
   Buffers: shared hit=4
 Planning Time: 0.140 ms
 Execution Time: 0.045 ms
(5 rows)
sql
-- 走顺序扫描：函数包裹导致索引失效
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM products WHERE lower(sku) = 'sku-0001';
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on products  (actual time=0.015..0.300 rows=1 loops=1)
   Filter: (lower(sku) = 'sku-0001'::text)
   Rows Removed by Filter: 119
   Buffers: shared hit=7
 Planning Time: 0.150 ms
 Execution Time: 0.330 ms
(6 rows)

步骤 3：观察连接方式的差异 ​

sql
-- 小结果集 + 索引 → Nested Loop
EXPLAIN (ANALYZE, COSTS OFF)
SELECT p.sku, o.order_no
FROM products p
JOIN orders o ON o.product_id = p.id
WHERE p.sku = 'SKU-0001';
text
                              QUERY PLAN
----------------------------------------------------------------------
 Nested Loop  (actual time=0.060..0.500 rows=42 loops=1)
   ->  Index Scan using products_sku_key on products p  (...)
         Index Cond: (sku = 'SKU-0001'::text)
   ->  Index Scan using idx_orders_product_id on orders o  (...)
         Index Cond: (product_id = p.id)
(6 rows)
sql
-- 全量聚合连接 → Hash Join
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT p.category, count(*)
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.category;
text
                              QUERY PLAN
----------------------------------------------------------------------
 HashAggregate  (actual time=22.5..22.6 rows=5 loops=1)
   Group Key: p.category
   ->  Hash Join  (actual time=1.2..18.0 rows=5000 loops=1)
         Hash Cond: (o.product_id = p.id)
         ->  Seq Scan on orders o  (...)
         ->  Hash  (cost=...) (actual time=0.3..0.5 rows=120 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 16kB
               ->  Seq Scan on products p  (...)
(9 rows)

步骤 4：识别估算偏差 ​

sql
-- 估算与实际的对比
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders WHERE amount > 1000;
text
                       QUERY PLAN
---------------------------------------------------------------
 Seq Scan on orders  (actual time=0.020..6.500 rows=9 loops=1)
   Filter: (amount > 1000)
   Rows Removed by Filter: 4991
 Planning Time: 0.110 ms
 Execution Time: 6.800 ms
(5 rows)
sql
-- 改善：建立索引后计划变化
CREATE INDEX idx_orders_amount ON orders (amount);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE amount > 1000;
text
                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on orders  (actual time=0.030..0.050 rows=9 loops=1)
   Recheck Cond: (amount > 1000.00)
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on idx_orders_amount  (...)
         Index Cond: (amount > 1000.00)
   Buffers: shared hit=8
(7 rows)

步骤 5：排序溢出到磁盘 ​

sql
-- 观察外排：先临时调小 work_mem
SET work_mem = '64kB';

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM orders ORDER BY amount DESC LIMIT 10;
text
                          QUERY PLAN
--------------------------------------------------------------
 Limit  (actual time=12.0..12.0 rows=10 loops=1)
   ->  Sort  (actual time=12.0..12.0 rows=10 loops=1)
         Sort Key: amount DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on orders o  (...)
(6 rows)
sql
-- 全量排序在大数据量下会外排
SET work_mem = '64kB';

EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku, amount FROM orders o JOIN products p ON p.id = o.product_id
ORDER BY o.amount, p.sku;
text
                          QUERY PLAN
--------------------------------------------------------------
 Sort  (actual time=45.0..48.0 rows=5000 loops=1)
   Sort Key: o.amount, p.sku
   Sort Method: external merge  Disk: 480kB
   ->  Hash Join  (...)
(5 rows)

看到 external merge 与 Disk: 480kB 即说明溢出，应提高 work_mem。

sql
SET work_mem = '16MB';

EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku, amount FROM orders o JOIN products p ON p.id = o.product_id
ORDER BY o.amount, p.sku;
text
                          QUERY PLAN
--------------------------------------------------------------
 Sort  (actual time=18.0..19.5 rows=5000 loops=1)
   Sort Key: o.amount, p.sku
   Sort Method: quicksort  Memory: 1024kB
   ->  Hash Join  (...)
(5 rows)

步骤 6：安全地对 DML 使用 EXPLAIN ANALYZE ​

sql
-- DML 一定要包在事务里并回滚
BEGIN;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
UPDATE products SET stock = stock + 1 WHERE category = '配件';

ROLLBACK;
text
                              QUERY PLAN
----------------------------------------------------------------------
 Update on products  (actual time=0.500..0.500 rows=24 loops=1)
   Buffers: shared hit=10 read=1
   ->  Seq Scan on products  (actual time=0.010..0.100 rows=24 loops=1)
         Filter: (category = '配件'::text)
         Rows Removed by Filter: 96
(5 rows)

注意事项 ​

  • EXPLAIN ANALYZE 会真正执行语句。 对 INSERT / UPDATE / DELETE 必须包在 BEGIN ... ROLLBACK 中。
sql
-- 错误！会真实删除数据
EXPLAIN ANALYZE DELETE FROM orders WHERE amount < 10;
-- 正确
BEGIN;
EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM orders WHERE amount < 10;
ROLLBACK;

  • 不要用 EXPLAIN 判断 SQL 是否正确。 它只输出计划，不保证语义，语法错误仍会报错，但逻辑错误不会暴露。

  • cost 是相对值，不是毫秒。 不同查询的 cost 不可横向比较，只能在同一查询的不同计划间比较。

  • 上层节点的 actual time 包含子节点耗时。 归因时要减去子节点，或直接看 Execution Time 与耗时最深的叶子节点。

  • 首次执行的 actual time 不准。 缓存未预热时会偏慢，性能对比应执行多次取稳定值。

  • rows=N 是平均值，不是总和。 在 Nested Loop 中 loops 很大时，需用 rows * loops 估算总量。

text
 ->  Index Scan on orders  (actual time=0.005..0.008 rows=1 loops=5000)

上例实际访问了 5000 × 1 = 5000 行。

  • Buffers 不开启时看不到 I/O 信息。 分析磁盘瓶颈必须加 BUFFERS 且配合 ANALYZE。

  • FORMAT JSON 的字段名与 TEXT 不同。 用程序解析时以 JSON 字段为准（如 Total Cost、Actual Rows）。

  • 并行计划不总是更快。 小表上并行带来额外调度开销，Workers Launched: 0 也可能是资源不足而非计划问题。

最佳实践 ​

  1. 日常分析统一用固定组合：EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)，既看实际数据又看 I/O，且屏蔽代价噪声。
sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM orders WHERE amount > 1000;

  1. 对比计划时保持环境一致，连续执行多次，SET work_mem 等参数变化要显式声明。

  2. 优先关注两件事：Rows Removed by Filter 过大、估算行数与实际行数差数量级，这两点指向大部分性能问题。

sql
-- 出现大量 Rows Removed by Filter 就该考虑索引
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM orders WHERE amount > 1000;
  1. 看到 external merge 就调 work_mem，看到 Batches > 1 就调哈希内存，二者都是内存不足信号。
sql
SET work_mem = '64MB';
  1. 让索引列「裸用」，避免函数或类型转换包裹，必要时建表达式索引。
sql
-- 不推荐
WHERE lower(sku) = 'sku-0001'
-- 推荐：建表达式索引
CREATE INDEX idx_products_sku_lower ON products (lower(sku));
  1. 用 ANALYZE 刷新统计信息后再确认计划，很多「优化器选错」其实是统计过期。
sql
ANALYZE orders;
  1. 用 auto_explain 扩展采集线上慢查询计划，避免逐个手工复现。
ini
-- postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
  1. 用 pg_stat_statements 定位谁慢，再用 EXPLAIN 分析为什么慢，两者配合是最标准的调优流程。
sql
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
  1. 对 DML 的计划分析一律包事务回滚，把「会改数据」这件事当成纪律而非例外。
sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) UPDATE products SET stock = 0 WHERE id = 1;
ROLLBACK;

下一节

继续学习：ANALYZE 统计更新

参考链接 ​