上一篇【第17篇】视图(View)与物化视图(Materialized View)——数据抽象与查询加速
下一篇【第19篇】索引原理与优化——从B-Tree到索引策略

---

标签：PostgreSQL、EXPLAIN、执行计划、查询优化、Seq Scan、Index Scan、ANALYZE

摘要：EXPLAIN 是 PostgreSQL 中最核心的性能诊断工具。它展示了数据库执行 SQL 的完整路径——从全表扫描到索引扫描，从嵌套循环到哈希连接。本文从基础到进阶，手把手教你读懂执行计划的每个节点，掌握性能优化的第一项核心技能。

---

一、开篇引言

一个 SQL 查询很慢，你怎么办？加索引？改 SQL？还是升级硬件？

答案都在执行计划里。EXPLAIN 就像 SQL 的"心电图"，它能告诉你数据库是怎么执行你的查询的——用了什么扫描方式、什么连接算法、预估了多少行、实际花了多少时间。

很多开发者遇到慢查询时，第一反应是"加个索引试试"。但如果你能读懂执行计划，就能精准定位问题：是该加索引、该改 SQL 写法、还是该调整统计信息。盲目优化不仅浪费时间，还可能让情况更糟。

本文将从零开始，带你逐个破解 EXPLAIN 输出中的各种节点，掌握这项 SQL 优化的核心技能。

---

二、EXPLAIN 基础

2.1 基本用法

-- 只看计划，不实际执行（速度快）
EXPLAIN SELECT * FROM orders WHERE amount > 1000;

-- 实际执行，显示真实耗时和行数（推荐使用）
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;

-- 显示详细信息（缓冲区命中、排序等）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE amount > 1000;

-- JSON 格式输出（方便程序分析）
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders WHERE amount > 1000;

