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 ...

EXPLAIN ANALYSE Execution Time 和实际的查询时间差距很大

这是一个非常典型、而且非常容易被误判的问题。

下面按「结论 -> 原因分类 -> 如何验证 -> 解决建议」可以直接对照排查的完整解释。

一、先给结论（重点）

EXPLAIN ANALYZE 里的 Execution Time ≠ 客户端感知到的“查询耗时”

两者本来就不一定相等，而且差距大通常是正常现象。

二、Execution Time 到底统计了什么？

EXPLAIN ANALYZE 统计的是：

包含	不包含
SQL 执行器耗时	网络传输
表扫描 / 索引扫描	客户端渲染
排序 / Join / 聚合	ORM 序列化
WAL 生成	客户端解析
CPU + IO	客户端等待

📌 不包括：

结果集传输时间
客户端读取时间
客户端打印 / JSON 序列化
ORM 映射对象

三、最常见的 8 个原因（非常实战）

1️⃣ 结果集很大（第一大原因）

典型现象

EXPLAIN ANALYZE SELECT * FROM logs;
Execution Time: 20 ms

客户端却：

2.3 秒

原因

PostgreSQL 很快算完
慢在“发数据”

📌 EXPLAIN 不计算 socket 发送时间

验证方法

EXPLAIN ANALYZE SELECT count(*) FROM logs;

如果 count(*) 很快，说明慢在结果传输。

2️⃣ ORM / 驱动层处理很慢

常见场景

SQLAlchemy
Django ORM
返回对象数多

session.execute(stmt).scalars().all()  # 慢

📌 ORM 创建对象、映射字段非常耗时

验证方法

psql 里执行同样 SQL
对比 ORM 执行时间

3️⃣ 网络延迟（远程数据库）

环境	影响
本机 psql	很快
远程 ORM	很慢
跨地域	非常慢

📌 EXPLAIN 完全不涉及网络

4️⃣ 客户端分页 / 渲染时间

CLI 输出 1 万行
GUI 工具（DBeaver / DataGrip）

📌 显示比查询还慢

5️⃣ 查询被缓存了（假快）

EXPLAIN ANALYZE 连跑两次

第一次慢
第二次快

📌 Execution Time 是缓存命中后的

6️⃣ EXPLAIN ANALYZE 本身改变执行方式

禁用了并行？
不走缓存路径？

尤其是：

EXPLAIN ANALYZE INSERT / UPDATE

7️⃣ 并行查询的时间理解错误（高级）

Workers Planned: 4
Execution Time: 100 ms

并不等于：

4 × 100 ms

👉 是墙钟时间（wall clock）

8️⃣ autocommit / 事务提交耗时

客户端看到慢，其实慢在：

commit
fsync
WAL flush

📌 EXPLAIN 统计不包含 commit

四、最重要的一条：如何对齐真实时间？

方法 1：psql 打开计时

\timing on
SELECT ...;

你看到的才是真实端到端耗时

方法 2：对比 3 个时间

时间	看哪里
EXPLAIN Execution Time	DB 执行
psql \timing	DB + 网络
应用日志	全链路

五、快速定位“慢在哪一层”（表格）

现象	结论
EXPLAIN 快，psql 慢	网络 / 结果集
psql 快，ORM 慢	ORM
首次慢，后续快	缓存
count(*) 快，select 慢	传输
执行时间抖动	IO / 云盘

六、实战示例对照

SQL

SELECT * FROM articles ORDER BY datetime DESC LIMIT 10000;

场景	时间
EXPLAIN ANALYZE	15 ms
psql	120 ms
Python ORM	800 ms

👉 数据库不是瓶颈

七、解决建议（按优先级）

✅ 优先级 1

LIMIT
只 SELECT 必要字段

✅ 优先级 2

游标 / 流式读取
服务器端分页

✅ 优先级 3

降低网络 RTT
数据库与应用同机房

八、总结

EXPLAIN ANALYZE 的 Execution Time 只统计数据库内部执行时间，不包含网络传输、客户端处理和结果渲染，两者差距大通常是结果集或客户端问题，而非 SQL 本身慢。