追本溯源：执行计划与统计信息

7 追本溯源：执行计划与统计信息

第 5 章已经说明：优化器比较的是基于统计与成本参数的候选路径，不是在预言未来耗时；第 6 章又把“看到 Seq Scan 或高 cost 不直接判错”写进 PREF-PLAN-005。本章开始为这条规则补运行证据。

读计划的核心不是认节点图标，而是沿一棵数据流树回答：

关系语义
  → planner 估计每一步会输出多少行
  → 候选路径怎样消费这些行
  → cost model 选择总成本较低者
  → executor 实际产生多少行、循环多少次、访问多少 buffer/WAL
  → 偏差来自统计、参数、条件表达、资源还是等待

本章用 100000 行确定性 fixture 制造两种经典偏差：region 与 order_status 完全相关，但普通统计分别观察两列；tenant_id=1 有 90000 行，其余 tenant 各 10 行，使 custom 与 generic plan 面对完全不同的选择率。另一个四分区 fixture 对照规划时裁剪、执行初始化裁剪和包裹分区键导致的失效。

本章目标

完成本章后，读者应当能够：

从叶子到根读懂 scan、join、sort、aggregate、materialize 的数据流；
区分 startup cost、total cost、estimated rows、width 与实际时间；
解释 cost 是相对比较单位，父节点 cost 已包含子树；
正确使用 EXPLAIN、ANALYZE、BUFFERS、WAL、SETTINGS 与机器可读格式；
知道 EXPLAIN ANALYZE 会真实执行 SQL，写语句必须有受控事务和副作用边界；
区分 planning、executor、server/client/network 与排队时间；
从 pg_stats 理解 null fraction、n_distinct、MCV、histogram 与 correlation；
用 dependency/MCV 扩展统计修复跨列估算，而不把统计当约束；
识别陈旧统计、数据倾斜、采样误差和表达式不匹配；
区分 plan-time、initialization-time 与 execution-time partition pruning；
知道 partition parent 不会由 autovacuum 自动分析，何时要显式 ANALYZE；
对照 custom/generic plan，理解参数敏感查询；
把 plan change 当调查信号，而不是自动回归；
正确使用 pg_stat_statements 的归一化聚合视角；
评估 auto_explain 的阈值、采样、参数泄露与 per-node timing 成本；
从 Pigsty 时间窗关联 query、database/user/application、wait、plan 与资源；
产出 baseline v0.2 proposal，为 PREF-PLAN-005 增加 runtime evidence。

实验边界

实验基线为 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04 L1；SQL 保持 PostgreSQL 14–18 可用。fixture 只创建：

shop_private.ch07_plan_probe（100000 行）；
shop_private.ch07_event_probe（3650 行、四个季度分区）；
shop_private.ch07_region_status_stats。

setup 会在 marker 完全匹配后重建这些专属对象，属于 R1；reset 删除它们，属于 R2，要求 action/target 双 token。EXPLAIN ANALYZE 即使查询只读也会真实执行并消耗资源，所以只在已确认 L1 运行。

下载资产：

本章目录

实测摘要

一次 PostgreSQL 18.6 运行得到：

correlated_estimate≈6300 → 25000 / actual=25000
impossible_estimate≈6300 → 1 / actual=0
custom_hot=Seq Scan / estimate=90000 / actual=90000
custom_cold=Index Scan / estimate=10 / actual=10
generic_estimate=100 / hot_actual=90000 / cold_actual=10
partition_counts=constant:1, wrapped:4, generic:1
partition_parent_stats=0→4

ANALYZE 使用统计抽样，所以修复前的约 6300 每次可能略变；node type、cost、buffers 和时间也不是 golden。稳定断言是偏差方向、修复幅度、参数敏感性、裁剪集合与前后业务 checksum。

章节验收

能从叶子到根解释计划树，正确使用 loops × rows；
不把 cost 当毫秒，不把 estimated rows 当扫描行数；
采集计划时同时保存 SQL、参数、版本、统计、settings 和 buffer/WAL；
对写计划知道怎样 rollback，并明确 sequence/外部副作用例外；
能读 pg_stats，知道 MCV/histogram 是 sample summary；
能说明单列统计为什么把相关条件近似独立相乘；
能选择 dependencies、ndistinct、MCV 的适用问题；
能证明 ANALYZE 后 estimate 改善，而不是只说“统计更新了”；
能区分三种 partition pruning 时机；
能从 Subplans Removed、loops 与 never executed 识别执行期裁剪；
能说明为何 partition parent 需显式 ANALYZE；
能对照 custom/generic plan 并识别参数倾斜；
plan 变化时先检查结果、SLO、估算、数据、统计、settings 和 wait；
不把 pg_stat_statements.queryid 当跨大版本永久 ID；
不在生产无评估开启 auto_explain.log_analyze/timing；
task.sh all 和双令牌 reset 均通过，ch04-v1 checksum 不变。

