8.2 基数估算、成本模型与执行计划诊断
一条查询昨天只跑几十毫秒，今天却因为换了连接顺序超时；执行计划里最先失真的，是中间结果行数。
优化器最难的不是知道有哪些算法，而是在执行前估算每个中间 relation 有多少 rows。一次 100 倍的 cardinality error 会沿 join tree 放大，让原本适合小 outer 的 nested loop 被用于百万 rows，或让 hash table 严重低估 memory。
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_bytescost 是按配置参数加权的抽象单位，用来比较 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;support 与适用 predicate 要查当前版本。extended stats 不会神奇解决跨表 correlation 或所有 expression。
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 安全的 read query 示例：
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, TIMING OFF, SUMMARY ON)
SELECT a.adventurer_id, COUNT(*)
FROM adventurers AS a
JOIN quests AS q
ON q.adventurer_id = a.adventurer_id
WHERE q.status = 'active'
GROUP BY a.adventurer_id;ANALYZE 实际执行 statement。对 INSERT/UPDATE/DELETE 使用前应在 transaction 中 ROLLBACK 或在复制环境分析，并考虑 triggers/external side effects。
首先比较 estimated 与 actual rows
找最早出现数量级偏差的 node，而不是只看 root。上游错误会传递到 parent。
loops
PostgreSQL 的 actual time 与 rows 通常是 per-loop averages；总 rows/work 需结合 loops。parallel plan 中 worker details 和 gathered rows 也要一起看。
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，但版本输出字段不同。
一套诊断顺序
- 保存 SQL、parameters、schema/index、数据库版本与 plan settings；
- 确认等待时间：CPU、I/O、lock、client/network；
- 获取带 actual rows/buffers 的安全 plan；
- 找第一个 cardinality 大偏差；
- 检查 predicate semantics、stats、correlation、constraints；
- 检查 operator resource：spill、loops、heap fetch、parallel skew；
- 形成最小 change：stats/index/query/schema/config；
- 用代表 parameters 和冷/热 cache对照；
- 观察写入成本、其他 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。
检查 plan 中哪些操作在 remote 执行、传回多少 rows/bytes，而不是笼统说“军师管不到远程表”。
验收清单
- [ ] 不把 cost 当毫秒；
- [ ] 找最早 cardinality 偏差，不只看 root；
- [ ]
actual rows/time结合 loops； - [ ] 统计能描述单列/扩展相关性，但不是实时真值；
- [ ] prepared statement 同时测试代表 parameters；
- [ ]
EXPLAIN ANALYZE的副作用已控制； - [ ] 优化后检查写成本、并发和其他 plans。
本章小结
执行器决定“怎样做”，优化器依据不完整 statistics 估计“哪种做法更便宜”。最重要的诊断信号通常是 intermediate cardinality 与实际资源，而不是某个 node 名字看起来是否高级。下一章进入 query compilation：如何减少解释器/iterator overhead，并把表达式变成更贴近 CPU 的代码。