PostgreSQL EXPLAIN

一、EXPLAIN 是什么？

EXPLAIN 用来告诉你：PostgreSQL 打算“如何执行”这条 SQL

EXPLAIN SELECT * FROM users WHERE id = 1;

👉 不执行 SQL，只生成执行计划（Execution Plan）

EXPLAIN vs EXPLAIN ANALYZE

命令	是否执行 SQL	用途
EXPLAIN	❌ 不执行	看优化器“打算怎么跑”
EXPLAIN ANALYZE	✅ 会执行	看真实执行情况（最准）

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

⚠️ 生产环境慎用 ANALYZE（可能是 UPDATE / DELETE）

二、执行计划的整体结构

一个典型 EXPLAIN：

Seq Scan on users  (cost=0.00..35.50 rows=5 width=64)
  Filter: (age > 30)

核心结构

[执行节点]
  └─ 子节点

👉 从内到外读，从上到下执行

三、最重要的 5 个字段（必背）

(cost=0.00..35.50 rows=5 width=64)

字段	含义
cost	执行成本（估算值）
rows	预估返回行数
width	每行平均字节数
actual time	真实耗时（ANALYZE）
loops	节点执行次数

cost 详解

cost = 启动成本 .. 总成本

启动成本：返回第一行前的代价
总成本：返回所有行的代价

👉 cost 不是时间，是相对值

四、最常见的扫描方式（🔥重点）

1️⃣ Seq Scan（全表扫描）

Seq Scan on users

含义

从头扫到尾
大表通常是性能杀手

出现原因

无索引
条件不走索引
返回行太多

2️⃣ Index Scan（索引扫描）

Index Scan using users_pkey on users

特点

先走索引
再回表（heap）

适用

返回少量行

3️⃣ Index Only Scan（覆盖索引）

Index Only Scan using idx_users_age

特点（性能最好）

不回表
只读索引

条件

查询字段都在索引中
visibility map 足够干净（VACUUM）

4️⃣ Bitmap Index Scan + Bitmap Heap Scan

Bitmap Index Scan
Bitmap Heap Scan

适用

返回中等数量行
多条件索引

👉 常见于：

WHERE a = 1 AND b = 2

五、JOIN 执行方式（🔥🔥）

1️⃣ Nested Loop（嵌套循环）

Nested Loop

特点

小表驱动大表
适合：
主键 JOIN
小结果集

危险

大表 × 大表 = 灾难

2️⃣ Hash Join（最常用）

Hash Join

特点

构建 hash 表
适合等值 JOIN

ON a.id = b.id

3️⃣ Merge Join

Merge Join

特点

双方必须有序
常用于：
已排序索引
ORDER BY + JOIN

六、Filter / Index Cond 的区别

Index Cond: (id = 1)
Filter: (status = 'active')

项	含义
Index Cond	在索引层过滤
Filter	回表后再过滤（慢）

👉 Filter 越多，索引越没用

七、EXPLAIN ANALYZE 的核心指标

actual time=0.025..0.030 rows=1 loops=1

重点关注

actual time 是否远大于 cost
rows 预估 vs 实际是否差很多
loops 是否异常

估算不准的原因

统计信息过旧
数据分布不均

解决

ANALYZE table_name;

八、常见性能问题 & EXPLAIN 表现

❌ 1. 索引没用上

Seq Scan on big_table

可能原因：

函数包裹字段
隐式类型转换
LIKE ‘%xxx%’

❌ 2. rows 估算严重偏差

rows=10 actual rows=100000

原因：

统计信息不准
需要 extended statistics

CREATE STATISTICS s1 ON a, b FROM t;
ANALYZE t;

❌ 3. Nested Loop 爆炸

Nested Loop (rows=100000 loops=100000)

解决：

加索引
改 JOIN 顺序
提高 work_mem（允许 Hash Join）

九、EXPLAIN 常用参数（实战）

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

参数	作用
ANALYZE	真实执行
BUFFERS	显示缓存命中
VERBOSE	显示详细字段
TIMING	显示精确时间

BUFFERS 示例

Buffers: shared hit=120 read=10

hit：命中缓存
read：磁盘读取（慢）

十、EXPLAIN 调优套路（⭐通用）

一句口诀：

先看 Scan -> 再看 Join -> 再看 rows -> 最后看 Buffers

标准流程

是否全表扫描？
索引是否命中？
JOIN 方式是否合理？
rows 预估是否离谱？
是否大量磁盘 IO？

十一、其它

Q：EXPLAIN cost 是时间吗？

A：不是，是相对代价单位。

Q：为什么用了索引还是 Seq Scan？

A：返回行太多，优化器认为顺扫更快。

Q：Index Only Scan 为什么更快？

A：不回表，只读索引。

Q：Hash Join 什么时候比 Nested Loop 好？

A：大表等值 JOIN。

Q：如何让优化器更准确？

A：ANALYZE、VACUUM、extended statistics。

十二、可以直接用的 EXPLAIN 模板

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...