下一章 ch08《抽丝剥茧：慢 SQL 诊断方法论》将把单条计划放回真实 workload、等待与时间序列中。

参考资料

上一章：立木取信：开发规约与交付基线 · 返回上卷导读 · 下一章：抽丝剥茧：慢 SQL 诊断方法论 · 查看全书目录 · 查看索引中心

7.1 优化器如何选择路径

SQL 描述结果关系，planner 则要在等价实现中选择一棵可执行树。选择发生在当前 catalog、statistics、parameter visibility、planner GUC 和 cost constants 下；环境改变，最便宜路径也可能改变。

7.1.1 扫描、连接、排序、聚合与物化节点

叶子节点产生基础行：

Seq Scan 顺序访问 relation page，并在节点上应用 filter；
Index Scan 按 index 找 tuple，再访问 heap 取可见行/列；
Index Only Scan 仍需 visibility map 证明可跳过 heap；
Bitmap Index + Heap Scan 先收集 TID，再按 page 批量访问；
Function/Values/CTE/Subquery Scan 从非普通表来源产行。

没有“高级节点一定更快”。小表或低选择性查询用 Seq Scan 很合理；返回大量 heap row 时，随机 index fetch 可能更贵。Rows Removed by Filter 说明读到但未输出的行，不能与节点 rows 混为一谈。

中间节点转换数据流：

责任	常见节点	关键观察
join	Nested Loop / Hash Join / Merge Join	outer rows、inner loops、hash/sort 输入
order	Sort / Incremental Sort	key、method、memory、disk spill
aggregate	Aggregate / HashAggregate / GroupAggregate	group estimate、batches、memory/disk
reuse	Materialize / Memoize	重复读取是否被缓存，命中/溢出
combine	Append / Merge Append	child/partition 数与裁剪
parallel	Gather / Gather Merge	planned/launched workers、每 worker rows

Nested Loop 的 inner child 通常执行 outer row 次数，所以要看 loops；Hash Join 先构建 hash 再 probe，关注 build side、batches 与内存；Merge Join 要求两侧有序，排序可能由 index 或显式 Sort 提供。节点名只说明算法，不说明它在本次 cardinality 上是否正确。

7.1.2 成本、选择率、行数与路径竞争

计划行：

(cost=startup..total rows=N width=W)

startup 是开始输出前的估算成本；
total 假设节点完整执行；
rows 是节点输出行，不是读取/比较的全部行；
width 是平均输出 bytes；
父节点 cost 包含其子树成本；
cost 使用由 seq_page_cost 等参数构成的相对单位，不是毫秒。

planner 先估 predicate selectivity，再估每个节点 cardinality。一个底层 100 倍误差进入 join 后可能乘成更大误差，改变 join order、algorithm、memory 与 parallelism。因此排查计划常先找“最早出现的大 estimate/actual 偏差”，而不是先看顶层总时间。

路径竞争还受目标影响。带 LIMIT 时，低 startup 的路径可能优于完整执行 total 更低的路径；ORDER BY 与 index order 匹配时可以省 Sort；参数化 inner path 可让 Nested Loop 每次精确 index lookup。planner 选择的是其估计下的最低 cost，不保证统计错误时仍选到真实最快方案。

禁用 enable_seqscan 等 GUC 是诊断对照，不是永久修复。多数 enable flag 只是强烈抬高该路径成本，甚至在没有正确替代时仍会使用并标记 Disabled。对照的价值是回答“如果走另一条路径会怎样”，随后仍要修 SQL、统计、index 或 cost calibration 的根因。

7.1.3 计划树的阅读顺序与数据流

一套稳定阅读顺序：

先复述 SQL 的结果与参数，不看节点猜业务；
看顶层输出 rows、总时间与是否有 LIMIT/order/aggregate；
从叶子向根追每条数据流；
在每个 node 对比 estimated rows 与 actual rows × loops；
找第一处显著偏差与随后放大点；
看 filter/index cond/join filter 分别在哪层生效；
看 buffers、temp、WAL、sort/hash memory 与 worker；
最后结合 wait、客户端时间和并发判断瓶颈。

文本计划的缩进表示 parent/child，不表示实际先后时间。一个节点的 actual time=a..b 是每 loop 平均的 first/last row 时间；不能把所有节点时间简单相加，因为父时间包含子时间，pipeline 也会重叠。并行计划的 rows/loops 又可能按 worker 聚合或显示每循环平均，必须回到当前版本字段定义。

以本章参数实验为例，先不评价 Seq/Index：

custom hot:  estimate=90000 actual=90000
custom cold: estimate=10    actual=10
generic:     estimate=100   hot actual=90000 / cold actual=10

