8.2 基数估算、成本模型与执行计划诊断

同一条查询换了参数后突然变慢，可以先比较中间结果的估算行数与实际行数。基数估算若偏小，优化器可能选中反复探测的大型嵌套循环，或给哈希表留下不足的内存。本课用统计信息和执行计划定位偏差，再检查成本、等待和资源；计划里数字很精确，小数点却不是准确性的担保。

Optimizer 选择什么

physical alternatives 包括：

• access path：sequential/index/bitmap/partition scan；
• join order 与 join algorithm；
• predicate placement；
• aggregation/sort implementation；
• parallelism、partition exchange、materialization；
• required/provided order；
• remote pushdown；
• JIT/compiled execution thresholds。

optimizer 不一定枚举所有可能 plans。search space 随 joins 增长迅速，系统会用 dynamic programming、memo/cascades、heuristics、join-order restrictions 或 genetic/randomized search 剪枝。

Cost 不是毫秒

PostgreSQL plan 中：

cost=startup_cost..total_cost
rows=estimated_output_rows
width=estimated_average_row_bytes

cost 是按配置参数加权的抽象单位，用来比较 alternatives，不直接等于 wall-clock milliseconds。常见组成是 page I/O、tuple/operator CPU、parallel setup/transfer、sort/hash 等模型。

seq_page_cost、random_page_cost 等默认与意义会随版本/配置变化。不能写“SSD 就把 random_page_cost 固定设成 1.1”。这些参数还隐含 cache 与整个 workload 的相对代价，需要 benchmark 和 plan regression 验证。

Selection cardinality

optimizer 可能使用：

• table row/page estimate；
• NULL fraction；
• number of distinct values；
• most-common values/frequencies；
• histogram bounds；
• physical correlation；
• expression/index statistics；
• constraints/partition bounds。

等值 predicate 对 MCV 可用直接频率；不在 MCV 的 value 常按 remaining distinct distribution 估算。range predicate 用 histogram interpolation。它们都是 sample/model estimates，不是运行 query 得到的真值。

Correlated columns

WHERE country = 'CN'
  AND city = 'Shanghai'

若优化器假设 independence：

selectivity(country='CN') × selectivity(city='Shanghai')

会低估相关组合。PostgreSQL extended statistics 可记录 dependencies、MCV combinations 或 ndistinct：

CREATE STATISTICS stats_country_city
    (dependencies, mcv, ndistinct)
ON country, city
FROM addresses;

ANALYZE addresses;

这段需要 PostgreSQL 中已有 addresses(country, city) 表，是配置与分析片段；WHERE 块只是谓词片段。扩展统计不覆盖所有谓词或跨表相关性，具体支持见 PostgreSQL 扩展统计。

Join cardinality

简化 equijoin estimate 可能依赖两侧 rows、distinct counts、MCV 与 uniqueness：

|R ⋈ S| ≈ |R| × |S| / max(ndistinct(R.k), ndistinct(S.k))

这是假设均匀/containment 的粗略公式。skew、partial overlap、NULL、composite key correlation 和 filters 会破坏它。PK/FK/UNIQUE constraints 能提供强证据，所以 constraints 也帮助 optimization，不只负责 integrity。

Statistics 为什么过时

• bulk load 后未 analyze；
• rapidly changing/partitioned table；
• sampling 没捕获 rare skew；
• prepared statement parameters 分布不同；
• expression/correlation 未建 statistics；
• temporary table lifecycle；
• remote source 没提供准确 estimates。

“突然慢的头号原因就是没 ANALYZE”过度武断。I/O、lock、plan cache、data growth、bloat、network 与 dependency latency 都可能是根因。

Join-order search

inner joins 常可重排；outer/semi/anti/lateral joins 和 volatile semantics 会限制 legal orders。即使三张表有多种 parenthesization，commutativity 还会产生重复表示，optimizer 用 equivalence classes/memo 避免盲目全排列。

PostgreSQL 对较小 join problem 使用 exhaustive/dynamic-programming style search，对超过 geqo_threshold 的情况可启用 GEQO。GEQO 不是 PostgreSQL 12 才出现，具体 threshold/default 以当前配置为准。

把 15-table query 手工拆成 temp tables 可能降低搜索空间，也可能丢失 pushdown/reordering、增加 I/O 和改变 transaction semantics。先用 plan evidence 判断。

Prepared statements：custom 与 generic plan

同一 SQL 的最佳 plan 可能依赖 parameter：

SELECT * FROM events WHERE tenant_id = $1;