-- 显示实际执行时间（包含 planning time 和 execution time）
EXPLAIN (ANALYZE, TIMING, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE amount > 1000;

注意：EXPLAIN ANALYZE 会实际执行查询（包括 INSERT/UPDATE/DELETE），在分析这些语句时要小心！

2.2 执行计划输出解读

EXPLAIN ANALYZE
SELECT o.customer_id, c.name, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 100
GROUP BY o.customer_id, c.name
ORDER BY total DESC
LIMIT 10;

输出示例：

Limit  (cost=... rows=10 width=...) (actual time=0.082..0.095 rows=10 loops=1)
  -> Sort  (cost=... rows=...) (actual time=0.079..0.087 rows=25 loops=1)
        Sort Key: (sum(o.amount)) DESC
        Sort Method: quicksort  Memory: 25kB
        -> HashAggregate  (cost=... rows=...) (actual time=0.056..0.066 rows=25 loops=1)
              Group Key: o.customer_id, c.name
              -> Hash Join  (cost=... rows=...) (actual time=0.023..0.043 rows=100 loops=1)
                    Hash Cond: (o.customer_id = c.id)
                    -> Seq Scan on orders o  (cost=... rows=...) (actual time=0.007..0.015 rows=100 loops=1)
                          Filter: (amount > 100)
                          Rows Removed by Filter: 50
                    -> Hash  (cost=... rows=...) (actual time=0.009..0.009 rows=50 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 4kB
                          -> Seq Scan on customers c  (cost=... rows=...) (actual time=0.004..0.006 rows=50 loops=1)
Planning Time: 0.152 ms
Execution Time: 0.148 ms

2.3 关键指标解读

每个节点都显示两组数据：

指标	说明
cost=...	优化器的代价估算（单位：磁盘页读取开销）
rows=...	优化器预估的行数（来自统计信息）
width=...	预估的每行字节数
actual time=..	实际执行时间（毫秒），范围是首行到末行
rows=... (actual)	实际返回的行数
loops=...	节点执行次数

关键对比：

• rows（预估）vs actual rows（实际）：差异大说明统计信息过时
• cost（预估）vs actual time（实际）：差异大说明代价模型不准

---

三、扫描方式

3.1 Seq Scan（顺序扫描/全表扫描）

Seq Scan on orders  (cost=0.00..15.00 rows=200 width=40) (actual time=0.010..0.050 rows=200 loops=1)

从头到尾读取整张表的所有数据块。适用于：

• 小表（几百行以内）
• 查询返回大部分行的场景
• 没有合适的索引

-- 触发全表扫描
EXPLAIN SELECT * FROM orders;  -- 查询所有数据

-- 索引列上使用函数（索引失效）
EXPLAIN SELECT * FROM orders WHERE LOWER(status) = 'pending';

3.2 Index Scan（索引扫描）

Index Scan using idx_orders_amount on orders  (cost=0.28..8.29 rows=1 width=40) (actual time=0.015..0.016 rows=1 loops=1)
  Index Cond: (amount = 299.99)

通过索引定位数据行的物理位置，然后到表中取出完整数据。适用于：

• 返回少量行的精确匹配查询
• 索引列上使用等值或范围条件

-- 创建索引后
CREATE INDEX idx_orders_amount ON orders(amount);
EXPLAIN SELECT * FROM orders WHERE amount = 299.99;
-- 走索引扫描

3.3 Index Only Scan（仅索引扫描）

Index Only Scan using idx_orders_date_amount on orders  (cost=0.28..8.29 rows=1 width=12) (actual time=0.010..0.011 rows=1 loops=1)
  Index Cond: (order_date = '2024-01-15' AND amount > 100)

所有需要的列都在索引中，不需要回到表中读取数据。性能最好！

-- 创建覆盖索引（包含查询需要的所有列）
CREATE INDEX idx_orders_covering ON orders(order_date, amount, customer_id);

-- 查询只需要索引中的列
EXPLAIN SELECT order_date, SUM(amount), COUNT(customer_id)
FROM orders
WHERE order_date = '2024-01-15'
GROUP BY order_date;
-- 走 Index Only Scan

3.4 Bitmap Scan（位图扫描）

Bitmap Heap Scan on orders  (cost=4.33..18.54 rows=100 width=40) (actual time=0.020..0.035 rows=100 loops=1)
  Recheck Cond: (amount > 100 AND amount < 1000)
  -> Bitmap Index Scan on idx_orders_amount  (cost=0.00..4.30 rows=100 width=0) (actual time=0.012..0.012 rows=100 loops=1)
        Index Cond: (amount > 100 AND amount < 1000)

位图扫描 = 先用索引构建位图，再根据位图批量读取表数据。适用于：

• 范围查询返回较多行（但不是大部分行）
• 同时使用多个索引

位图扫描 vs 索引扫描：

• 索引扫描：逐行随机读取，适合少量数据
• 位图扫描：批量顺序读取，适合中等量数据

---

四、连接方式

4.1 Nested Loop（嵌套循环）

Nested Loop  (cost=0.00..15.50 rows=50 width=80) (actual time=0.010..0.080 rows=50 loops=1)
  -> Seq Scan on orders  (cost=0.00..10.00 rows=50 width=40) (actual time=0.005..0.020 rows=50 loops=1)
  -> Index Scan using idx_customers_id on customers  (cost=0.00..0.11 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=50)
        Index Cond: (id = o.customer_id)

原理：外层表每返回一行，就在内层表中查找匹配行。类似于两个嵌套的 for 循环。

适用场景：

• 外层表返回行数少
• 内层表上有高效索引
• 小表驱动大表

4.2 Hash Join（哈希连接）

Hash Join  (cost=20.00..40.00 rows=500 width=80) (actual time=0.050..0.120 rows=500 loops=1)
  Hash Cond: (o.customer_id = c.id)
  -> Seq Scan on orders  (cost=0.00..10.00 rows=500 width=40) (actual time=0.005..0.030 rows=500 loops=1)
  -> Hash  (cost=10.00..10.00 rows=200 width=40) (actual time=0.010..0.010 rows=200 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 16kB
        -> Seq Scan on customers  (cost=0.00..10.00 rows=200 width=40) (actual time=0.003..0.007 rows=200 loops=1)

原理：用较小的表在内存中构建哈希表，然后扫描较大的表探测匹配行。

适用场景：

• 两张表都比较大
• 等值连接（ON a.id = b.id）
• 较小的表能完全放入内存

4.3 Merge Join（合并连接）

Merge Join  (cost=100.00..200.00 rows=1000 width=80) (actual time=0.100..0.200 rows=1000 loops=1)
  Merge Cond: (o.customer_id = c.id)
  -> Index Scan using idx_orders_cid on orders  (cost=0.00..50.00 rows=1000 width=40) (actual time=0.030..0.080 rows=1000 loops=1)
  -> Sort  (cost=50.00..52.50 rows=500 width=40) (actual time=0.040..0.060 rows=500 loops=1)
        Sort Key: c.id
        Sort Method: quicksort  Memory: 42kB
        -> Seq Scan on customers  (cost=0.00..10.00 rows=500 width=40) (actual time=0.005..0.015 rows=500 loops=1)

原理：先对两张表按连接键排序，然后类似"拉链"一样合并。

适用场景：

• 两张表已经按连接键排好序
• 或者有排序的开销不大
• 结果集较大

4.4 三种连接对比

连接方式	时间复杂度	适用场景
Nested Loop	O(N*M)	小结果集，内表有索引
Hash Join	O(N+M)	大表等值连接
Merge Join	O(NlogN + MlogM)	数据已排序，或范围连接

---

五、其他常见节点

-- Sort（排序）
-- 用于 ORDER BY、GROUP BY、DISTINCT 等
Sort  (cost=...) (actual time=...)
  Sort Key: amount DESC
  Sort Method: quicksort  Memory: 25kB   -- 内存排序
  Sort Method: external merge  Disk: 1024kB  -- 磁盘排序（注意性能问题！）

-- HashAggregate（哈希聚合）
-- 用于 GROUP BY
HashAggregate  (cost=...) (actual time=...)
  Group Key: customer_id

-- Limit（限制行数）
Limit  (cost=...) (actual time=...)

-- Filter（过滤条件）
Filter: (amount > 100)
Rows Removed by Filter: 50  -- 被过滤掉的行数

-- Subquery Scan（子查询扫描）
Subquery Scan on sub  (cost=...) (actual time=...)

-- CTE Scan（CTE 扫描）
CTE Scan on cte_data  (cost=...) (actual time=...)

---

六、统计信息——执行计划的基础

6.1 为什么统计信息重要

执行计划是优化器基于统计信息生成的。如果统计信息过时，优化器可能选择错误的执行计划：

-- 查看统计信息
SELECT * FROM pg_stats WHERE tablename = 'orders';

-- 手动收集统计信息
ANALYZE orders;

-- 收集所有表的统计信息
ANALYZE;

-- 详细收集（增加采样率）
ANALYZE VERBOSE orders;

6.2 预估行数 vs 实际行数

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 5000;
-- 如果预估行数和实际行数差距很大（超过10倍），说明统计信息过时

当 rows（预估）和 actual rows（实际）差距很大时：

1. 先执行 ANALYZE 更新统计信息
2. 如果 ANALYZE 后仍然差距大，可能需要调整统计目标
3. 考虑使用更精确的列统计

-- 增加统计目标（默认100，可调整到100-10000）
ALTER TABLE orders ALTER COLUMN amount SET STATISTICS 1000;
ANALYZE orders;

---

七、实战：优化慢查询

场景：订单查询很慢

-- 慢查询
EXPLAIN ANALYZE
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.amount > 100
ORDER BY o.amount DESC
LIMIT 50;

步骤1：查看执行计划，找到瓶颈节点

步骤2：根据瓶颈选择优化方案

• 全表扫描 → 创建索引
• 嵌套循环效率低 → 考虑哈希连接
• 磁盘排序 → 增加 work_mem
• 统计信息不准 → 执行 ANALYZE

步骤3：创建合适的索引

CREATE INDEX idx_orders_date_amount ON orders(order_date, amount DESC);

步骤4：再次 EXPLAIN ANALYZE 验证

---

八、EXPLAIN 使用技巧

-- 1. BUFFERS 选项：查看缓冲区命中情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders;

-- 2. VERBOSE 选项：显示列名
EXPLAIN (ANALYZE, VERBOSE) SELECT * FROM orders;

-- 3. COSTS OFF：隐藏代价信息，只看实际数据
EXPLAIN (ANALYZE, COSTS OFF) SELECT * FROM orders;

-- 4. TIMING OFF：隐藏时间信息（减少测量开销）
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM orders;

-- 5. SUMMARY 选项：添加汇总信息（PG16+）
EXPLAIN (ANALYZE, SUMMARY) SELECT * FROM orders;

-- 6. 最推荐的组合
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT) SELECT ...;

---

九、总结与下篇预告

本文系统讲解了 PostgreSQL 的 EXPLAIN 执行计划：

• EXPLAIN ANALYZE 是性能诊断的核心工具，必须实际执行才能看到真实数据
• 三种扫描方式：Seq Scan（全表扫描）、Index Scan（索引扫描）、Bitmap Scan（位图扫描）
• 三种连接方式：Nested Loop、Hash Join、Merge Join
• 关注 actual rows vs rows 的差异来判断统计信息是否过时
• BUFFERS 选项可以查看内存命中率，Sort Method: external merge 提示内存不足

下篇预告：第 19 篇将深入讲解索引原理与优化。索引是查询性能优化的核心武器。本文将从 B-Tree 索引的底层结构讲起，涵盖唯一索引、多列索引、索引失效场景、索引维护等核心知识点。

---

上一篇【第17篇】视图(View)与物化视图(Materialized View)——数据抽象与查询加速
下一篇【第19篇】索引原理与优化——从B-Tree到索引策略

---