首先成立的结论是 generic estimate 对 hot 参数错了 900 倍；Seq Scan / Index Scan 的差异是这个 cardinality 与成本模型共同产生的结果。把结论写成“90% 选择率用 Seq Scan、0.01% 用 Index Scan”比“禁止 Seq Scan”更有解释力，但仍只对本表宽度、cache、index 和硬件有效。

计划树最终要翻译成一句因果链：

planner 看见什么统计/参数
  → 估了多少行
  → 为什么认为某路径便宜
  → executor 实际发生什么
  → 哪个可回退改变能验证假设

缺少其中任一段，都只是计划描述，不是诊断。

返回本章目录 · 下一节：正确使用 EXPLAIN · 查看全书目录 · 查看索引中心

7.2 正确使用 EXPLAIN

EXPLAIN 是观测工具，也会改变观测成本。普通 EXPLAIN 只规划；加 ANALYZE 后真实执行；加 timing、buffers、WAL 后又增加不同程度的采集工作。先明确问题，再选择选项。

7.2.1 `EXPLAIN`、`ANALYZE`、`BUFFERS`、`WAL`

推荐的机器证据：

EXPLAIN (
  ANALYZE,
  BUFFERS,
  WAL,
  SETTINGS,
  SUMMARY,
  FORMAT JSON
)
SELECT ...;

各选项回答：

ANALYZE：真实 rows、loops、time，且真的执行；
BUFFERS：shared/local/temp hit/read/dirtied/written；
WAL：records、FPI、bytes，主要对写路径有意义；
SETTINGS：影响 planner 且偏离 built-in default 的设置；
SUMMARY：planning/execution summary；
JSON/YAML/XML：给程序解析，文本留给人读。

buffer hit 不是“没有 I/O”，只表示 page 已在 PostgreSQL shared buffers；它可能刚由另一 backend 或操作系统读入。单次 warm run 不能代表 cold cache。WAL bytes 也不等于磁盘最终写入 bytes，FPI、compression、并发与 checkpoint 都会影响。

若只验证估算与树形，可先 EXPLAIN (FORMAT JSON)，避免执行高风险/高成本 SQL；若要 actual，先用生产等价的只读副本、L1 或受控参数范围。不要在事故高峰对未知查询直接加 ANALYZE。

7.2.2 规划时间、执行时间与客户端时间

EXPLAIN 的 Planning Time 与 Execution Time 都是服务器视角，通常不包含：

连接建立、pool queue；
客户端序列化/反序列化；
网络传输与 result consumption；
application thread/event-loop 排队；
transaction 中前后其他 SQL；
在开始采集前已经发生的重试。

而 planner cost 连服务器毫秒也不是。诊断至少对齐四个时间：

application span
  = pool/connect + server round trip + decode + application work

server statement duration
  = parse/plan（可能缓存）+ lock/wait + executor + output

EXPLAIN planning/execution
  = 本次受 instrument 影响的服务器测量

dashboard sample
  = 采样时间窗中的聚合/近似

TIMING off 仍保留 actual rows/loops 与总 execution time，可降低逐节点读时钟开销；当目标是 cardinality 而非每节点时间时更合适。反复执行要记录次数、warmup、参数和并发，报告分布而不是最佳一次。

7.2.3 对写语句使用 `ANALYZE` 的事务保护

EXPLAIN ANALYZE UPDATE/DELETE/INSERT/MERGE 会真实改数据、触发 trigger、约束、WAL 与锁。最小演练模式：

BEGIN;
SET LOCAL statement_timeout = '30s';
SET LOCAL lock_timeout = '5s';

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
UPDATE ...;

ROLLBACK;

rollback 能恢复同一 PostgreSQL transaction 内的数据变化，却不能撤销 sequence 值、某些外部副作用、通知接收方已经看到的消息，或 volatile function 对外部系统的动作。trigger/function 在演练环境也要审查。DDL、VACUUM 与不能在 transaction block 运行的命令又有不同边界。

生产写计划优先从同分布 L1、脱敏 clone 或 read-only EXPLAIN 开始；确需在线 ANALYZE 时限定精确 key、窗口、owner、timeout、before/after fingerprint，并确认复制/WAL预算。不要用 ROLLBACK 三个字把 R2/R3 动作伪装成 R0。

本章所有 ANALYZE 都是专属 fixture 上的 SELECT。task 保存原始 JSON，再由 Python 读取语义字段；它不 grep 文本节点，也不固定动态 cost/time。若 JSON 无效或 actual 行数漂移，分析立即失败。

上一节：优化器如何选择路径 · 返回本章目录 · 下一节：统计信息与估算偏差 · 查看全书目录 · 查看索引中心

7.3 统计信息与估算偏差

planner 不逐行检查数据再选计划；那相当于先执行查询。它读取 relation size、单列统计和可选扩展统计，近似选择率。理解近似来源，才能判断该 ANALYZE、提高 target、建扩展统计，还是改条件表达。