小 tenant 适合 index scan，大 tenant 可能适合 sequential/bitmap scan。系统可在每次根据 parameter 生成 custom plan，或复用 generic plan 节省 planning time。

parameter sniffing/generic-plan regression 的表现是某些 values 快、某些慢。诊断要记录 parameter distribution 与 plan choice，不要把 literal 拼进 SQL 规避 plan cache 并引入 injection。

阅读 EXPLAIN ANALYZE

以下 PostgreSQL 只读聚合片段需要已有 customers 与 tasks 表；先核对实例、表结构与查询负载：

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF, SUMMARY ON)
SELECT a.customer_id, COUNT(*)
FROM customers AS a
JOIN tasks AS q
  ON q.customer_id = a.customer_id
WHERE q.status = 'active'
GROUP BY a.customer_id;

ANALYZE 会实际执行语句。对 INSERT/UPDATE/DELETE，优先使用可丢弃的测试实例；若在事务中执行后 ROLLBACK，仍需单独检查序列变化、触发器或函数的外部副作用，因为回滚不能撤销所有外部动作。

首先比较 estimated 与 actual rows

找最早出现数量级偏差的 node，而不是只看 root。上游错误会传递到 parent。

loops

PostgreSQL 的 actual time 与 rows 通常是 per-loop averages；总 rows/work 需结合 loops。例如 actual rows=25 loops=400 表示约处理 10000 行，不是只做了 25 行的工作。并行计划还要结合 worker details 和 gathered rows。父节点的时间及 buffer 统计可能包含子节点工作，不要把整棵树各项直接相加。

Buffers

• shared hit/read/dirtied/written；
• temp read/written；
• local buffers；
• I/O timing（若配置收集）。

buffer hit 不等于零成本，read 不一定等于 device miss；OS cache 与 async I/O 仍在下层。

Rows Removed

大量 rows removed by filter 表明 access path 读了许多后过滤，但不自动意味着“加一个单列 index”。需要评估 composite/partial index、selectivity、write cost 与 query importance。

Sort/Hash

查看 sort method、memory、disk；hash buckets/batches/memory。batches > 1 常表示 spill/repartition，但版本输出字段不同。

一套诊断顺序

1. 保存 SQL、parameters、schema/index、数据库版本与 plan settings；
2. 确认等待时间：CPU、I/O、lock、client/network；
3. 获取带 actual rows/buffers 的安全 plan；
4. 找第一个 cardinality 大偏差；
5. 检查 predicate semantics、stats、correlation、constraints；
6. 检查 operator resource：spill、loops、heap fetch、parallel skew；
7. 形成最小 change：stats/index/query/schema/config；
8. 用代表 parameters 和冷/热 cache 对照；
9. 观察写入成本、其他 queries 与并发 guardrails。

Query rewrite 的边界

• EXISTS 可表达 semijoin，避免 join duplicate；
• NOT EXISTS 更安全表达 nullable anti-join；
• predicate pushdown 要尊重 outer join/NULL；
• function/cast 包住 indexed column 可能阻止普通 index condition，可用 expression index 或改写；
• CTE 是否 inline/materialize 取决于产品版本与语法选项；
• OR 可用 bitmap OR、union 或 scan，手改 UNION ALL 要处理 duplicate semantics。

任何 rewrite 都先证明结果等价，再比较 plan。

Remote/FDW

remote source optimization 受限于 connector 能下推的 filter/join/aggregate、remote statistics、network latency 和 transaction semantics。optimizer 看到的 local cost 可能缺少 remote queue/cache/skew。应从计划确认哪些算子在远端执行，以及传回多少行或字节，再判断瓶颈是否在本地。

验收清单

• [ ] 不把 cost 当毫秒；
• [ ] 找最早 cardinality 偏差，不只看 root；
• [ ] actual rows/time 结合 loops；
• [ ] 统计能描述单列/扩展相关性，但不是实时真值；
• [ ] prepared statement 同时测试代表 parameters；
• [ ] EXPLAIN ANALYZE 的副作用已控制；
• [ ] 优化后检查写成本、并发和其他 plans。

本章小结

执行器决定“怎样做”，优化器依据不完整 statistics 估计“哪种做法更便宜”。最重要的诊断信号通常是 intermediate cardinality 与实际资源，而不是某个 node 名字看起来是否高级。下一章进入 query compilation：如何减少解释器/iterator overhead，并把表达式变成更贴近 CPU 的代码。

官方资料入口

• PostgreSQL Using EXPLAIN
• PostgreSQL Planner Statistics
• PostgreSQL Genetic Query Optimizer
• PostgreSQL Runtime Config: Query Planning