[!info] related notes

• 课程 MOC：BodySense 数据库工程深化课程 MOC
• 前置：第 7 课
• 实践：用 EXPLAIN 分析查询计划

BodySense 数据库第 8 课：Query Planner 与 EXPLAIN ANALYZE

[!abstract] 本课目标 从“有没有走索引”升级到“优化器为什么选择这个计划，以及它估错在哪里”。

从零建立概念：SQL 不会按你写的“文本顺序”机械执行

你写：

SELECT ...
FROM a
JOIN b ON ...
WHERE ...
ORDER BY ...;

这表达逻辑语义。

PostgreSQL 真正执行前，还要经过：

Parse
→ Analyze / Rewrite
→ Plan / Optimize
→ Execute

Query Planner / Optimizer 的工作是：

在所有合法执行方式中，估计哪一个成本最低。

为什么同一 SQL 有很多执行方法

读取一个表：

Seq Scan
Index Scan
Index Only Scan
Bitmap Heap Scan

两个表 JOIN：

Nested Loop
Hash Join
Merge Join

多表时还要选择：

join order
filter pushdown
sort strategy
parallelism
aggregate strategy

组合数量会非常大。

Planner 不可能只靠“有 Index 就用”。

Cost 是什么

EXPLAIN 中：

cost=0.29..8.31

不是毫秒。

它是 Planner 的内部抽象成本单位，用配置参数近似：

page I/O
CPU tuple processing
operator cost
random vs sequential access

用于相对比较 candidate plans。

所以：

cost=100

不能直接翻译成：

100ms

Rows Estimate 为什么是 Planner 的核心输入

假设：

WHERE status = 'rare'

Planner 认为只返回 10 rows：

Nested Loop + Index
可能很便宜

实际返回 500k rows：

同一个 Nested Loop
可能灾难性昂贵

所以：

Cardinality Estimation 是 Query Optimization 的核心。

Planner 的 statistics 来自 ANALYZE 等机制：

null fraction
n_distinct
most common values
histogram
correlation
extended statistics

这些是采样/统计模型，不是每次 query 前精确 COUNT。

常见 Plan Node 从零认识

Seq Scan

顺序读取表 pages
→ 对每行检查 Filter

适合：

小表
或需要大比例 rows

Index Scan

index finds tuple locations
→ heap fetch rows

适合高选择性 lookup/range。

Index Only Scan

需要列都在 index
+ visibility 条件满足
→ 尽量避免 heap fetch

Plan 里的 Heap Fetches 可以帮助判断实际是否真的避开 Heap。

Bitmap Index / Bitmap Heap Scan

collect many tuple locations as bitmap
→ 按 heap page 顺序读取

常用于“匹配比单点 lookup 多，但又没多到整表扫描更划算”的中间区域。

三种 JOIN Algorithm 的直觉

Nested Loop

for each outer row
→ find matching inner rows

外层很小、内层有好 Index 时非常强。

Hash Join

build hash table from one side
→ scan other side
→ hash lookup matches

常适合较大的 equality join。

Merge Join

both sides ordered by join key
→ merge forward

已有排序/Index 或大 ordered inputs 时可能合适。

不要背：

Hash Join 最快

因为选择取决于数据量、排序、内存、selectivity 和 cost。

EXPLAIN：只看计划，不执行

EXPLAIN SELECT ...;

你得到的是 Planner 的 estimate：

node type
cost
rows
width
conditions

EXPLAIN ANALYZE：真正执行并测量

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

增加：

actual time
actual rows
loops
buffer hit/read

最大危险

ANALYZE 会真的执行 statement。

所以：

EXPLAIN ANALYZE DELETE ...

不是“只模拟删除”。

对 mutation 要在安全副本、显式事务 rollback 或等价只读实验中验证。

rows × loops：读 Plan 时不要漏掉 loops

一个 node：

actual rows=100 loops=1000

总处理规模可能接近：

100 × 1000

Nested Loop 下尤其容易出现“单次看起来很小，总体被重复很多次”。

BUFFERS 把 Plan 接到物理 Page

常见：

shared hit
→ Page 已在 shared buffer

shared read
→ 需要把 page 读入 shared buffer

于是：

SQL
→ Plan Node
→ touched pages
→ buffer / I/O

第 6 课的物理模型终于与查询诊断闭环。

怎样读一份 Plan：固定顺序

建议不要一上来只找“Index Scan”。

按：

1. Top-level actual execution time
2. 哪个 node 消耗最大
3. estimated rows vs actual rows
4. loops
5. filters / rows removed
6. sort / spill
7. heap fetches
8. buffers hit/read
9. 是否存在不必要重复工作

最后才提出优化假设。

优化永远要：

hypothesis
→ change
→ measure again

而不是靠索引口诀。

1. Planner 的任务

同一条 SQL 可能有很多执行方式：

Seq Scan vs Index Scan
Nested Loop vs Hash Join vs Merge Join
先过滤 A 还是先 Join B
先 Sort 还是利用 index order

Planner 用统计信息和 cost model 选择估计成本最低的计划。

2. EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN
→ 只展示估计计划

EXPLAIN ANALYZE
→ 真正执行并显示 actual timing/rows

对写语句使用 ANALYZE 时必须小心，因为它会真实执行。

3. 最重要的差异：Estimated Rows vs Actual Rows

如果：

estimated rows = 10
actual rows    = 100000

planner 可能因为错误基数估计选择了不合适的 join / scan。

原因可能是：

• statistics 陈旧；
• 数据分布偏斜；
• 列间相关性；
• 表达式缺少统计；
• predicate 本身难估。

4. BUFFERS

BUFFERS 帮助区分：

shared hit  → page 从 shared buffer 命中
shared read → 需要读取 page

它把上一课的 page 心智模型和实际查询计划连接起来。

Prediction

[!question]

1. Planner 没选索引，是否一定是 planner 错了？
2. 表很小的时候 Seq Scan 为什么可能比 Index Scan 更合理？
3. estimated rows 与 actual rows 差几个数量级说明什么？
4. EXPLAIN ANALYZE DELETE ... 是否安全地“只看计划”？
5. 查询突然变慢，但 SQL 和索引都没改，统计信息可能扮演什么角色？

[!success]- Answer / 解析 1. 不一定。 低选择性查询、大比例返回、随机 heap fetch 成本等都可能让 Seq Scan 更便宜。

2. 整表只有少量 page 时顺序扫描成本很低，额外走索引再访问 heap 反而更贵。

3. Planner 的 cardinality estimate 可能失真，后续 join/scan/order 决策都可能被连锁影响。

4. 不安全。 ANALYZE 会真实执行语句；需要事务回滚、只读副本或改写为等价 SELECT 等安全手段。

5. Planner 依赖 ANALYZE 统计；数据分布变化但统计滞后时可能选择明显不同的计划。

Lab

选择一条 BodySense 多表查询：

1. EXPLAIN；
2. EXPLAIN (ANALYZE, BUFFERS)；
3. 记录估计行/实际行、scan、join、sort、buffers；
4. 改变数据分布或索引后再次比较。

L4 Gate

拿到一份真实 plan，能指出最昂贵节点、行数估计误差、主要 I/O，并提出可以被实验验证的优化假设。