7.3.1 直方图、高频值、空值率与相关性

pg_stats 是对 pg_statistic 的可读视图，常用字段：

字段	含义
`null_frac`	NULL 比例
`n_distinct`	正值为估计 distinct 数；负值表示与行数比例
`most_common_vals/freqs`	高频值与频率
`histogram_bounds`	排除 MCV 后近似等频桶边界
`correlation`	列逻辑顺序与物理行序的相关程度

MCV 适合倾斜 equality；histogram 估 range；correlation 影响 ordered index scan 的 heap page 成本预期。它们是 ANALYZE sample 的摘要，不是约束或精确 count。statistics target 提高会扩大 sample/MCV/histogram 细节，同时增加 ANALYZE、planning 与 catalog 成本。

本章 tenant_id 中 1 占 90%，应成为 MCV；cold tenant 各 10 行。custom plan 看见具体参数时可用 MCV，估出 90000/10。generic plan不知道 $1，只能使用平均选择率，约估 100。

7.3.2 扩展统计解决跨列相关

单列统计分别知道：

region='east'       ≈ 25%
order_status='paid' ≈ 25%

若假设独立，AND 约为 6.25%，即 6250 行；fixture 实际把 east 完全对应 paid，所以是 25000。反之 east+cancelled 实际为 0，单列仍估约 6250。

扩展统计类型解决不同问题：

dependencies：列之间近似函数依赖，修正组合条件；
ndistinct：列组合 distinct 数，帮助 GROUP BY 等；
mcv：记录常见值组合，能表达常见或不可能组合；
expression statistics：为表达式本身收集统计。

实验执行：

CREATE STATISTICS shop_private.ch07_region_status_stats
  (dependencies, mcv)
ON region, order_status
FROM shop_private.ch07_plan_probe;

ALTER STATISTICS shop_private.ch07_region_status_stats
  SET STATISTICS 1000;
ANALYZE shop_private.ch07_plan_probe;

CREATE STATISTICS 只定义对象，ANALYZE 才填数据。修复后 present estimate 为 25000，impossible estimate 降到 planner 的非零下限 1。不能据此说扩展统计“证明 east 绝不会 cancelled”；约束负责拒绝，统计只服务估算，而且数据变化后会过期。

7.3.3 数据倾斜、陈旧统计与采样误差

估算偏差按顺序排查：

query parameter/类型/cast 是否与生产一致；
last_analyze、修改量与分布是否变化；
MCV/histogram 是否能容纳关键倾斜；
多列是否相关却被独立估计；
条件是否包裹列、使用 expression/函数而无统计；
join/partition parent 是否缺统计；
sample randomness 是否让边界值波动；
planner 是否因 generic plan 看不到参数。

只运行 ANALYZE 而不对比 estimate/actual，不能证明问题已修。提高全库 default_statistics_target 也通常太粗；先对问题 column/statistics object 局部提高，再衡量 planning/catalog 成本。

本章每次 setup 后普通 ANALYZE 的相关组合估算在约 6250 周围小幅变化，这是随机采样的预期；analyzer 断言修复前误差至少 2 倍、修复后不超过 1.5 倍，而不固定 6250。稳定测试检查关系，动态证据保存具体值。

统计正确也不保证计划最快：cost parameters、cache、并发、I/O 与 executor 能力仍参与。它只让 planner 对“会有多少行”拥有更好输入。

上一节：正确使用 EXPLAIN · 返回本章目录 · 下一节：分区裁剪的两种时机 · 查看全书目录 · 查看索引中心

7.4 分区裁剪的两种时机

分区裁剪依据 partition bounds 与可证明的条件移除无关 child，不依赖分区键上存在 index。裁剪减少要规划/执行的子计划，却不会自动让分区内访问高效，也不消除启动时取得的 relation lock。

7.4.1 规划时裁剪与常量条件

四个季度分区上：

WHERE occurred_on = date '2025-05-15'

planner 在规划时知道常量，证明只有 Q2 可能匹配；JSON 计划只出现 ch07_event_probe_2025q2。适合裁剪的条件通常直接用 partition key 与符合 bounds operator class 的等值/范围比较。

语义等价不代表 planner 能证明。例如：

WHERE date_trunc('month', occurred_on::timestamp)
      = timestamp '2025-05-01'

函数包住 partition key，当前实验计划出现四个 child；每个分区再 filter。正确性不变，裁剪合同丢失。修复通常把 API 月份转换成明确半开范围：

WHERE occurred_on >= date '2025-05-01'
  AND occurred_on <  date '2025-06-01'

不要为追求裁剪擅自改写时区/边界语义；先证明新 predicate 与业务时间合同等价。

7.4.2 执行时裁剪、参数化节点与通用计划

generic prepared plan 在 planning 时不知道 $1，仍可在 executor initialization 获得参数后裁剪：

SET plan_cache_mode = force_generic_plan;
PREPARE p(date) AS
SELECT event_id
FROM shop_private.ch07_event_probe
WHERE occurred_on = $1;

EXPLAIN (ANALYZE, FORMAT JSON)
EXECUTE p(date '2025-05-15');

本章结果只执行 Q2，并报告 Subplans Removed: 3。初始化期移除的 partition 不再显示为完整 child；真正 execution parameter（例如 nested loop inner 参数）改变时还可重复裁剪，此时要看各 child loops 与 (never executed)。

“计划文本里有 Append”不等于所有分区都被扫描。要联合看：

plan 中保留的 child；
Subplans Removed；
each child loops/actual rows/buffers；
planning time 与 partition 数；
执行开始时仍可能取得的 locks。

7.4.3 分区父表统计需要显式 `ANALYZE`

autovacuum 会处理普通 leaf partition，却不处理不直接存 tuple 的 partitioned parent；child 变化也不会触发 parent 的 inheritance statistics 更新。查询父表若依赖整体统计，应在首次装载及分布显著变化后显式：

ANALYZE shop_private.ch07_event_probe;

默认会同时递归分析 partitions。大型 hierarchy 要评估采样、lock 与窗口；可用 ONLY 控制范围，但要理解自己放弃了什么统计。

实验先只 ANALYZE 四个 leaf，父表 pg_stats 行数为 0；显式分析父表后变为 4。数字 4 对应当前四列，不是通用 golden；稳定结论是 0→非零。监控 leaf last_autoanalyze 不能替代检查 parent statistics。

7.4.4 产出裁剪生效与失效的计划对照

运行：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
cd static/labs/ch07
export PG36_EVIDENCE_DIR="$PWD/evidence/ch07/partition-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh setup
./task.sh partition
cat "$PG36_EVIDENCE_DIR/plan-summary-partition.txt"

注意两个 action 若复用同一 evidence directory，后者会更新 manifest；正式 release 建议直接 all 保持单一 bundle。稳定摘要：

partition_counts=constant:1,wrapped:4,generic:1
partition_parent_stats=0->4

原始三个 JSON 才是审计证据。analyzer 递归收集实际 relation nodes，并要求 generic Subplans Removed>=3；不 grep 本地化文本。

这个实验不证明业务表应该分区。它只证明已有 partition design 下，条件形状、参数可见性与统计维护怎样影响 planner。是否分区仍要回到 retention、规模、约束和运维收益，遵守 PREF-PART-003。

上一节：统计信息与估算偏差 · 返回本章目录 · 下一节：参数、缓存计划与计划漂移 · 查看全书目录 · 查看索引中心

7.5 参数、缓存计划与计划漂移

同一 query shape 可能面对完全不同参数选择率。每次用具体值规划可得到针对性路径，却支付 planning 成本；复用 generic plan 可省 planning，却看不到具体参数。缓存计划是成本取舍，不是“prepared statement 必然更快”。

7.5.1 自定义计划与通用计划

custom plan 把本次参数代入后规划，可使用 MCV、partition bounds 等具体信息；generic plan 保留 $1，适合参数分布相近或 planning 昂贵的重复语句。

在 plan_cache_mode=auto 下，带参数的 prepared statement 前五次使用 custom plan；之后服务器生成 generic plan，并把它的估算成本与前五次 custom plan 的平均估算成本比较。只有 generic plan 并未贵到值得反复重新规划时，后续执行才会复用它。这个启发式使用规划器成本，不等于实际执行时延；因此还要用代表性参数分别测量 planning time、execution time 与结果行数。force_custom_plan / force_generic_plan 只适合诊断对照，不应在缺少 workload 证据时全局强制。

prepared statement 是 session 对象。DDL、统计变化和影响解析/规划的设置可触发 re-plan；search_path 变化也会重新解析。transaction pooling 下 client 与 server session 生命周期不同，driver/PgBouncer 的 prepared statement 支持与配置必须单独验证，不能从裸 PostgreSQL session 外推。

7.5.2 预备语句、数据倾斜与参数敏感

fixture：

tenant 1    = 90000 rows
tenant 2..1001 = 10 rows each
index(tenant_id)

强制 custom：

hot:  estimate=90000 actual=90000 → 实测 Seq Scan
cold: estimate=10    actual=10    → 实测 Index Scan

强制 generic：

estimate=100 for both
hot actual=90000
cold actual=10

具体 node type 受表宽、cache、cost constants 与版本影响，不作为硬断言；真正的参数敏感证据是 custom estimate 能区分 90000/10，而 generic 必须使用同一估计。hot generic 路径即使本次仍足够快，也已经暴露风险：平均延迟可能掩盖少数大 tenant 的尾延迟。

处理方案按证据选择：

保持 auto，并观察 generic/custom 计数与参数分布；
对真正参数敏感的 workload 分 query shape 或显式选择策略；
改 schema/index/partition，使不同选择率下都可接受；
对 hot tenant 单独路由/批处理；
局部 force custom，并量化 planning 开销。

不要把 tenant literal 拼进 SQL 来“骗 custom plan”；这会扩大 query shape、失去安全参数绑定并污染统计。

7.5.3 计划变化是症状，不自动等于回归

计划会因以下因素合理变化：

数据量/分布与 ANALYZE sample
参数值与 custom/generic 决策
index/constraint/partition/schema
planner GUC/cost/JIT/parallelism
PostgreSQL/extension 版本
relation page/visibility/correlation

回归判断顺序：

result contract 是否仍正确；
同 workload 的 latency/throughput/resource/SLO 是否变差；
wait 还是 executor 在耗时；
estimate/actual 从哪里开始偏；
数据、statistics、settings 与参数是否可比；
新 plan 是否只是节点不同但成本更好；
改动是否增加写放大、WAL、memory 或尾延迟。

对计划做 canonical fingerprint 可以帮助发现变化，但不能把完整 JSON bytes 当稳定 API；版本会新增字段，cost/time 天生动态。保留原始计划，同时抽取 query identity、node responsibilities、cardinality error、buffers/spill/WAL 与环境 facts。

应急时可以用 planner GUC 证明替代路径，长期方案仍要回到统计、SQL、index、参数策略或版本 bug。第 8 章会把这里的计划证据放进慢查询闭环，第 9 章再审查 index 收益和写成本。

上一节：分区裁剪的两种时机 · 返回本章目录 · 下一节：建立计划证据基线 · 查看全书目录 · 查看索引中心

7.6 建立计划证据基线

手工 EXPLAIN 回答“这个参数现在怎样”，生产基线还要回答“哪些 query shape 消耗最多、何时变化、影响谁”。pg_stat_statements、日志/auto_explain 与 Pigsty 时间序列分别提供聚合、样本与上下文。

7.6.1 `pg_stat_statements` 的归一化视角

pg_stat_statements 按 database、user、toplevel 与 normalized query identity 聚合 calls、rows、planning/execution time、buffers、WAL、JIT/parallel 等累计量。literal 通常归一为 $1，所以适合找高总耗时、高均值/方差、高 I/O 或高调用频率的 query family。

使用时保留：

dbid + userid + queryid + toplevel
stats_since / minmax_stats_since
calls / rows
total/min/max/mean/stddev exec time
shared/local/temp blocks + WAL
representative query（权限受控）

queryid 是 hash，不保证无碰撞，也不保证跨 major version、不同架构或重建对象后永久稳定；相同文本还可能因 search_path 解析到不同对象而分开。把它作为某实例/版本时间窗内的关联键，不作全球业务 ID。

track_planning 默认关闭且可能带来并发更新开销；plans 与 calls 也不必相等，因为 cached plan、规划成功但执行失败等路径不同。reset 会破坏累计基线，生产只由受控 owner 在记录旧窗口后执行，不能为了实验清空全局统计。

7.6.2 `auto_explain` 的阈值、采样与日志成本

auto_explain 能在 query 超过 log_min_duration 时写计划，补上“事后再 EXPLAIN 已无法复现”的样本。但默认不做任何事；至少要设置阈值。上线前审查：

threshold 与 sample_rate 是否覆盖目标尾部且可控日志量；
log_analyze 是否需要 actual rows；
log_timing 的逐节点时钟成本；
buffers/WAL/triggers/nested statements 是否必要；
log_parameter_max_length 是否会泄露 PII/secret；
log format、保留、访问与脱敏；
preload/load 权限和配置变更方式。

尤其 log_analyze=on 时，per-node instrumentation 对所有被考虑的 statement 生效，即使最终没达到日志阈值；log_timing=off 可降低成本但失去节点时间。不要直接在繁忙生产设 threshold 0、sample 1、完整参数。

先在 L1 用代表 workload 测量开销和日志体积，再小比例/较高阈值 canary，最后从实际 signal 调整。auto_explain 样本不是全量分布，仍需 pg_stat_statements/metrics 提供 denominator。

7.6.3 从 Pigsty 时间窗保存 SQL、参数、统计、计划与环境上下文

Pigsty 的 PGSQL Query/Database/Activity、PGCAT Query、Session/Xacts、Persist 等入口把 query 统计与 cluster/instance/database 资源放在同一时间轴。排查时先固定：

UTC start/end
cluster / instance / primary-replica role
database / user / application
queryid + representative query
calls/latency/rows/buffers/WAL
CPU/I/O/load/connection/wait/lock/replica lag
PostgreSQL/Pigsty/config/schema/statistics version

然后选具体参数在等价 L1 采集 machine-readable EXPLAIN。dashboard screenshot 只能证明画面，最好同时导出 query/metric value、过滤条件和 timezone。短查询可能落在 scrape interval 之间；瞬时 blocker 要回到 catalog/log。

参数可能含个人数据，query text 也可能暴露 literal。证据包使用最小权限、脱敏和访问控制，不能把 pg_read_all_stats 给业务角色，也不能把 Grafana datasource credential 导出。

一次完整计划证据应能回答：

这是哪个 workload 的哪个时间窗？
聚合上影响多大？
选择了哪个代表参数，为什么？
当时统计、schema、settings 和数据分布是什么？
estimate/actual、buffers/wait 的根因假设是什么？
变更前后结果/SLO/写成本怎样？
怎样回退，何时复查？

这套证据将在第 8 章变成慢查询诊断模板；本章暂不把 dashboard panel 名或 metric label 当跨版本稳定接口。

上一节：参数、缓存计划与计划漂移 · 返回本章目录 · 下一节：实战：解释订单查询的计划变化 · 查看全书目录 · 查看索引中心

7.7 实战：解释订单查询的计划变化

综合实验不追求把某个 node 调成最快，而是建立一条可审计推理链：确定性数据制造已知分布，保存修复前 JSON 计划，只改变一个统计/条件因素，再保存修复后计划并由程序检查关系。

7.7.1 用固定数据种子制造估算偏差

确认 service 指向可写 pg36_shop L1：

export PGSERVICEFILE=/absolute/private/path/pg_service.conf
export PGSERVICE=pg36-admin
cd static/labs/ch07
export PG36_EVIDENCE_DIR="$PWD/evidence/ch07/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

all 的顺序：

ch04-v1 verify-before
  → marker collision guard + deterministic setup
  → correlated plans before
  → CREATE STATISTICS + ANALYZE
  → correlated plans after
  → custom/generic parameter plans
  → constant/wrapped/generic partition plans
  → parent ANALYZE
  → Python semantic assertions
  → fixture verify + ch04-v1 verify-after

fixture 分布固定：

rows=100000
east+paid=25000
east+cancelled=0
tenant 1=90000
tenant 1001=10

region/status 分别四等分但完全对应。普通单列统计近似把两个 25% predicate 相乘，所以两种组合都估约 6250。具体值随 ANALYZE sample 波动，不能 hard-code；raw stats-before-*.json 保存本次 estimate、actual、buffers、settings 和时间。

参数实验选择 128-byte payload，确保 hot tenant 的 heap fetch 与 cold tenant 有明显路径取舍。analyzer 不强制节点必须是 Seq/Index，只强制 custom estimate 接近实际、generic 对两个参数使用同一 estimate，且 hot generic 偏差至少 100 倍。

7.7.2 修复统计与条件表达后重新比较

扩展统计只改变 planner knowledge，不改变数据、index 或 SQL：

present:    ~6300 → 25000 / actual 25000
impossible: ~6300 → 1     / actual 0

这证明问题属于跨列相关，不需要先建组合 index；若查询性能仍不达标，再用第 9 章方法评估 index。扩展统计改善 estimate 也可能不改变 node，因为本查询没有 region/status index 且需要 seq scan；“plan shape 没变”不代表修复无效。

分区实验只改变 predicate：

occurred_on = constant          → one child at plan time
date_trunc(... occurred_on ...) → four children, child filters
generic $1 equality             → one executed child, 3 removed

修复 wrapped 条件应在应用边界先算月初/月末，再直接写 partition-key 半开范围。创建 expression index 可能帮助分区内 filter，却不保证 partition bounds 能由该表达式裁剪；index 与 pruning 是两种机制。

父表统计单独验证 0→4。若只看 child autoanalyze，会遗漏 parent。生产维护频率由分布变化而非固定日历决定，并观察 pg_stat_progress_analyze、锁与资源。

查看结果：

cat "$PG36_EVIDENCE_DIR/plan-summary-all.txt"
python3 -m json.tool "$PG36_EVIDENCE_DIR/plan-summary-all.json"

一次实测：

status=ok
correlated_estimate=6352->25000/actual=25000
impossible_estimate=6275->1/actual=0
custom_hot=Seq Scan/estimate=90000/actual=90000
custom_cold=Index Scan/estimate=10/actual=10
generic_estimate=100/hot_actual=90000/cold_actual=10
partition_counts=constant:1,wrapped:4,generic:1
partition_parent_stats=0->4

时间与 buffers 用来比较同一次受控实验，不进入稳定 summary。要比较性能，应重复运行并记录 cache/并发条件；本章只验 cardinality 与裁剪机制。

安全复位

实验对象保留供观察。确认不再需要后：

export PG36_RESET_TOKEN=RESET_CH07_PLAN_LAB
export PG36_RESET_TARGET=pg36_shop/shop_private/ch07
export PG36_EVIDENCE_DIR="$PWD/evidence/ch07/reset-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh reset

reset 先复核 database、primary、ch04-v1、两个 relation marker 与两个 token，再只删除 ch07 relation。错误 token 必须 exit 3，且对象仍存在；成功后 remaining_ch07_relations=0，ch04-v1 relation checksum 仍为：

f8a7bfae59c6d16cd323abecfefe1014

7.7.3 把一条有证据的计划规则追加到规约

v0.1 的 PREF-PLAN-005 已声明：

看到 Seq Scan、Nested Loop 或高 cost 不直接判错；
先定位 estimate/actual、loops、buffers、wait 与 workload，
再用可回退对照验证 statistics、SQL 或 index 变更。

baseline-v0.2-proposal.json 不改写不可变 v0.1，而是基于其 canonical checksum 提案：

base=0.1.0
candidate=0.2.0
rule=PREF-PLAN-005
statement_change=none
change=evidence-and-runtime-check

新增 runtime check：

保存机器可读 plan，比较 estimate/actual、参数计划与裁剪范围；
不得以节点名或 cost 单独判定回归。

Python analyzer 会重新计算 v0.1 canonical checksum，拒绝 proposal 绑定到被悄悄修改的 base；也验证三项 evidence artifact 存在。当前 proposal 仍是 candidate，因为 promotion 还要求：

PostgreSQL 14–18 兼容矩阵通过；
至少一个不同硬件或规模复测；
合并为独立、不可变的 v0.2 release artifact。

这体现规则证据的正确节奏：单次 PostgreSQL 18.6 实验足以形成候选 runtime check，不足以宣称跨版本普遍阈值。后续复测即使 node type 不同，只要 cardinality 与 partition pruning 的因果关系仍成立，规则就得到进一步确认；若机制变化，就修正 scope/compatibility，而不是让测试强行 grep 旧节点。

完成本章后，读者应提交的不是一张漂亮计划图，而是：

manifest + source hashes
raw plans before/after
machine summary
fixture distribution
PostgreSQL/session facts
business model verify-before/after
rule proposal + known promotion gap

下一章会从 Pigsty 的真实慢查询时间窗选择 query family，再沿本章方法进入单条计划。

上一节：建立计划证据基线 · 返回本章目录 · 下一章：抽丝剥茧：慢 SQL 诊断方法论 · 查看全书目录 · 查看索引中心

追本溯源：执行计划与统计信息

7 追本溯源：执行计划与统计信息

本章目标

实验边界

本章目录

7.1 优化器如何选择路径

7.2 正确使用 EXPLAIN

7.3 统计信息与估算偏差

7.4 分区裁剪的两种时机

7.5 参数、缓存计划与计划漂移

7.6 建立计划证据基线

7.7 实战：解释订单查询的计划变化

实测摘要

章节验收

参考资料

7.1 优化器如何选择路径

7.1.1 扫描、连接、排序、聚合与物化节点

7.1.2 成本、选择率、行数与路径竞争

7.1.3 计划树的阅读顺序与数据流

7.2 正确使用 EXPLAIN

7.2.1 EXPLAIN、ANALYZE、BUFFERS、WAL

7.2.2 规划时间、执行时间与客户端时间

7.2.3 对写语句使用 ANALYZE 的事务保护

7.3 统计信息与估算偏差

7.3.1 直方图、高频值、空值率与相关性

7.3.2 扩展统计解决跨列相关

7.3.3 数据倾斜、陈旧统计与采样误差

7.4 分区裁剪的两种时机

7.4.1 规划时裁剪与常量条件

7.4.2 执行时裁剪、参数化节点与通用计划

7.4.3 分区父表统计需要显式 ANALYZE

7.4.4 产出裁剪生效与失效的计划对照

7.5 参数、缓存计划与计划漂移

7.5.1 自定义计划与通用计划

7.5.2 预备语句、数据倾斜与参数敏感

7.5.3 计划变化是症状，不自动等于回归

7.6 建立计划证据基线

7.6.1 pg_stat_statements 的归一化视角

7.6.2 auto_explain 的阈值、采样与日志成本

7.6.3 从 Pigsty 时间窗保存 SQL、参数、统计、计划与环境上下文

7.7 实战：解释订单查询的计划变化

7.7.1 用固定数据种子制造估算偏差

7.7.2 修复统计与条件表达后重新比较

安全复位

7.7.3 把一条有证据的计划规则追加到规约

7.2.1 `EXPLAIN`、`ANALYZE`、`BUFFERS`、`WAL`

7.2.3 对写语句使用 `ANALYZE` 的事务保护

7.4.3 分区父表统计需要显式 `ANALYZE`

7.6.1 `pg_stat_statements` 的归一化视角

7.6.2 `auto_explain` 的阈值、采样与日志成本