← 返回文章列表

2026年6月20日 更新于 2026年6月24日

PostgreSQL Planner、Executor 与 EXPLAIN 执行计划分析

从一条 SQL 到生产事故，串起 Planner 估算、Executor 真实执行、EXPLAIN 证据链、pgx 观测闭环与生产排障方法。

第 6 章　从一条 SQL 到生产事故：Planner、Executor 与 EXPLAIN

技术基线：PostgreSQL 18；主要内容兼容 PostgreSQL 14—18。Go 示例使用 github.com/jackc/pgx/v5 与 pgxpool。

版本标记：[PG14+]、[PG16+]、[PG17+]、[PG18] 表示功能首次进入相应主版本。

本章唯一主线：Planner 先估算“会有多少数据”，再选择“怎样处理这些数据”；Executor 把这个选择变成真实工作；EXPLAIN 用来比较估算与实际。高性能关注一次查询做了多少工作，高并发关注这些工作如何相乘，高可用关注它们如何影响 WAL、复制与故障恢复。

---

1. 从一个故障开始

假设订单系统有一条“大客户消费排行”查询：

SELECT c.customer_id,
       sum(o.amount_cents) AS total_amount,
       max(o.created_at)   AS last_order_at
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.tenant_id = $1
  AND c.region = $2
  AND c.tier = $3
  AND o.created_at >= $4
GROUP BY c.customer_id
ORDER BY total_amount DESC
LIMIT 50;

它在测试环境只需 40 ms，但上线后出现三个现象：

1. 普通租户仍然很快，大租户偶尔需要 4 s；
2. 报表并发从 2 提高到 20 后，数据库 CPU、临时文件和连接池等待同时上升；
3. 故障切换后数据库很快恢复连接，但同一查询的 P99 在十几分钟内持续恶化。

这三个现象看似分别属于 SQL 优化、容量治理和高可用，实际上都沿着同一条链路发生：

参数与数据分布
    ↓
Planner 估算行数
    ↓
选择 Scan / Join / Aggregate / Sort / Parallel Path
    ↓
Executor 读取页面、循环调用、分配内存、写临时文件
    ↓
单查询延迟与资源消耗
    ↓
并发资源乘法、连接排队、锁持有时间
    ↓
WAL/复制竞争、故障切换后的冷缓存与 RTO

所以，本章不会把节点类型、参数、并发和高可用拆成互不相关的知识点，而是围绕四个连续问题展开：

1. Planner 当时相信会返回多少行？
2. 它因此选择了什么执行路径？
3. Executor 实际做了多少工作，工作放大在哪里？
4. 这些工作在并发和故障场景下怎样继续放大？

1.1 本章阅读地图

章节	要回答的问题	形成的能力
第 2—3 节	SQL 如何从文本变成计划，Planner 如何决策	建立完整心智模型
第 4—5 节	Executor 如何执行，怎样读 EXPLAIN	从计划中建立证据链
第 6—7 节	各类节点怎样组合，计划为什么失控	从现象定位根因
第 8 节	如何从性能、并发、可用性审视同一计划	完成系统级取舍
第 9—12 节	如何实验、观测、排障并复盘事故	把知识用于生产
第 13—15 节	如何避免反模式并应对面试与练习	完成迁移与检验

1.2 完成本章后应能做到

• 画出 SQL → Query → Path → Plan → PlanState → Tuple 的完整链路；
• 解释 cost、rows、width、actual time、actual rows、loops、Buffers；
• 从叶子节点向上找到最早出现的数量级估算误差；
• 解释为什么有索引仍可能选择 Seq Scan；
• 判断 Nested Loop、Hash Join、Merge Join 的适用条件与风险；
• 识别 Sort、Hash、Aggregate、Materialize、Memoize 的内存与落盘行为；
• 判断问题属于估算错误、参数敏感计划，还是运行环境资源不足；
• 用高性能、高并发、高可用三个维度验证一个优化方案；
• 在 pgx 中低开销采集查询耗时与错误，而不在请求路径自动执行 EXPLAIN ANALYZE；
• 使用本章 Runbook 完成止损、根治、回归与故障切换验证。

---

2. Planner 想象的世界与 Executor 真实的世界

理解执行计划，首先要分清两个世界：

• Planner 的世界由统计信息、成本参数和候选路径组成；其中的行数与成本都是估算值。
• Executor 的世界由真实数据、缓存、锁、内存、并发和 I/O 组成；其中的时间、行数和页面访问才是实际发生的工作。

EXPLAIN 展示 Planner 的判断，EXPLAIN ANALYZE 则把 Executor 的真实结果叠加在同一棵树上。计划分析的本质，就是比较这两个世界在哪里第一次分叉。

2.1 一条 SQL 的生命周期

阶段一：Raw Parser——先确认语法

Parser 只负责把 SQL 文本变成语法结构。此时 orders.amount_cents 只是一个名称引用，还没有确认表、列、函数和操作符是否真实存在。

阶段二：Parse Analysis——让语义确定下来

Parse Analysis 查询系统目录，完成：

• 表、列、函数与操作符解析；
• 数据类型推导和隐式类型转换；
• 聚合、窗口函数与分组规则检查；
• Range Table 和表达式树构建。

输出是语义明确的 Query。列不存在、类型不兼容等错误通常在这里发生。

阶段三：Rewrite——展开视图与规则

Rewriter 对 Query 应用规则系统。视图通常在这里展开为底层关系。Rewrite 负责语义转换，不负责根据成本选择最快路径。

阶段四：Planner——枚举并比较候选路径

Planner 为基表、连接关系和上层结果建立 RelOptInfo，并生成多个 Path：

• Seq、Index、Index Only、Bitmap Scan；
• 不同连接顺序与 Nested Loop、Hash Join、Merge Join；
• Sort、Aggregate、Window、Materialize、Memoize；
• 串行与并行路径。

Planner 不会把所有候选都变成完整执行树，而是先用轻量的 Path 比较成本、排序属性、参数化依赖和并行安全性。选中最佳 Path 后，才生成 Plan。

阶段五：Executor——把计划变成状态机

Executor 为每个 Plan 节点创建对应的 PlanState。父节点需要一行时向子节点请求，子节点继续向下请求，直到 Heap、Index、缓存或临时文件产生 Tuple。这种方式称为 demand-pull。

Limit 需要一行
  → Sort 需要足够的候选
    → Aggregate 需要分组输入
      → Join 需要两侧行
        → Scan 从表或索引读取 Tuple

2.2 把内部对象串成一条链

对象	所处阶段	它回答的问题	与下一对象的关系
Query	解析/重写后	SQL 的语义是什么	Planner 以它为输入
PlannerInfo	规划期	本次规划有哪些全局状态	管理各层关系和目标属性
RelOptInfo	规划期	某个基表或连接关系有哪些候选	保存 rows 与候选 Path
Path	规划期	一种执行方法成本多少、是否有序、是否并行	最优 Path 转换为 Plan
Plan	规划结果	最终要执行哪些节点	Executor 为其创建状态
PlanState	运行期	节点当前执行到哪里、使用多少内存、是否重扫	产生 Tuple 并记录 Instrumentation
TupleTableSlot	运行期	节点之间如何传递一行	被父节点消费

最容易混淆的是：

• Path 是候选方案；
• Plan 是被选中的执行树；
• PlanState 是运行中的节点状态。

EXPLAIN 展示的是 Plan，不会列出所有被淘汰的 Path。

2.3 流式节点与阻塞节点

Demand-pull 并不意味着每个节点都能立即返回数据。

类型	典型节点	行为	对延迟的影响
流式节点	Seq Scan、Index Scan、Nested Loop 的部分形态	获得一行后即可继续向上返回	首行延迟低
半阻塞节点	Hash Join	先构建一侧 Hash，再探测另一侧	有固定启动成本
阻塞节点	Sort、部分 Aggregate、WindowAgg	往往先消费大量或全部输入	首行延迟高，容易使用内存或临时文件

这解释了为什么 LIMIT 10 有时会偏好 Nested Loop：它可能很快产生前 10 行；而 Hash Join 即使完整执行更便宜，也必须先构建 Hash 表。

2.4 贯穿全章的核心变量：Cardinality

Planner 做多数重大决策前都要回答“有多少行”：

• 谓词会留下多少行；
• Join 会形成多少行对；
• Group By 会产生多少组；
• Sort 需要处理多少行、每行多宽；
• 是否值得启动并行 Worker；
• Nested Loop 内侧会执行多少次。

因此，一个叶子节点把 10,000 行估成 10 行，后果并不只是一处数字错误，而可能形成完整的错误链：

过滤结果被低估 1000×
  → Planner 认为外侧很小
  → 选择 Nested Loop
  → 内侧索引扫描从 10 次变成 10000 次
  → 上层 Aggregate 输入暴涨
  → Sort 超过 work_mem 落盘
  → 查询持有连接和 Snapshot 更久
  → 并发时池等待、I/O 队列和复制延迟一起上升

这就是后续读计划时要找“最早数量级误差”，而不是只盯着最上层 Sort 或 Aggregate 的原因。

---

3. Planner 如何做决定：先估行数，再算成本

3.1 选择性与估算行数

对单表过滤，Planner 会根据统计信息估算选择性：

estimated_rows ≈ relation_rows × selectivity(predicate)

例如表有 1,000,000 行，status='refunded' 预计选择性为 1%，则输出约 10,000 行。这个估算会继续成为 Join、Aggregate 和 Sort 的输入。

常见统计来源包括：

• n_distinct：不同值数量；
• MCV：最常见值及频率；
• Histogram：非 MCV 值的大致分布；
• Correlation：列值顺序与物理行顺序的相关程度；
• 扩展统计：多列 dependencies、MCV、ndistinct。

单列统计只知道边际分布，不一定知道列之间的关系。若 region='APAC'、tier='VIP'、channel='MOBILE' 总是一起出现，而 Planner 把它们近似当成独立条件，就可能错误地相乘：

真实联合选择性：10%
独立性估算：10% × 10% × 10% = 0.1%
误差：100×

3.2 Cost 是比较尺，不是毫秒

典型计划节点：

Index Scan using orders_tenant_created_idx on orders
  (cost=0.43..52.81 rows=20 width=48)

含义是：

• 0.43：startup cost，返回第一行前的估算成本；
• 52.81：完整执行该节点的 total cost；
• rows=20：预计每次完整执行向父节点输出 20 行；
• width=48：预计每行平均 48 字节。

Cost 是由页访问、Tuple 处理、表达式计算、排序、Hash、并行启动等成本参数组成的相对单位：

节点成本
≈ 子节点成本
+ 页访问量 × 页成本
+ Tuple 数 × CPU 成本
+ 表达式执行次数 × 操作符成本
+ Sort / Hash / Parallel 额外成本

所以：

• cost=100 不表示 100 ms；
• 上层节点的成本通常已包含子节点，不能简单相加；
• 相同 cost 在冷缓存、热缓存、SSD、网络盘和高并发下可能对应完全不同的实际时间；
• Planner 用 cost 比较候选，真实结论必须由 ANALYZE、Buffers、Wait Event 和系统指标验证。

3.3 Startup Cost 与 Total Cost 为什么都重要

没有 LIMIT 时，Planner 通常更重视完整执行的 total cost；有 LIMIT、游标或 EXISTS 时，能否尽快返回前几行会更重要。

可把 LIMIT 下的路径成本简化理解为：

LIMIT 路径成本
≈ startup_cost
+ 预计消费比例 × (total_cost - startup_cost)

因此：

• Hash Join 可能完整执行成本低，但启动前要先建 Hash；
• Nested Loop + Index Scan 完整执行成本更高，却可能更快产生前 50 行；
• ORDER BY ... LIMIT 50 可能使用 Top-N Sort，而不是完整排序；
• 被 LIMIT 提前终止的子节点，实际行数少于“完整执行估算行数”不一定是估算错误。

3.4 Planner 如何比较主线查询的候选计划

对本章开头的排行查询，Planner 至少可能考虑以下两类思路。

候选 A：小外表驱动 Nested Loop

过滤 customers
  → 每个 customer 通过 orders(customer_id, created_at) 查订单
  → GroupAggregate / HashAggregate
  → Top-N Sort
  → Limit 50

当过滤后只有少量客户、每个客户只有少量订单时，这条路径首行快、读取少。

候选 B：批量扫描后 Hash Join

扫描时间范围内 orders
  → 扫描并过滤 customers
  → Hash Join
  → HashAggregate
  → Top-N Sort
  → Limit 50

当客户和订单都很多时，批量扫描与 Hash 探测通常比成千上万次随机索引回表更稳定。

Planner 的选择不是“Nested Loop 永远快”或“Hash Join 永远快”，而是取决于它对以下数量的估算：

• 过滤后客户数；
• 时间范围内订单数；
• 每个客户平均匹配订单数；
• 分组数量；
• 行宽与缓存命中；
• LIMIT 能否提前停止。

如果第一个数字就错了，后面的比较很可能全部失真。

3.5 Planner 的“最优”有边界

Planner 选择的是：

在当前统计信息、成本参数、配置、参数可见性与候选搜索空间下，估算成本最低的合法路径。

它并不保证：

• 对每个参数值都最优；
• 在任何并发级别都最优；
• 故障切换到另一台机器后仍有相同实际耗时；
• 统计错误时仍能猜中真实最优路径；
• Executor 可以运行一半后随意切换为另一棵计划树。

因此，计划分析不能停在“为什么 Planner 选了它”，还必须继续问“Executor 执行后发生了什么”。

---

4. Executor 如何把估算变成真实工作

Planner 的错误本身不会消耗 CPU 或 I/O；真正产生代价的是 Executor 按错误计划执行时形成的工作量。读 EXPLAIN ANALYZE 时，必须把每个字段翻译成“执行了多少次、处理了多少数据、访问了多少页面”。

4.1 actual time、actual rows 与 loops

示例：

Index Scan ...
(actual time=0.020..0.080 rows=3 loops=10000)

含义是：

• 每次执行平均约 0.020 ms 返回第一行；
• 每次执行平均约 0.080 ms 结束；
• 每次平均输出 3 行；
• 节点共执行 10,000 次。

因此：

累计输出行数 ≈ 3 × 10000 = 30000
粗略累计节点工作时间 ≈ 0.080 ms × 10000 = 800 ms

这里最容易犯的错误，是只看到“0.080 ms 很快”，却忽略它被调用了一万次。

需要注意：

• Actual Time 和 Actual Rows 通常是 per-loop 平均值；
• Rows Removed by Filter、Rows Removed by Join Filter 也通常是每 loop 平均并可能四舍五入；
• 父节点时间通常包含子节点时间，不能机械相减得到精确“独占时间”；
• 并行节点会重叠执行，多个 Worker 的时间不能直接相加成墙钟时间。

4.2 Buffers：页面访问比单次毫秒数更可迁移

使用：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SUMMARY)
SELECT ...;

常见字段：

字段	含义	不能直接推出的结论
shared hit	页面已在 PostgreSQL shared_buffers 中	不表示查询没有 CPU 成本
shared read	PostgreSQL 需要把页面读入 shared_buffers	不一定发生物理磁盘读取，可能命中 OS Page Cache
shared dirtied	查询使共享页面变脏	不等于已经写回磁盘
shared written	查询过程中发生页面写出	仍需结合 Checkpoint、Backend Write 等判断
temp read/written	访问了临时文件块	通常指向 Sort、Hash、tuplestore 等落盘
local	临时表/本地缓冲区访问	与 shared buffer 不同

时间受缓存冷热和机器影响很大，而“读了多少页、循环了多少次、丢弃了多少行”更容易解释计划结构本身的工作放大。

4.3 条件出现在什么位置，决定何时丢弃数据

字段	条件发生的位置	典型意义
Index Cond	索引访问阶段	真正缩小索引扫描范围
Filter	Tuple 已被节点取出之后	先读后过滤，可能造成读放大
Recheck Cond	Bitmap Heap 取得候选 Tuple 后	对 lossy bitmap 或候选结果重新验证
Hash Cond / Merge Cond	Join 算法核心匹配阶段	决定 Hash 或 Merge 如何配对
Join Filter	候选行对形成后	大量丢弃可能说明先形成了过多行对

例如：

Index Scan using orders_customer_idx on orders
  Index Cond: (customer_id = c.customer_id)
  Filter: (created_at >= $4)
  Rows Removed by Filter: 500

这表示索引只按 customer_id 缩小范围，时间条件是在回表后才过滤。若每个客户历史订单很多，Nested Loop 内侧就会反复读取大量旧订单。一个更匹配访问模式的索引可能是：

CREATE INDEX ON orders (customer_id, created_at);

但新增索引也会带来空间、写放大、WAL 和恢复时间成本，必须在第 8 节的三个维度中评估。

4.4 内存节点为什么会落盘

work_mem 不是“一个连接最多使用的总内存”，而是 Sort、Hash、Materialize、Memoize 等单个操作的内存基线。一个查询可以同时或先后拥有多个内存节点，并行 Worker 还可能各自使用额度。

常见信号：

Sort Method: quicksort       Memory: 8192kB
Sort Method: top-N heapsort  Memory: 256kB
Sort Method: external merge  Disk: 524288kB

Hash
  Buckets: 262144  Batches: 8  Memory Usage: 8192kB

解释：

• quicksort：排序留在内存；
• top-N heapsort：LIMIT 场景只维护前 N 项；
• external merge：排序已写临时文件；
• Batches > 1：Hash 数据被分批处理，通常伴随临时 I/O；
• 运行期 Batches 比规划时预期更多，往往说明行数或行宽被低估。

临时文件不仅来自 Sort，还可能来自：

• Hash Join；
• HashAggregate；
• Materialize 与 CTE tuplestore；
• WindowAgg；
• 某些集合操作与递归查询。

4.5 Wait Event：慢是在“做事”还是“等待”

执行计划告诉你查询打算做什么，但不能单独解释它此刻为什么慢。某节点 actual time 很高，可能是：

• CPU 正在执行表达式、Hash、排序；
• 等待数据文件或临时文件 I/O；
• 等锁；
• 等 WAL flush 或同步复制确认；
• 等并行 Worker 或进程调度；
• 被主机 CPU run queue 延迟。

所以分析必须把计划与 pg_stat_activity.wait_event_type、wait_event 以及 OS 指标放在同一时间窗口中。若查询正在等待锁，优先处理 blocker；此时更换 Join 算法通常不是第一修复动作。

4.6 WAL：写计划还会影响复制路径

对 DML 使用：

EXPLAIN (ANALYZE, BUFFERS, WAL, VERBOSE, SUMMARY)
UPDATE ...;

可以观察 WAL records、full page images 和 bytes。写入行数、索引数量、触发器和页面首次修改都会影响 WAL。WAL 越多：

• 主库写入与刷盘负担越高；
• 同步复制提交路径越长；
• 异步副本发送、重放与追赶时间可能增加；
• 备份、归档和故障恢复需要处理的数据更多。

因此，DML 的“性能优化”不能只看主库执行时间，还要看 WAL 与复制。

---

5. 用 EXPLAIN 建立一条完整证据链

5.1 先选择安全的观测级别

一级：只看 Planner，不执行查询主体

EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT ...;

适合生产排查的第一步。它仍会解析、重写、规划并取得必要对象锁，但不会执行查询主体。

[PG17+] 可增加 MEMORY 查看规划阶段内存：

EXPLAIN (VERBOSE, COSTS, SETTINGS, MEMORY)
SELECT ...;

这里的 Memory 不是 Executor 每个节点的实际 work_mem 峰值。

二级：执行并叠加真实数据

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

ANALYZE 会真正执行 SQL。对 SELECT，也可能：

• 调用易变函数；
• 获取锁；
• 触发 I/O；
• 消耗大量 CPU、内存和临时空间。

对 INSERT、UPDATE、DELETE、MERGE，它会真实修改数据、运行触发器并产生 WAL。

三级：降低节点计时开销

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    SUMMARY,
    TIMING OFF
)
SELECT ...;

高 loops、单次极短的节点中，逐节点计时本身可能有明显开销。TIMING OFF 仍保留行数、loops、Buffers 和总执行时间，适合重复测量。

四级：比较 Generic Plan 与参数相关计划

[PG16+]：

EXPLAIN (GENERIC_PLAN, VERBOSE, SETTINGS)
SELECT *
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2;

GENERIC_PLAN 不能与 ANALYZE 同用。它用于判断 Prepared Statement 的通用计划是否忽略了参数偏斜。

DML 的安全边界

BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '500ms';

EXPLAIN (ANALYZE, BUFFERS, WAL, VERBOSE, SUMMARY)
UPDATE accounts
SET balance = balance - 100
WHERE id = 42;

ROLLBACK;

即使 ROLLBACK：

• Sequence 的 nextval() 通常不会回退；
• 触发器已经执行；
• 外部网络、文件或扩展副作用未必可回滚；
• 锁、CPU、I/O 与 WAL 已经真实发生。

生产 DML 最安全的做法仍是脱敏克隆或隔离测试库。

5.2 一份贯穿全章的示例计划

下面的数值是为了说明读法而构造的示例：

Limit  (cost=1680.00..1680.13 rows=50 width=24)
       (actual time=1830.500..1830.540 rows=50 loops=1)
  Buffers: shared hit=182000 read=12000
  ->  Sort  (cost=1680.00..1680.30 rows=120 width=24)
            (actual time=1830.498..1830.520 rows=50 loops=1)
        Sort Key: (sum(o.amount_cents)) DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  HashAggregate  (cost=1670.00..1675.00 rows=120 width=24)
                            (actual time=1822.000..1828.400 rows=8000 loops=1)
              Group Key: c.customer_id
              Batches: 1  Memory Usage: 1169kB
              ->  Nested Loop  (cost=12.00..1650.00 rows=240 width=16)
                                (actual time=0.080..1710.000 rows=240000 loops=1)
                    ->  Bitmap Heap Scan on customers c
                          (cost=8.00..40.00 rows=12 width=8)
                          (actual time=0.050..12.000 rows=8000 loops=1)
                          Recheck Cond: (tenant_id = 42)
                          Filter: ((region = 'APAC') AND (tier = 'VIP'))
                          Rows Removed by Filter: 2000
                    ->  Index Scan using orders_customer_created_idx on orders o
                          (cost=0.43..130.00 rows=20 width=16)
                          (actual time=0.015..0.180 rows=30 loops=8000)
                          Index Cond: ((customer_id = c.customer_id)
                                   AND (created_at >= '2026-06-01'))
                          Buffers: shared hit=181000 read=12000
Planning Time: 1.800 ms
Execution Time: 1831.200 ms

不要先看最上面的 1.8 s，也不要因为 Sort 在根部就认定“排序慢”。按数据流从下向上读：

1. customers 预计 12 行，实际 8,000 行，误差约 667×；
2. 这是计划中最早的数量级误差；
3. Planner 因此外推 orders 内侧只执行约 12 次；
4. 实际 Index Scan 执行 8,000 次，每次平均 30 行；
5. Nested Loop 最终产生 240,000 行，而不是预计 240 行；
6. HashAggregate 实际产生 8,000 组，而不是预计 120 组；
7. Sort 只做 Top-N，内存仅 32 kB，并不是主要问题；
8. 根因应优先从 customers 多列选择性、参数偏斜或统计信息入手。

完整因果链是：

customers 多条件估算错误
  → Nested Loop 被错误认为成本低
  → orders 内侧 loops 放大
  → 页面访问与 CPU 放大
  → Aggregate 输入放大
  → 查询时长和连接占用延长

5.3 顺藤摸瓜七步法

以后面对任何计划，都按同一顺序，避免被最显眼的节点带偏。

第一步：确认 SQL 语义与参数类别

先确认：

• 查询结果语义是否正确；
• LEFT JOIN 是否被 WHERE 条件变成了事实上的 INNER JOIN；
• 谓词中是否存在隐式类型转换、函数包列、不同 Collation；
• 参数属于大租户还是小租户、热时间窗还是冷时间窗；
• 是否包含 LIMIT、ORDER BY、DISTINCT、窗口函数。

错误 SQL 不能通过“优化计划”变正确。

第二步：先看计划形状，不先看时间

从根节点快速回答：

• 最终是 Limit、Sort、Aggregate 还是 ModifyTable；
• Join 的主骨架是什么；
• 是否并行；
• 是否出现阻塞节点。

这一步只建立地图，不立即下结论。

第三步：从叶子向上比较 Estimated Rows 与 Actual Rows

误差倍数可粗略计算为：

error_ratio = max(actual / estimated, estimated / actual)

优先标记 10×、100×、1000× 误差。estimated_rows=0、实际为 0、LIMIT 提前停止等情况需单独解释。

第四步：把 loops 乘回去

对每个高 loops 节点计算：

累计输出 ≈ actual rows × loops
粗略累计工作 ≈ actual total time × loops

尤其关注 Nested Loop 内侧、SubPlan、函数扫描和被反复 rescan 的节点。

第五步：判断数据在哪里被过滤

检查 Index Cond、Filter、Recheck Cond、Join Filter 和 Rows Removed。问题常不是“没有过滤”，而是“过滤发生得太晚”。

第六步：核对 Buffers、内存、临时文件与并行

回答：

• 读取了多少 shared pages；
• 随机回表是否被 loops 放大；
• Sort/Hash 是否 spill；
• Workers Planned 与 Launched 是否一致；
• per-worker 行数是否倾斜。

第七步：回到运行现场

把计划与以下数据对齐到同一时间窗口：

• 应用 P50/P95/P99、连接池 Acquire 等待；
• pg_stat_activity Wait Event 与 blocker；
• CPU、RSS、Swap、IOPS、磁盘延迟；
• temp bytes、WAL rate、Checkpoint；
• 复制发送/刷盘/重放 lag。

计划解释“结构性工作”，现场指标解释“为什么这一次特别慢”。

5.4 读计划时的四个常见误判

1. 根节点最慢，所以根节点是根因：根节点时间通常包含全部子树。
2. 单次 Index Scan 很快，所以可以忽略：高 loops 会把微秒级操作放大成秒。
3. shared read 很多，所以一定是磁盘坏了：可能命中 OS Page Cache，也可能只是读集合太大。
4. Sort 出现在顶层，所以加大 work_mem：先看 Sort Method、Disk、输入行数和上游估算；Top-N Sort 可能几乎没有问题。

---

6. 节点不是知识清单，而是一条数据加工流水线

一棵计划可以统一理解为：

Scan 取得原料
  → Join 组合原料
    → Aggregate / Window 改变结果形状
      → Sort / Gather 建立输出属性
        → Limit / ModifyTable 形成最终结果

下面按这条数据流讲节点，而不是孤立背诵定义。

6.1 Scan：决定最初要触碰多少数据

节点	怎样取数据	何时合理	主要失败信号
Seq Scan	顺序读取 Heap Page 并过滤	返回比例高、表小、无有效索引、顺序读更经济	大量 Rows Removed、shared read 高、并发全表扫
Index Scan	通过 Index Tuple 定位 TID，再访问 Heap	高选择性、需要索引顺序、参数化点查	随机回表多、内侧 loops 高、Filter 发生太晚
Index Only Scan	尽量只从索引返回列	覆盖查询且 Visibility Map 允许	Heap Fetches 高、索引过宽导致写放大
Bitmap Index + Bitmap Heap	先形成 TID/页位图，再按 Heap Page 访问	中等选择性、多个索引条件组合	lossy blocks、Recheck 多、失去索引顺序

为什么有索引仍可能 Seq Scan

若 status='paid' 命中 70% 行：

• Index Scan 要遍历大量索引项；
• 还要回表取金额等未覆盖列；
• Heap 访问可能接近随机；
• 最终仍读取大部分表。

此时一次顺序扫描往往更便宜。索引存在只说明“有一条候选 Path”，不说明它一定成本最低。

Scan 的三维影响

维度	关注点
高性能	选择性、覆盖、物理相关性、返回行宽、Buffers
高并发	多个全表扫会争抢带宽并污染缓存；大量点查会放大随机 I/O
高可用	新增索引提升读性能，但会增加写入、WAL、复制与恢复数据量

6.2 Join：决定输入会不会发生乘法

Nested Loop

工作方式：外侧每输出一行，就执行一次内侧计划。

适合：

• 外侧很小；
• 内侧有高选择性参数化索引；
• 需要低 startup cost；
• 外侧 key 重复时可由 Memoize 受益。

风险：

外侧实际 10000 行，而不是预计 10 行
  → 内侧从 10 loops 变成 10000 loops

它不是算法本身“差”，而是最怕外侧行数低估和内侧单次访问不够便宜。

Hash Join

工作方式：把一侧构建为 Hash 表，另一侧按等值键探测。

适合：

• 两侧输入较大；
• 等值连接；
• 不要求已有顺序。

风险：

• 构建侧行数或行宽低估导致 Hash 分 batch；
• 多并发 Hash 形成内存乘法；
• 大量 temp I/O 与业务数据 I/O 争抢同一磁盘。

Merge Join

工作方式：两侧按连接键有序，像归并一样向前推进。

适合：

• 输入已经有合适索引顺序；
• 大结果需要保序；
• 可流式处理。

风险：

• 缺少顺序时两侧先 Sort；
• 重复键可能需要 Materialize 或回退；
• 为了 Merge 强行给大输入排序，可能比 Hash 更贵。

Semi Join 与 Anti Join

• Semi Join：左侧只要存在一个匹配就输出一次，常对应 EXISTS/IN；
• Anti Join：只输出没有匹配的左行，常对应 NOT EXISTS；
• NOT IN 遇到 NULL 时有三值逻辑，不能随意与 NOT EXISTS 等价改写。

Join 的三维影响

维度	关注点
高性能	外侧行数、内侧访问成本、Hash Batches、排序属性
高并发	Nested Loop 随机 I/O、Hash 内存、Merge Sort 临时文件都会相乘
高可用	长 Join 延长事务与 Snapshot；DML Join 还会增加锁、WAL 和复制压力

6.3 Aggregate、Window 与 Sort：决定结果如何变形

HashAggregate

以 Group Key 维护 Hash 状态，不要求输入有序。核心估算是“会有多少组”。组数或状态宽度低估时可能分 batch、写临时文件。

GroupAggregate

要求输入按 Group Key 有序，可以利用索引顺序或显式 Sort，然后逐组完成。若已有顺序，它通常内存稳定；若需要先排序，成本可能转移到 Sort。

WindowAgg

窗口函数不会像 GROUP BY 那样减少输出行数。它通常依赖 PARTITION BY / ORDER BY 顺序，并可能使用 tuplestore。大分区、宽行和多个不同窗口定义容易放大内存与临时 I/O。

Sort 与 Incremental Sort

• 普通 Sort 对完整输入排序；
• Top-N Sort 只维护前 N 项；
• Incremental Sort 利用输入已按前缀有序，只排序每个前缀组的剩余键。

Sort 本身往往是上游输入过大的受害者。看到 spill 时应先问：

1. 输入行数是否本来就过多；
2. 能否利用索引或已有 pathkeys；
3. 能否更早过滤、预聚合或减少 SELECT 宽度；
4. LIMIT 是否能形成 Top-N；
5. 提高内存后并发预算是否仍安全。

结果塑形节点的三维影响

维度	关注点
高性能	输入行数、组数、行宽、已有顺序、是否 spill
高并发	work_mem × 节点 × Worker × 活跃查询 的乘法
高可用	临时 I/O 会与 WAL 重放、备份和恢复争抢磁盘，即使临时文件本身不写 WAL

6.4 Materialize、Memoize 与 CTE：为什么 Executor 要保存中间结果

Materialize

把子计划输出保存到 tuplestore，供上层反复读取、回退或避免重复计算。它缓存的是一段完整结果流，可能驻留内存，也可能按 work_mem 落盘。

Memoize [PG14+]

常位于参数化 Nested Loop 内侧，以参数值为 key 缓存结果：

Memoize
  Cache Key: c.customer_id
  Hits: 9000  Misses: 1000  Evictions: 0  Overflows: 0

• 外侧 key 重复度高：Hits 多，收益大；
• key 基本唯一：Misses 多，价值小；
• distinct key 估算错误：可能出现 eviction 或错误选型。

Materialize 与 Memoize 的区别：

Materialize：缓存整个子计划结果流
Memoize：按参数 key 缓存多组不同结果

CTE Materialization

对非递归、无副作用的 SELECT CTE：

• 单次引用时通常可折叠进主查询；
• 多次引用时通常物化一次并复用；
• MATERIALIZED 强制形成优化边界；
• NOT MATERIALIZED 允许展开和谓词下推，但可能重复执行昂贵子查询。

选择的核心不是“物化好还是坏”，而是：

复用中间结果的收益，是否大于失去谓词下推和增加内存/临时文件的代价。

6.5 Parallel：把一棵计划拆给多个执行进程

常见节点：

• Parallel Seq Scan、Parallel Index Scan；
• Parallel Hash；
• Parallel Append；
• Gather；
• Gather Merge。

Gather 收集无序结果；Gather Merge 合并各 Worker 的有序流并保持全局顺序。

关键字段：

Workers Planned: 4
Workers Launched: 2

Planned 与 Launched 不一致，说明执行时没有获得足够 Worker。即使全部启动，也要检查：

• per-worker 实际行数是否严重倾斜；
• Leader 是否承担过多汇总工作；
• 每个 Worker 是否重复构建 Sort、Hash 或内侧结果；
• 同时运行的并行查询是否超过 CPU/I/O 容量。

并行的目标通常是缩短单查询墙钟时间，不保证提高系统总吞吐。大量并行报表同时运行，反而可能恶化 OLTP P99。

---

7. 计划失控的三条主因

所有复杂计划问题大多可以先归入三类。分类的价值在于避免用错误手段解决错误问题。

7.1 第一类：Cardinality Estimation 错误

常见来源

• ANALYZE 过期，数据量或分布已变化；
• 多列高度相关，但只有单列统计；
• 数据严重偏斜，参数落在长尾；
• 表达式、函数、类型转换使统计难以利用；
• Join 两侧存在跨表相关性；
• 分区间分布不同；
• 组数、行宽或 distinct 值被错误估算。

典型证据链

叶子 Scan rows 估算错
  → Join 顺序或算法错
  → loops 或 Hash 构建规模错
  → Aggregate 组数错
  → Sort/Hash spill

修复顺序

1. 先确认统计时间与采样是否合理；
2. 增加合适的单列统计目标；
3. 对相关列建立 dependencies/MCV/ndistinct 扩展统计；
4. 消除妨碍统计与索引利用的表达式或隐式转换；
5. 改写 SQL，使谓词更早、更直接地作用于基表；
6. 必要时调整数据模型、分区或预聚合。

enable_nestloop=off、enable_seqscan=off 只能用于反事实比较，不能替代修复估算。

7.2 第二类：参数敏感与 Generic Plan

Prepared Statement 可能使用：

• Custom Plan：根据当前参数值规划；
• Generic Plan：不针对当前参数值，复用统一计划。

多租户系统最典型：

99% 租户：几百行
1% 大租户：几千万行

一个对普通租户优秀的 Index Scan + Nested Loop，对大租户可能产生数十万 loops；而大租户更适合 Bitmap/Seq Scan + Hash Join。Generic Plan 试图代表“平均情况”，但平均计划未必适合重尾分布。

诊断

• 按租户规模、时间窗、状态值等低基数类别统计 P95/P99；
• 比较异常参数的 Custom Plan；
• [PG16+] 查看 EXPLAIN (GENERIC_PLAN)；
• 观察问题是否在执行若干次后才出现；
• 不要只看 pg_stat_statements 的归一化均值。

修复

• 为大/小参数建立稳定 SQL 变体或路由；
• 补充统计和索引；
• 极窄范围评估 Custom Plan；
• 极端租户升级为分区、独立表或物理隔离；
• 避免每个租户生成不同 SQL 文本，防止计划缓存和监控维度爆炸。

7.3 第三类：运行环境与计划假设不匹配

即使 Estimated Rows 很准、计划形状合理，执行仍可能慢，因为 Planner 的成本模型无法完整描述运行时排队与资源竞争。

内存不足或内存过量

单查询提高 work_mem
  → spill 减少
  → 单次查询更快

但在并发下：

峰值内存风险
≈ 活跃查询数
× 同时活跃的内存节点数
× 并行参与进程数
× 单节点额度

全局提高 work_mem 可能把“临时文件慢”变成“Swap/OOM 更慢”。

Worker 不足

规划时预计 4 个 Worker，执行时可能一个也拿不到。单查询因此退化，而并发并行查询还可能争抢有限 Worker 池。

缓存冷热变化

同一 Plan 在热缓存时主要 shared hit，故障切换后新 Primary 可能需要大量读入页面。计划未变，但实际时间明显上升。

Primary 与 Replica 环境不同

物理副本数据相近，但以下条件仍可能不同：

• 本地 GUC；
• 可用并行 Worker；
• 缓存与存储延迟；
• WAL 重放竞争；
• 恢复冲突与查询取消；
• 查询参数与连接会话状态。

所以“副本能执行相同 SQL”不等于“副本具有相同性能”。

7.4 三类问题的快速区分

现象	首要怀疑	关键证据
Estimated 与 Actual 相差 100×	估算错误	最早误差节点、统计信息
同 SQL 仅少数参数极慢	参数敏感计划	参数分桶、Custom/Generic 对比
行数估算准确但高并发才慢	资源竞争	Wait Event、CPU、I/O、RSS、池等待
故障切换后计划相同却变慢	缓存/环境变化	Buffers、I/O、Worker、恢复竞争
Sort/Hash spill 且输入远超估算	估算错误导致资源失配	上游 rows 误差 + temp blocks
Sort/Hash spill 但估算准确	单查询内存或工作负载设计	work_mem、行宽、并发预算

---

8. 用同一条因果链审视高性能、高并发与高可用

三个维度不是三个独立章节，而是对同一个执行计划提出三层问题：

高性能：一次执行做多少工作？
高并发：这些工作同时发生多少份？
高可用：这些工作是否拖累复制、切换与恢复？

8.1 高性能：减少“被触碰但没有形成结果”的工作

高性能的核心不是强制某个节点名称，而是减少无效工作。

重点指标

观察对象	放大信号	优化方向
Scan	扫描千万行只返回百行	更早过滤、合适索引、分区裁剪
Nested Loop	内侧 loops 数十万	修复外侧估算、批量 Join、Memoize
Filter	Rows Removed 很高	让条件进入 Index Cond 或更早节点
Join Filter	形成大量行对后丢弃	改连接条件、顺序或数据模型
Sort/Hash	external merge、Batches > 1	减输入、减行宽、利用顺序、受控内存
Index Only	Heap Fetches 很高	Vacuum/可见性、评估覆盖索引
Parallel	Worker 倾斜、Leader 瓶颈	改分布、降低传输、重新评估并行

三种放大

• 读放大：读取大量 Page/Tuple，最终只返回少量结果；
• 写放大：为加速读取增加多个索引，使每次 DML 写更多 Index 与 WAL；
• 空间放大：覆盖索引、Bloat、临时文件和物化结果占用额外空间。

一个优化方案若只降低读放大，却让写放大和空间放大不可接受，就不是完整方案。

8.2 高并发：把“单查询成本”改写成“系统资源乘法”

首先区分几个常被混用的概念：

名称	含义
goroutine 并发	同时运行或等待的应用任务数
连接数	已建立的 PostgreSQL 会话数
活跃查询数	当前真正执行或等待资源的 SQL 数
数据库并发	后台进程、并行 Worker 同时竞争资源的程度
TPS	单位时间完成的事务数
排队请求数	等应用 Semaphore、连接池 Acquire 或数据库锁的请求数

单连接 50 ms 的查询，在 100 并发下不一定仍是 50 ms。主要原因：

• 多个 Sort/Hash 形成内存乘法；
• 多个 Seq Scan 争抢带宽并污染缓存；
• 多个 Index Scan 放大随机 I/O；
• Parallel Query 争抢 CPU、Worker 与 Leader；
• 慢查询延长连接、Snapshot 和锁持有时间；
• 超时后的无上限重试形成重试风暴。

合理的请求路径是：

请求到达
  → 应用有界队列/并发门
  → pgxpool Acquire（有超时）
  → PostgreSQL 执行
  → 取消与截止时间向下传播

连接池是最后一道边界，不应承担无限队列。OLTP、报表和批任务应使用不同角色、连接池、并发额度和 per-role GUC。

8.3 高可用：把查询性能纳入 RTO

Planner/Executor 不负责选主和 Fencing，但查询计划会显著影响 HA 结果。

HA 主题	执行计划的影响
RPO	由复制策略决定；DML 计划通过 WAL 量影响复制路径负担
RTO	坏计划会占满 CPU/I/O，拖慢恢复、健康检查和流量接管
同步复制	WAL 越多，发送、刷盘、确认路径越重
异步复制	大查询与 WAL replay 争抢 CPU/I/O，lag 可能扩大
故障切换	新 Primary 冷缓存、会话重建、Prepared Plan 重建导致 P99 波动
只读副本	长查询可能与恢复冲突并被取消；副本不是免费算力
备份/PITR	大扫描和临时 I/O 会与备份、恢复争抢磁盘
Failback	必须验证配置、计划、缓存和性能，而非只验证连接

故障切换完成的标准不应只是“端口可连接”，而应包含：

• 核心 SQL 的 P95/P99；
• 关键参数类别的计划与 Buffers；
• Worker 启动率；
• WAL/复制追赶；
• 连接池重连与错误处理；
• 冷缓存阶段是否有流量保护和预热策略。

8.4 三维影响矩阵

设计动作	高性能收益	高并发风险	高可用影响
新增覆盖索引	减少回表、改善点查	写入热点与维护成本增加	WAL、复制和恢复数据量增加
提高 work_mem	减少单查询 spill	节点 × Worker × 并发导致 OOM	内存压力可能拖慢恢复与守护进程
增加并行度	缩短单次大查询	抢占 CPU/I/O，系统吞吐可能下降	副本 replay 与故障恢复资源受挤压
将报表移到副本	保护 Primary	报表仍需并发门	可能增加 lag、恢复冲突和读时效问题
创建扩展统计	改善行数估算	ANALYZE 有额外成本	一般不改变 RPO，但切换后需确认统计一致
强制 Custom Plan	适应参数偏斜	增加规划 CPU	新会话/切换后行为需重新验证
分区/物理隔离	减少扫描、隔离热点	路由和运维复杂	备份、复制、切换拓扑更复杂

8.5 一个优化方案的完整验收条件

不能只说“执行时间从 2 s 降到 200 ms”。至少要验证：

1. 单查询：Actual Rows、loops、Buffers、temp、WAL 是否下降；
2. 参数覆盖：小、中、大参数是否都正确；
3. 目标并发：吞吐、P95/P99、RSS、I/O 队列、池等待是否可控；
4. 写入代价：索引维护、WAL、Vacuum 是否仍在预算内；
5. Primary/Replica：计划与实际表现是否可接受；
6. 故障切换：冷缓存和连接重建阶段是否仍满足恢复目标；
7. 回滚：变更失败时能否快速撤销或路由到安全路径。

---

9. 三个实验：按“假设—计划—执行—系统后果”验证

三个实验分别验证本章主线中的三个关键环节：

1. Planner 并不是“见索引就用索引”；
2. 一个叶子估算错误怎样传播成 Join 与 loops 放大；
3. 单查询减少 spill 的做法，为什么到了高并发可能变成内存事故。

所有实验应在专用测试库执行。数值只用于制造可观察的数据分布，不是性能基准。实验结论应以自己的版本、硬件、缓存状态和并发测量为准。

9.1 实验一：索引存在，Seq Scan 仍然可能是正确计划

9.1.1 要验证的因果链

不同参数值具有不同选择性
  → Planner 估算返回比例不同
  → 高命中值偏向 Seq Scan
  → 稀有值偏向 Index/Bitmap Scan

本实验不是证明“Seq Scan 快”，而是证明：访问路径由预计工作量决定，不由索引是否存在决定。

9.1.2 准备数据

DROP SCHEMA IF EXISTS ch06_e1 CASCADE;
CREATE SCHEMA ch06_e1;

CREATE TABLE ch06_e1.orders (
    order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status     text           NOT NULL,
    amount     numeric(12, 2) NOT NULL,
    created_at timestamptz    NOT NULL,
    payload    text           NOT NULL
);

INSERT INTO ch06_e1.orders(status, amount, created_at, payload)
SELECT CASE
           WHEN g % 100 < 70 THEN 'paid'       -- 70%
           WHEN g % 100 < 99 THEN 'shipped'    -- 29%
           ELSE 'refunded'                     -- 1%
       END,
       (g % 100000)::numeric / 100,
       timestamptz '2026-01-01 00:00:00+00'
           - (g % 365) * interval '1 day',
       repeat(md5(g::text), 2)
FROM generate_series(1, 1000000) AS g;

CREATE INDEX orders_status_idx
    ON ch06_e1.orders(status);

ANALYZE ch06_e1.orders;

记录规模：

SELECT count(*) AS rows,
       pg_size_pretty(pg_relation_size('ch06_e1.orders')) AS heap_size,
       pg_size_pretty(pg_relation_size('ch06_e1.orders_status_idx')) AS index_size,
       pg_size_pretty(pg_total_relation_size('ch06_e1.orders')) AS total_size
FROM ch06_e1.orders;

9.1.3 先只看 Planner 的判断

EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'paid';

EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'refunded';

预期：

• paid 命中约 70%，Seq Scan 常更便宜；
• refunded 命中约 1%，Index Scan 或 Bitmap Scan 常更便宜。

节点名称受版本、硬件和 GUC 影响，实验成功标准不是必须得到某个固定文本，而是两个参数的估算行数与候选成本明显不同。

9.1.4 再看 Executor 实际做了多少工作

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, SUMMARY)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'paid';

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, SUMMARY)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'refunded';

按以下顺序比较：

1. Estimated Rows 与 Actual Rows 是否接近；
2. 两个查询分别访问多少 Heap Page；
3. 高命中值是否读取表的大部分页面；
4. 稀有值是否显著缩小读集合；
5. 冷缓存与预热后的时间是否不同，但 Buffers 结构仍能解释工作量。

9.1.5 反事实计划只能用于诊断

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (VERBOSE, COSTS, SETTINGS)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'paid';

ROLLBACK;

这里仅观察“如果劝阻 Seq Scan，Planner 会考虑什么”。不要为了证明索引路线更差，就在生产大表上运行强制索引的 EXPLAIN ANALYZE。

9.1.6 查看 Planner 使用的统计

SELECT attname,
       n_distinct,
       most_common_vals,
       most_common_freqs,
       correlation
FROM pg_stats
WHERE schemaname = 'ch06_e1'
  AND tablename = 'orders'
  AND attname = 'status';

MCV 频率解释了为什么 Planner 知道 paid 和 refunded 不是同一种参数。

9.1.7 用三个维度解释结论

维度	结论
高性能	高命中值顺序读全表可能比大量索引回表更经济
高并发	多个高命中全表扫会争抢带宽，仍需限流或分区，而不是强制索引
高可用	为所有状态盲目增加覆盖索引会增加写入、WAL、复制和恢复成本

9.1.8 清理

DROP SCHEMA ch06_e1 CASCADE;

---

9.2 实验二：相关列估算错误如何放大 Nested Loop

9.2.1 要验证的因果链

四个列高度相关
  → 单列统计按独立性近似相乘
  → customers 被低估约 1000×
  → Planner 认为 Nested Loop 内侧只执行少量次数
  → 实际 loops 与页面访问暴涨
  → 扩展统计修复最早误差，Join 成本重新计算

9.2.2 准备数据

DROP SCHEMA IF EXISTS ch06_e2 CASCADE;
CREATE SCHEMA ch06_e2;

CREATE TABLE ch06_e2.customers (
    customer_id integer PRIMARY KEY,
    region      text NOT NULL,
    tier        text NOT NULL,
    channel     text NOT NULL,
    risk_band   text NOT NULL
);

-- 每 10 个客户中有 1 个同时满足四个特殊值。
INSERT INTO ch06_e2.customers
SELECT g,
       CASE WHEN g % 10 = 0 THEN 'APAC'   ELSE 'EMEA'    END,
       CASE WHEN g % 10 = 0 THEN 'VIP'    ELSE 'REGULAR' END,
       CASE WHEN g % 10 = 0 THEN 'MOBILE' ELSE 'WEB'     END,
       CASE WHEN g % 10 = 0 THEN 'HIGH'   ELSE 'LOW'     END
FROM generate_series(1, 100000) AS g;

CREATE TABLE ch06_e2.events (
    event_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    amount      integer NOT NULL,
    payload     text NOT NULL
);

-- 每个客户约 30 行，并打乱 Heap 中 customer_id 的局部性。
INSERT INTO ch06_e2.events(customer_id, amount, payload)
SELECT (((g::bigint * 7919) % 100000) + 1)::integer,
       (g % 1000)::integer,
       repeat(md5(g::text), 2)
FROM generate_series(1, 3000000) AS g;

CREATE INDEX events_customer_idx
    ON ch06_e2.events(customer_id);

ANALYZE ch06_e2.customers;
ANALYZE ch06_e2.events;

目标 SQL：

SELECT sum(e.amount + octet_length(e.payload))
FROM ch06_e2.customers AS c
JOIN ch06_e2.events AS e
  ON e.customer_id = c.customer_id
WHERE c.region = 'APAC'
  AND c.tier = 'VIP'
  AND c.channel = 'MOBILE'
  AND c.risk_band = 'HIGH';

实际匹配约 10,000 个客户。若把四个 10% 条件独立相乘，估算可能接近：

100000 × 0.1⁴ = 10 个客户

9.2.3 第一次执行：没有扩展统计

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT sum(e.amount + octet_length(e.payload))
FROM ch06_e2.customers AS c
JOIN ch06_e2.events AS e
  ON e.customer_id = c.customer_id
WHERE c.region = 'APAC'
  AND c.tier = 'VIP'
  AND c.channel = 'MOBILE'
  AND c.risk_band = 'HIGH';

不要先记录总时间，先填写下面的证据表：

证据	观察值
customers Estimated Rows
customers Actual Rows
最早误差倍数
Join 节点
events 内侧 loops
events 累计输出约 rows × loops
shared hit/read
temp blocks

若 Planner 选择 Nested Loop，重点观察 events 索引节点的 loops 是否接近实际客户数。

9.2.4 创建扩展统计

CREATE STATISTICS ch06_e2.customers_corr_stats
    (dependencies, mcv)
ON region, tier, channel, risk_band
FROM ch06_e2.customers;

ANALYZE ch06_e2.customers;

查看统计：

SELECT statistics_name,
       attnames,
       kinds,
       dependencies,
       most_common_vals,
       most_common_freqs
FROM pg_stats_ext
WHERE statistics_schemaname = 'ch06_e2'
  AND tablename = 'customers';

9.2.5 第二次执行：比较最早误差是否收敛

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT sum(e.amount + octet_length(e.payload))
FROM ch06_e2.customers AS c
JOIN ch06_e2.events AS e
  ON e.customer_id = c.customer_id
WHERE c.region = 'APAC'
  AND c.tier = 'VIP'
  AND c.channel = 'MOBILE'
  AND c.risk_band = 'HIGH';

成功标准依次是：

1. customers 的 Estimated Rows 接近真实数量级；
2. 上层 Join 与 Aggregate 的估算随之改善；
3. Planner 重新比较 Nested、Hash、Merge 的成本；
4. 实际 loops、Buffers 或总资源消耗得到改善。

Planner 修复估算后仍可能选择 Nested Loop，这并不表示实验失败。是否切换 Join 取决于数据规模、缓存、成本参数和硬件。

9.2.6 可选反事实：只用于比较替代 Join

BEGIN;
SET LOCAL enable_nestloop = off;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, SUMMARY)
SELECT sum(e.amount + octet_length(e.payload))
FROM ch06_e2.customers AS c
JOIN ch06_e2.events AS e
  ON e.customer_id = c.customer_id
WHERE c.region = 'APAC'
  AND c.tier = 'VIP'
  AND c.channel = 'MOBILE'
  AND c.risk_band = 'HIGH';

ROLLBACK;

这只能回答“替代计划在当前实验环境中表现怎样”，不能把 enable_nestloop=off 当生产修复。

9.2.7 用三个维度解释结论

维度	结论
高性能	真正修复点是叶子选择性估算，而不是顶层 Aggregate
高并发	单查询多出一万次随机回表，乘以并发后会迅速形成 I/O 和连接占用问题
高可用	长查询延长 Snapshot，争抢副本 replay 的 CPU/I/O；DML 场景还会扩大锁与 WAL 风险

9.2.8 清理

DROP SCHEMA ch06_e2 CASCADE;

---

9.3 实验三：Sort 落盘与 work_mem 的并发悖论

9.3.1 要验证的因果链

宽行全量排序
  → 低 work_mem 下 external merge
  → 单查询提高 work_mem 可减少临时 I/O
  → 但并发查询、多个 Sort、并行 Worker 会把内存相乘

9.3.2 准备数据

DROP SCHEMA IF EXISTS ch06_e3 CASCADE;
CREATE SCHEMA ch06_e3;

CREATE TABLE ch06_e3.wide_events (
    event_id   bigint PRIMARY KEY,
    group_id   integer NOT NULL,
    created_at timestamptz NOT NULL,
    payload    text NOT NULL
);

INSERT INTO ch06_e3.wide_events
SELECT g,
       (g % 100000)::integer,
       timestamptz '2026-01-01 00:00:00+00'
           - (g % 1000000) * interval '1 second',
       repeat(md5((g * 17)::text), 4)
FROM generate_series(1, 500000) AS g;

ANALYZE ch06_e3.wide_events;

9.3.3 记录数据库级临时文件基线

SELECT datname,
       temp_files,
       temp_bytes,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();

这是数据库级累计值，可能混入其他会话。实验环境应尽量隔离，并在前后取差分。

9.3.4 第一轮：极低 work_mem

BEGIN;
SET LOCAL work_mem = '64kB';

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT event_id, group_id, created_at, payload
FROM ch06_e3.wide_events
ORDER BY payload, created_at, event_id;

ROLLBACK;

预期观察：

Sort Method: external merge  Disk: ...
Buffers: shared ..., temp read=..., written=...

9.3.5 第二轮：事务级提高 work_mem

BEGIN;
SET LOCAL work_mem = '256MB';

EXPLAIN (
    ANALYZE,
    BUFFERS,
    SETTINGS,
    VERBOSE,
    SUMMARY
)
SELECT event_id, group_id, created_at, payload
FROM ch06_e3.wide_events
ORDER BY payload, created_at, event_id;

ROLLBACK;

若仍落盘，说明实际排序需求更大。不要为了得到 quicksort 而无限提高内存；同时记录进程 RSS 和系统可用内存。

9.3.6 对比表

项目	64 kB	较高 work_mem
Sort Method
Memory / Disk
temp read/written
shared hit/read
Execution Time
Backend RSS
磁盘吞吐/延迟

9.3.7 把单查询结论扩展到并发

假设生产配置：

• 40 个报表同时运行；
• 每个查询有 2 个大型 Sort；
• 每个查询启动 3 个 Worker 加 1 个 Leader；
• 每个 Sort 的内存额度为 256 MB。

只做上界估算：

40 × 2 × 4 × 256 MB = 81920 MB

实际节点不一定同时达到上限，但这个数量级足以说明：全局把 work_mem 调成 256 MB 极其危险。

9.3.8 正确治理顺序

1. 减少输入行数和 SELECT 宽度；
2. 利用索引顺序、Incremental Sort 或 Top-N；
3. 预聚合或拆分报表；
4. 为报表使用独立角色和事务级 SET LOCAL；
5. 建立有界队列和并发上限；
6. 监控 temp bytes/s、RSS、Swap、磁盘空间与延迟；
7. 最后才确定每类工作负载的内存额度。

9.3.9 用三个维度解释结论

维度	结论
高性能	提高单节点内存可能减少 external merge 和临时 I/O
高并发	内存额度会按节点、Worker 和活跃查询相乘，必须配合 Admission Control
高可用	临时 I/O 与 WAL replay、备份、恢复争抢磁盘；OOM 还可能直接破坏服务恢复

9.3.10 清理

DROP SCHEMA ch06_e3 CASCADE;

9.4 实验记录模板

任何计划实验都至少记录：

项目	记录值
PostgreSQL 完整版本 / OS / 文件系统
关键 GUC、扩展、角色设置
表/索引大小、行数、平均行宽
参数类别与数据分布
缓存状态：首次/预热后/不可控
客户端并发、活跃查询、池大小
预热轮次、测试时长、采样轮次
P50 / P95 / P99 / 吞吐
Plan、Estimated/Actual、loops
Buffers / temp / WAL
CPU / RSS / IOPS / 延迟
Wait Event / Worker
Primary / Replica / 节点角色

---

10. 从应用到数据库：用 pgx 建立低开销观测闭环

EXPLAIN 适合解释一次查询，但生产系统首先要回答“哪一类查询、哪一类参数、从什么时候开始变慢”。应用侧观测负责发现异常，数据库侧证据负责解释异常，两者不能互相替代。

完整闭环应当是：

pgx 请求路径记录稳定 query_name、耗时、错误、返回/影响行数
  → 指标系统按查询名和参数类别计算 P50/P95/P99
  → pg_stat_statements / Wait Event 确认数据库侧资源与等待
  → 保存异常参数的脱敏类别、版本、GUC、Schema 与数据时间点
  → 先 Plain EXPLAIN
  → 在受控环境执行 EXPLAIN ANALYZE
  → 用三个维度回归并灰度发布

10.1 Tracer 的边界

Tracer 位于每次查询的公共路径，必须轻量、低基数且无副作用。

应该记录：

• orders.list_recent 这类稳定业务查询名；
• duration；
• SQLSTATE 和错误类型；
• CommandTag 与 RowsAffected；
• 慢查询是否超过阈值；
• 与应用 Span、实例和版本的关联信息。

默认不应记录：

• 完整参数；
• Token、密码、个人信息；
• 把 tenant_id、user_id 拼进标签；
• 每次查询的完整 SQL 文本；
• 在 Tracer 回调中再次访问数据库。

尤其禁止“发现慢查询后自动执行 EXPLAIN ANALYZE”，因为它会再次执行 SQL，写语句甚至可能二次修改数据。

10.2 可编译示例：查询观测与有界并发

package main

import (
    "context"
    "errors"
    "fmt"
    "log/slog"
    "os"
    "os/signal"
    "sync"
    "syscall"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgconn"
    "github.com/jackc/pgx/v5/pgxpool"
)

type queryNameKey struct{}
type traceStateKey struct{}

type traceState struct {
    startedAt time.Time
    queryName string
}

// WithQueryName attaches a stable, low-cardinality operation name.
// Do not use raw SQL or parameter values as the name.
func WithQueryName(ctx context.Context, name string) context.Context {
    return context.WithValue(ctx, queryNameKey{}, name)
}

type QueryTracer struct {
    logger        *slog.Logger
    slowThreshold time.Duration
}

func (t *QueryTracer) TraceQueryStart(
    ctx context.Context,
    _ *pgx.Conn,
    _ pgx.TraceQueryStartData,
) context.Context {
    name, _ := ctx.Value(queryNameKey{}).(string)
    if name == "" {
        name = "unnamed"
    }

    return context.WithValue(ctx, traceStateKey{}, traceState{
        startedAt: time.Now(),
        queryName: name,
    })
}

func (t *QueryTracer) TraceQueryEnd(
    ctx context.Context,
    _ *pgx.Conn,
    data pgx.TraceQueryEndData,
) {
    state, ok := ctx.Value(traceStateKey{}).(traceState)
    if !ok {
        return
    }

    duration := time.Since(state.startedAt)
    sqlState := ""
    errorClass := ""

    if data.Err != nil {
        errorClass = fmt.Sprintf("%T", data.Err)
        var pgErr *pgconn.PgError
        if errors.As(data.Err, &pgErr) {
            sqlState = pgErr.Code
        }
    }

    attrs := []any{
        "query_name", state.queryName,
        "duration_ms", float64(duration.Microseconds()) / 1000,
        "command_tag", data.CommandTag.String(),
        "rows", data.CommandTag.RowsAffected(),
        "sqlstate", sqlState,
        "error_class", errorClass,
    }

    switch {
    case data.Err != nil:
        t.logger.ErrorContext(ctx, "db.query", attrs...)
    case duration >= t.slowThreshold:
        t.logger.WarnContext(ctx, "db.query", attrs...)
    default:
        t.logger.InfoContext(ctx, "db.query", attrs...)
    }
}

type Order struct {
    ID        int64
    Amount    int64
    CreatedAt time.Time
}

func listRecentOrders(
    ctx context.Context,
    pool *pgxpool.Pool,
    tenantID int64,
    limit int,
) ([]Order, error) {
    if limit < 1 || limit > 1000 {
        return nil, fmt.Errorf("limit out of range")
    }

    queryCtx, cancel := context.WithTimeout(ctx, 2*time.Second)
    defer cancel()
    queryCtx = WithQueryName(queryCtx, "orders.list_recent")

    rows, err := pool.Query(queryCtx, `
        SELECT order_id, amount_cents, created_at
        FROM orders
        WHERE tenant_id = $1
        ORDER BY created_at DESC
        LIMIT $2
    `, tenantID, limit)
    if err != nil {
        return nil, fmt.Errorf("query recent orders: %w", err)
    }
    defer rows.Close()

    orders := make([]Order, 0, limit)
    for rows.Next() {
        var order Order
        if err := rows.Scan(
            &order.ID,
            &order.Amount,
            &order.CreatedAt,
        ); err != nil {
            return nil, fmt.Errorf("scan recent order: %w", err)
        }
        orders = append(orders, order)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("iterate recent orders: %w", err)
    }

    return orders, nil
}

// runBounded is an application-level admission-control example.
// maxWorkers and pgxpool MaxConns are independent capacity limits.
func runBounded(
    parent context.Context,
    pool *pgxpool.Pool,
    tenantIDs []int64,
    maxWorkers int,
) error {
    if maxWorkers < 1 {
        return fmt.Errorf("maxWorkers must be positive")
    }

    ctx, cancel := context.WithCancel(parent)
    defer cancel()

    jobs := make(chan int64)
    errCh := make(chan error, 1)
    var wg sync.WaitGroup

    worker := func() {
        defer wg.Done()

        for {
            select {
            case <-ctx.Done():
                return
            case tenantID, ok := <-jobs:
                if !ok {
                    return
                }
                if _, err := listRecentOrders(ctx, pool, tenantID, 100); err != nil {
                    select {
                    case errCh <- err:
                    default:
                    }
                    cancel()
                    return
                }
            }
        }
    }

    for i := 0; i < maxWorkers; i++ {
        wg.Add(1)
        go worker()
    }

sendLoop:
    for _, tenantID := range tenantIDs {
        select {
        case <-ctx.Done():
            break sendLoop
        case jobs <- tenantID:
        }
    }

    close(jobs)
    wg.Wait()

    select {
    case err := <-errCh:
        return err
    default:
    }
    return parent.Err()
}

func run(logger *slog.Logger) error {
    rootCtx, stop := signal.NotifyContext(
        context.Background(),
        os.Interrupt,
        syscall.SIGTERM,
    )
    defer stop()

    databaseURL := os.Getenv("DATABASE_URL")
    if databaseURL == "" {
        return fmt.Errorf("DATABASE_URL is required")
    }

    config, err := pgxpool.ParseConfig(databaseURL)
    if err != nil {
        return fmt.Errorf("parse database config: %w", err)
    }

    // Example only. Derive from database capacity and all service pools.
    config.MaxConns = 16
    config.ConnConfig.Tracer = &QueryTracer{
        logger:        logger,
        slowThreshold: 250 * time.Millisecond,
    }

    pool, err := pgxpool.NewWithConfig(rootCtx, config)
    if err != nil {
        return fmt.Errorf("create database pool: %w", err)
    }
    defer pool.Close()

    pingCtx, cancel := context.WithTimeout(rootCtx, 5*time.Second)
    err = pool.Ping(pingCtx)
    cancel()
    if err != nil {
        return fmt.Errorf("ping database: %w", err)
    }

    tenantIDs := []int64{101, 202, 303, 404}
    if err := runBounded(rootCtx, pool, tenantIDs, 4); err != nil &&
        !errors.Is(err, context.Canceled) {
        return fmt.Errorf("database work failed: %w", err)
    }

    return nil
}

func main() {
    logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))
    if err := run(logger); err != nil {
        logger.Error("service stopped", "error", err)
        os.Exit(1)
    }
}

10.3 代码与本章主线的对应关系

代码设计	对应的问题
稳定 query_name	找出哪一类 SQL 的尾延迟异常
Context timeout	避免请求无限占用连接和数据库资源
RowsAffected	观察返回/影响行数量级，但不能替代 EXPLAIN Actual Rows
SQLSTATE	区分超时、锁、约束、序列化失败等错误类别
defer rows.Close() + rows.Err()	确保结果完整消费或正确关闭，及时归还连接
maxWorkers	应用级并发门，避免 goroutine 直接打满连接池
MaxConns	池容量边界，不等于数据库应承受的活跃查询数
信号传播	停机时取消等待连接和执行中的查询

pgx.Conn 不适合多个 goroutine 并发使用，服务端并发应通过 pgxpool 管理。池上限也不能单独决定吞吐；还需综合事务时长、其他服务连接、数据库 CPU/I/O、并行 Worker 与 SLO。

10.4 为什么慢查询日志不能替代 Histogram

只记录超过 500 ms 的查询，无法回答：

• P95 是 100 ms 还是 490 ms；
• 延迟是逐渐恶化还是突然跳变；
• 大租户是否与普通租户分布不同；
• 发布前后尾部是否回归；
• 连接池等待是否已经占据大部分客户端耗时。

生产应对所有查询记录低开销 Histogram/Counter，只对错误和慢样本采样日志。标签必须保持低基数，例如：

query_name=orders.list_recent
parameter_bucket=tenant_large
node_role=primary
result=success|timeout|lock_error|other

不要把 tenant_id 或完整 SQL 直接用作指标标签。

10.5 为什么请求路径不能自动执行 EXPLAIN ANALYZE

假设 Tracer 发现原 SQL 超过 500 ms 后立即重跑：

• 原查询被执行第二次；
• DML 可能二次修改；
• 第二次缓存、锁、参数环境已变化，未必代表第一次；
• 数据库负载至少增加一份；
• 大查询可能再次产生 temp、WAL 或复制延迟；
• 参数采集还可能泄露敏感信息。

正确做法是：请求路径只留下足以复现的标识与类别，计划采集在受控环境完成。

---

11. 生产排障 Runbook：从 P99 现象走到最早根因

Runbook 不是“先 EXPLAIN 再说”，而是按延迟发生的位置逐层缩小范围。

11.1 第一阶段：先确认延迟发生在哪一层

客户端总耗时可拆成：

总耗时
= 应用排队
+ 连接池 Acquire
+ 数据库锁/资源等待
+ Planner/Executor
+ 结果序列化与网络
+ 客户端消费

先确认：

• 异常影响 P50、P95 还是 P99；
• 是单租户、单参数、单实例还是全局；
• 数据库 active query 是否同步上升；
• pool Acquire 是否先于数据库执行时间上升；
• 是否有发布、ANALYZE、Schema、索引、配置或故障切换；
• 当前节点是 Primary 还是 Replica。

若数据库执行只有 50 ms，而连接池等待 2 s，优先处理长事务、池泄漏、准入和容量，而不是继续微调执行计划。

11.2 第二阶段：在同一时间窗口收集四组证据

层次	关键指标
应用	请求率、错误率、P50/P95/P99、重试、队列、pool Acquire
PostgreSQL	active sessions、Wait Event、TPS、Buffers、temp、WAL、Vacuum
OS	CPU、run queue、RSS/Swap、IOPS、吞吐、磁盘延迟、空间
HA	send/write/flush/replay lag、恢复冲突、归档积压、同步复制等待

11.3 第三阶段：判断当前查询是在执行还是等待

SELECT pid,
       leader_pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - query_start AS query_elapsed,
       now() - xact_start  AS xact_elapsed,
       wait_event_type,
       wait_event,
       backend_xid,
       backend_xmin,
       query_id,
       left(query, 500) AS query_text
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;

解释：

• state='active' 不等于一直在使用 CPU；Wait Event 非空时可能正在等待；
• xact_elapsed 远大于 query_elapsed，可能存在长事务或 idle in transaction；
• 长期 backend_xmin 会阻碍 Vacuum 清理；
• leader_pid 可识别并行 Worker；
• query_id 是否存在取决于 Query ID 配置。

11.4 第四阶段：有锁等待时先找 blocker

SELECT a.pid AS waiting_pid,
       a.application_name,
       a.wait_event_type,
       a.wait_event,
       now() - a.query_start AS waiting_for,
       pg_blocking_pids(a.pid) AS blocking_pids,
       left(a.query, 300) AS waiting_query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

继续展开：

WITH waiting AS (
    SELECT pid,
           unnest(pg_blocking_pids(pid)) AS blocker_pid
    FROM pg_stat_activity
)
SELECT w.pid AS waiting_pid,
       b.pid AS blocker_pid,
       b.state AS blocker_state,
       now() - b.xact_start AS blocker_xact_age,
       left(b.query, 300) AS blocker_query
FROM waiting AS w
JOIN pg_stat_activity AS b
  ON b.pid = w.blocker_pid;

只要存在明确 blocker，先处理事务边界和锁链。高 actual time 可能主要包含等待，不代表节点算法需要更换。

11.5 第五阶段：找出资源大户与尾延迟语句族

安装 pg_stat_statements 后，可先看累计资源：

SELECT queryid,
       calls,
       total_exec_time,
       mean_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read,
       temp_blks_read,
       temp_blks_written,
       wal_bytes,
       left(query, 400) AS normalized_query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

理解边界：

• total_exec_time 找总资源大户；
• mean_exec_time 可能掩盖少数极慢参数；
• rows 是累计返回或影响行数，不是扫描行数；
• shared_blks_read 不等于物理盘读取；
• temp_blks_* 指向 Sort/Hash/tuplestore 落盘；
• wal_bytes 可解释写放大与复制压力。

应用侧仍必须按 query_name 和参数类别看 P95/P99。

11.6 第六阶段：安全取得计划

按风险从低到高：

1. 保存 SQL 语义、稳定查询名和参数类别；
2. 获取 EXPLAIN (VERBOSE, COSTS, SETTINGS)；
3. 参数敏感 SQL 比较 Generic 与 Custom；
4. 保存版本、GUC、Schema、统计时间和节点角色；
5. 在克隆或受控副本执行 EXPLAIN ANALYZE；
6. DML 使用脱敏环境，不依赖 ROLLBACK 消除所有副作用。

11.7 第七阶段：沿七步法找最早错误

从叶子向上填写：

节点	Est Rows	Actual Rows	loops	累计量	Buffers/temp	结论
叶子 Scan
第一个 Join
Aggregate
Sort/Gather

先找第一个 10×/100× 误差，再解释它如何传播。不要从根节点倒推一个未经证据支持的猜测。

11.8 第八阶段：把问题归类

类别	计划/数据库信号	外部信号	首要动作
估算	Est/Actual 大幅偏差	参数或数据分布变化	修统计、SQL、数据模型
CPU	高 loops、Filter、Hash/Sort，Buffers 多为 hit	CPU、run queue 高	减少处理行数与重复计算
内存	spill、Batches、eviction；或无 spill 但 RSS 高	Swap/OOM	调整查询与并发预算
I/O	shared/temp read/write 高	磁盘延迟、队列高	区分数据读、temp、Checkpoint
锁	Lock Wait、blocking_pids	请求堆积	处理 blocker 与事务边界
连接池	DB active 不高，Acquire 高	应用队列上升	查泄漏、长事务、准入
WAL	WAL bytes/FPI 高	WALSync、复制 lag	减写放大、检查 Checkpoint
Vacuum	Heap Fetches、死 Tuple 读放大	dead tuples、old xmin	修长事务与 autovacuum
复制	Replica 慢或被取消	replay lag、conflicts	限制副本查询、隔离资源

11.9 第九阶段：临时止损

止损优先保护系统，不追求立即获得理论最优计划。

1. 限制异常查询入口与并发；
2. 设置操作级超时和有界队列；
3. 暂停非关键报表或批任务；
4. 按参数类别路由到已验证的安全 SQL 变体；
5. 使用事务级/角色级配置，不做全局激进修改；
6. 必要时取消明确的非关键大查询；
7. 禁止无上限自动重试。

高风险止损包括：

• 全局关闭 Seq Scan 或某类 Join；
• 无预算提高 work_mem、并行度和连接数；
• 关闭 fsync、full_page_writes 或同步复制保护；
• 在生产大表直接对写语句执行 EXPLAIN ANALYZE；
• 未确认事务语义就终止未知会话。

11.10 第十阶段：根治与三维验证

根治可能是：

• 更新或扩展统计；
• 增加或调整索引；
• 使谓词可索引并提前过滤；
• 改写 Join、Aggregate、窗口或分页方式；
• 预聚合、分区、物理隔离；
• 修复事务边界与连接泄漏；
• 为报表建立独立池和准入；
• 处理复制与存储资源竞争。

验证必须覆盖：

• 多类真实参数；
• 冷/热缓存；
• 单查询与目标并发；
• P50/P95/P99、吞吐和错误；
• Buffers、temp、WAL、CPU、I/O、Wait Event；
• Primary 与 Replica；
• 故障切换后的连接重建和冷缓存；
• 回滚方案与容量上限。

---

12. 两个生产事故：把整条因果链走一遍

12.1 事故一：大租户在若干次执行后 P99 突然升高

背景

订单服务使用 pgxpool 与 Prepared Statement。绝大多数租户只有几百到几万行，少数租户有数千万行。查询按 tenant_id 和时间范围过滤，再做 Join、聚合与排序。

现象

• 发布后最初几次执行正常；
• 随后普通租户仍快，大租户从几十毫秒升到数秒；
• 平均耗时变化不大，但大租户 P99 和 pool Acquire 明显上升；
• 团队最初认为是缓存抖动，于是提高连接池上限，问题反而加重。

证据链

1. 应用按 query_name × tenant_size_bucket 分桶后，异常只出现在大租户；
2. 锁等待不明显，CPU 和随机 I/O 上升；
3. Custom Plan 对大租户使用 Bitmap/批量扫描 + Hash Join；
4. [PG16+] GENERIC_PLAN 显示通用计划偏向参数化 Index Scan + Nested Loop；
5. 真实大租户使外侧行数与内侧 loops 大幅增加；
6. 更多连接让更多坏计划同时执行，I/O 队列和池等待继续放大。

根因

参数分布重尾，Generic Plan 适合多数小租户，却不适合极端大租户。问题不在“Prepared Statement 本身”，而在一个统一计划无法覆盖相差数个数量级的数据分布。

止损

• 限制大租户查询并发；
• 将大租户路由到独立 SQL 路径；
• 在窄范围会话评估 Custom Plan；
• 不再提高连接数。

根治

• 建立小/中/大租户低基数路由；
• 补充统计和合适索引；
• 必要时按租户或时间分区；
• 极端租户升级为物理隔离；
• 回归测试覆盖执行序列、Custom/Generic 与目标并发。

三维复盘

维度	复盘
高性能	大参数需要不同访问路径，不能只用平均参数验证
高并发	坏计划 × 更多连接形成随机 I/O 和队列雪崩
高可用	资源饱和会扩大复制 lag，并影响故障切换时新 Primary 接管能力

---

12.2 事故二：并发报表打满临时盘并拖慢副本

背景

Primary 同时承载 OLTP 与每小时报表。报表包含 Parallel Seq Scan、两个大型 Sort 和 WindowAgg，调度器一次启动 40 个任务。

现象

• temp bytes/s 暴涨；
• 临时盘延迟和 OLTP P99 同时上升；
• 部分报表报“设备无空间”；
• 异步副本 replay lag 增长；
• 团队准备把全局 work_mem 从 4 MB 提到 256 MB。

证据链

1. 计划显示 external merge 与大量 temp blocks；
2. pg_stat_statements 和 log_temp_files 指向同一报表族；
3. 每个查询有多个内存节点和并行参与进程；
4. 40 路并发让临时 I/O 同时发生；
5. 副本 replay 与主库业务 I/O 共享底层存储带宽；
6. 按 活跃查询 × 节点 × Worker × work_mem 估算后，全局 256 MB 可能远超物理内存。

根因

不是单一 work_mem 太小，而是：

宽行全排序
+ 大 Window 分区
+ 多内存节点
+ 并行 Worker
+ 40 路无界并发

共同形成资源乘法。

止损

• 暂停非关键报表；
• 将报表并发降到可承受范围；
• 使用独立角色和事务级内存设置；
• 必要时降低该工作负载并行度；
• 保护 OLTP 与复制重放资源。

根治

• 建立报表专用队列与连接池；
• 预聚合和减少 SELECT 宽度；
• 增加匹配过滤与排序前缀的索引；
• 拆分大报表或迁移到分析系统；
• 为临时盘、内存和 Worker 设置容量预算与告警。

三维复盘

维度	复盘
高性能	单查询 Sort 可以通过减少输入、利用顺序和受控内存优化
高并发	关键问题是无界任务数，而不只是单查询配置
高可用	临时 I/O 拖慢 replay，故障发生时副本可能无法及时接管

---

13. 常见错误与反模式

反模式	为什么错	正确做法
把 cost 当毫秒	cost 是相对比较单位	用 Actual、Buffers 和现场指标测真实代价
只看根节点最慢	根节点包含整个子树	从叶子找最早数量级误差
忽略 loops	微秒级节点可执行几十万次	计算 rows/time × loops
看到索引就要求 Index Scan	高命中时随机回表可能更贵	比较选择性、行宽、顺序和 Buffers
永久设置 enable_seqscan=off	破坏其他查询，只是劝阻开关	修统计、SQL、索引或成本模型
全局大幅提高 work_mem	节点、Worker、并发会相乘	按角色/事务设置并配合准入
提高连接数解决慢查询	更多坏计划会同时竞争资源	先降低单查询工作量和活跃并发
在 Tracer 自动 EXPLAIN ANALYZE	二次执行、可能二次写入	采集标识，在受控环境重放
对生产 DML 直接 ANALYZE	会真实修改、加锁、产 WAL	使用脱敏克隆并评估副作用
比较不同参数和缓存的两次计划	变量不可归因	固定参数类别、GUC、数据与缓存状态
把 LIMIT 下少读行判为估算错	子节点可能被提前停止	区分完整估算与实际消费比例
只看平均耗时	长尾参数与排队会被掩盖	看 P50/P95/P99 并按参数分桶
忽略 Filter 与 Join Filter	可能先读或配对海量数据再丢弃	推动条件进入更早访问阶段
把 shared read 等同物理盘读	可能命中 OS Page Cache	联合 I/O Timing、pg_stat_io 与 OS 指标
看到 Parallel 就认为更快	有启动、传输、Leader、倾斜成本	比较 Planned/Launched 与系统吞吐
忽略 Generic Plan	同 SQL 的参数需求可能完全不同	成对验证 Custom/Generic
记录完整 SQL 和参数	泄密、高基数、日志成本高	使用稳定 query_name 和脱敏参数类别
把副本当免费报表机	查询会与 replay 竞争并产生恢复冲突	独立准入、监控 lag、必要时用分析系统
只验证故障切换后能连接	可连接不等于性能恢复	把关键 SQL P99 和冷缓存纳入 RTO

---

14. 面试题

面试题仍沿着本章主线组织：先考 Planner/Executor 边界，再考证据链，最后考三个维度的系统设计。

14.1 基础原理题

Q1：为什么 EXPLAIN 的 cost 不是毫秒？

核心回答：cost 是 Planner 比较候选 Path 的相对单位，由页访问、Tuple 处理、表达式、Sort、Hash、并行等成本组成；Actual Time 才是 EXPLAIN ANALYZE 记录的毫秒。两者可能相关，但不能换算。

展开要点：total cost 通常包含子节点；缓存、并发和 I/O 队列会改变实际耗时；成本参数应按整体工作负载校准，不能为一条 SQL 随意修改。

追问：SSD 上为什么也不应把 random_page_cost 机械设为 1？

回答：随机访问仍包含索引层级、Heap 回表、CPU、缓存局部性和并发影响，应通过基准和回归确定。

Q2：如何解释 actual time=0.02..0.08 rows=3 loops=10000？

核心回答：每次平均 0.02 ms 返回首行、0.08 ms 完成，平均输出 3 行，共执行 10,000 次；累计输出约 30,000 行，粗略累计工作约 800 ms。

追问：为什么不能用父节点时间减子节点时间得到精确独占时间？

回答：父子时间嵌套、计时有开销，并行执行还会重叠。

Q3：Path、Plan、PlanState 有什么区别？

核心回答：Path 是 Planner 比较候选执行方式的轻量对象；Plan 是从最优 Path 生成的完整执行树；PlanState 是 Executor 为 Plan 创建的运行时状态。

追问：EXPLAIN 为什么看不到所有候选 Path？

回答：EXPLAIN 展示被选中的 Plan，不展示规划过程中被裁剪和淘汰的全部候选。

Q4：为什么 Cardinality 是执行计划的主线？

核心回答：Scan 选择、Join 顺序与算法、Aggregate 组数、Sort/Hash 内存、并行收益和 Nested Loop loops 都依赖行数估算。叶子估算错会层层传播。

追问：为什么要找最早误差而不是最大误差？

回答：上层最大误差通常只是下层错误传播的结果，最早误差更接近可修复的统计、谓词或参数根因。

Q5：Index Cond、Filter、Recheck Cond、Join Filter 的区别是什么？

核心回答：Index Cond 限制索引访问范围；Filter 在节点取出 Tuple 后过滤；Recheck Cond 对 Bitmap 候选重新验证；Join Filter 在候选行对形成后过滤。

追问：Rows Removed 很高一定表示索引缺失吗？

回答：不一定，还可能是语义无法下推、条件顺序、低选择性或 Join 结构问题，需结合节点位置判断。

14.2 计划诊断题

Q6：有索引却选择 Seq Scan，排查顺序是什么？

核心回答：先看谓词选择性和返回比例，再看所需列是否回表、表大小、物理相关性、统计是否准确、LIMIT/排序属性和替代路径成本。高命中时 Seq Scan 可能完全正确。

追问：如何安全验证索引替代计划？

回答：先用 Plain EXPLAIN 和事务级 enable_seqscan=off 做反事实，不在生产大表盲目运行强制计划的 ANALYZE。

Q7：Nested Loop、Hash Join、Merge Join 怎么选？

核心回答：小外表加高选择性索引适合 Nested Loop；大规模等值连接常适合 Hash Join；两侧已有合适顺序或需保序时 Merge Join 有优势。选择取决于行数、排序、内存和 startup/total cost。

追问：Nested Loop 最危险的信号是什么？

回答：外侧 Actual Rows 远大于 Estimated Rows，导致内侧 loops 数量级放大。

Q8：如何判断 Sort 或 Hash 落盘，应该怎么修？

核心回答：Sort 看 external merge、Disk 和 temp blocks；Hash 看 Batches、Disk Usage 和 temp blocks。先减少输入与行宽、利用顺序或预聚合，再按角色/事务调整内存，并验证并发预算。

追问：为什么不能直接全局提高 work_mem？

回答：它按节点、并行参与进程和活跃查询相乘，可能造成 Swap/OOM。

Q9：为什么 LIMIT 会改变计划？

核心回答：LIMIT 提高 startup cost 的权重。Nested Loop 或索引路径可能更快产生前 N 行，即使完整执行成本高于 Hash Join 或全量 Sort。

追问：ORDER BY ... LIMIT 10 为什么仍可能扫描很多行？

回答：缺少匹配顺序的索引、过滤与排序键不一致、相关性差，或合格行很稀疏。

Q10：Generic Plan 为什么会产生参数敏感问题？

核心回答：Generic Plan 不针对当前参数值，适合分布均匀与规划成本高的语句；参数高度偏斜时，一个平均计划可能只适合多数值，却伤害少数大值。

追问：为什么问题可能执行几次后才出现？

回答：系统可能先使用 Custom Plan，随后比较重复规划成本并转向 Generic Plan，切换后行为才变化。

Q11：Workers Planned: 4、Workers Launched: 0 怎么排查？

核心回答：检查会话与集群并行参数、全局 Worker 配额、同时运行的并行任务和执行上下文；再判断 Leader 独自执行后的退化程度。

追问：Gather 与 Gather Merge 的区别？

回答：Gather 收集无序流；Gather Merge 合并各 Worker 的有序流并保持全局顺序，Leader 还承担归并成本。

14.3 系统设计题

Q12：设计一套不会拖垮数据库的计划观测体系。

核心回答：请求路径只记录低基数 query_name/queryid、耗时、SQLSTATE、行数、池等待和参数类别；用 Histogram 发现 P99；异常后先 Plain EXPLAIN，再在受控克隆用脱敏参数执行 ANALYZE；保存计划、版本、GUC 和 Schema 指纹。

关键取舍：低开销与现场真实性之间有差距，所以需要应用、pg_stat_statements、Wait Event、计划仓库和受控重放组合，而不是在线重跑所有慢查询。

Q13：如何隔离 OLTP 与大 Sort/Hash 报表？

核心回答：使用独立入口、角色和 pgxpool；报表进入有界队列，设置较低并发、角色/事务级内存与并行限制；监控临时空间；必要时迁移到副本或独立分析系统。

关键取舍：副本可保护 Primary，但不是免费资源，还要考虑 lag、恢复冲突、数据时效和故障时的回落策略。

Q14：多租户系统怎样治理参数敏感计划？

核心回答：按租户规模和参数分布建立低基数分类，比较 Custom/Generic Plan，使用稳定 SQL 变体或路由；维护扩展统计和索引；极端租户考虑分区或物理隔离。

关键取舍：不能为每个租户生成独立 SQL 文本，否则计划缓存、监控和运维复杂度会失控。

Q15：故障切换后数据库已可连接，但关键查询 P99 很差，怎样验证？

核心回答：检查新 Primary 的 GUC、扩展、索引、统计、Worker 配额和存储；观察冷缓存 Buffers、I/O、Checkpoint、WAL 追赶；确认连接池重连与 Prepared Plan 重建；用关键参数集比较切换前后的计划和 Actual。

关键取舍：可连接只是控制面恢复，关键查询性能、复制追赶和流量承载能力才决定业务 RTO 是否真正完成。

Q16：一个“执行时间降低 90%”的优化为什么仍可能被拒绝？

核心回答：它可能通过超宽覆盖索引、全局大 work_mem、过高并行度或关闭持久性保护换取单查询速度，从而恶化写入、并发内存、WAL、复制与恢复。优化必须通过性能、并发、可用性三维验收。

---

15. 练习与参考答案

15.1 理论题

题 1

一个节点显示 cost=100..200 rows=1000 width=80。解释四个值，并说明为什么不能推断执行为 200 ms。

参考答案：100 是 startup cost，200 是完整执行 total cost，1000 是预计每次完整执行输出行数，80 是平均输出宽度。Cost 是相对单位，受成本参数和估算影响，不包含连接池排队与网络，不能换算为毫秒。

题 2

某内侧节点 actual rows=2 loops=500000，上层只输出 100 行。能否忽略内侧节点？

参考答案：不能。内侧累计输出约 100 万行并执行 50 万次。上层只输出 100 行可能是 Join Filter 或后续过滤大量丢弃。应检查单次时间、Buffers、参数化 Index Cond 和最早行数误差。

题 3

HashAggregate 与 GroupAggregate 的核心取舍是什么？

参考答案：HashAggregate 不要求输入有序，以组为 Hash 状态，组数/宽度低估会 spill；GroupAggregate 依赖 Group Key 顺序，可利用索引或 Sort 并逐组处理。选择取决于组数、排序成本、内存、并行和输出顺序。

题 4

为什么 LIMIT 10 可能使 Planner 从 Hash Join 改为 Nested Loop？

参考答案：LIMIT 提高 startup cost 权重。Nested Loop 配合索引可能更快产生前 10 行；Hash Join 要先构建 Hash 表。若实际匹配稀疏，Nested Loop 仍可能读取很多候选，所以必须用真实参数验证。

15.2 实验题

题 5：让实验一出现 Index Only Scan

建立覆盖索引：

CREATE INDEX orders_status_cover_idx
ON ch06_e1.orders(status)
INCLUDE (amount);

VACUUM (ANALYZE) ch06_e1.orders;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SUMMARY)
SELECT sum(amount)
FROM ch06_e1.orders
WHERE status = 'refunded';

参考结论：是否选择 Index Only 仍由成本决定。Heap Fetches 低表示多数页面 all-visible；覆盖索引会增加空间、写放大和 WAL。

题 6：只提高单列统计目标，为什么实验二仍可能估算错误？

参考答案：单列 MCV/直方图能更准确描述每列约 10% 的边际选择性，但不知道四列总是一起取特殊值，Planner 仍可能按独立性相乘。需要 dependencies/MCV 扩展统计。

题 7：为实验三设计并发测试

参考答案：在专用测试库中分别运行 1、4、8、16 并发；每轮用事务级 SET LOCAL work_mem；固定数据、参数和缓存记录方式；采集 P50/P95/P99、RSS、Swap、temp bytes、磁盘延迟、active query、pool wait 和错误。预先设置内存与临时盘停止阈值。

15.3 排障题

题 8

线上 WHERE tenant_id=$1 平均 20 ms，但 P99 5 s，pg_stat_statements 均值正常。给出排查顺序。

参考答案：按 query_name 和租户规模分桶；区分 pool wait、锁与执行；提取异常参数类别；比较 Plain、Generic 与 Custom Plan；从叶子检查 rows/loops；联合 Buffers、I/O、Wait Event。若 Generic 对大租户失配，先限流/路由，再用 SQL 变体、统计、索引或隔离根治。

题 9

计划显示 Parallel Seq Scan，但执行比串行慢，如何判断原因？

参考答案：看 Workers Planned/Launched、per-worker rows、Leader 工作量、Gather/Gather Merge、并行内侧是否重复构建；检查其他并行任务、CPU run queue、I/O 带宽和数据倾斜；在相同数据与缓存下对比串并行计划。

15.4 系统设计题

题 10

设计“慢查询发现—计划复现—回归验证—灰度发布”系统。

参考答案：

1. 采集：pgx Tracer 输出 query_name、duration、SQLSTATE、rows、pool wait、参数类别；
2. 检测：按查询名和参数桶计算 P50/P95/P99、错误率、吞吐；
3. 证据：保存版本、GUC、Schema hash、统计时间、计划 JSON 与脱敏样本；
4. 复现：先 Plain EXPLAIN，再在克隆执行 Analyze，采集 Buffers、temp、WAL 与 OS 指标；
5. 修复：统计、索引、SQL、数据模型、隔离或准入；
6. 回归：覆盖参数偏斜、冷/热缓存、目标并发、Primary/Replica；
7. 灰度：小流量、自动回滚阈值、计划指纹与 P99 对比；
8. HA：故障切换后重新验证连接重建、冷缓存与核心计划。

---

16. 本章检查清单

Planner 与 Executor

• 能画出 Query → RelOptInfo → Path → Plan → PlanState → Tuple。
• 能解释 Planner 的“最优”依赖统计、成本与候选空间。
• 能说明 demand-pull、流式节点和阻塞节点。
• 能解释为什么 Cardinality 是计划决策的主线。

EXPLAIN 证据链

• 不把 cost 当毫秒。
• 能正确解释 Actual Time、Rows 与 loops。
• 能从叶子找到最早数量级误差。
• 能区分 Index Cond、Filter、Recheck Cond、Join Filter。
• 能结合 Buffers、temp、WAL、Wait Event 和 OS 指标。
• 知道 EXPLAIN ANALYZE 会真实执行 SQL。

节点与失控机制

• 能解释 Seq、Index、Index Only、Bitmap 的取舍。
• 能解释 Nested、Hash、Merge Join 的适用条件。
• 能识别 Sort、Hash、Aggregate 的 spill。
• 能区分 Materialize 与 Memoize。
• 能解释 Generic/Custom Plan 与参数偏斜。
• 能分析 Worker Planned/Launched 与并行倾斜。

三个维度

• 高性能：能量化读放大、写放大和空间放大。
• 高并发：能计算节点 × Worker × 活跃查询的资源乘法。
• 高可用：能说明计划对 WAL、复制、冷缓存与 RTO 的影响。
• 不会用关闭持久性保护换取查询速度。

生产闭环

• pgx Tracer 使用稳定低基数 query_name，不记录敏感参数。
• 请求路径不会自动执行 EXPLAIN ANALYZE。
• 能区分应用排队、pool Acquire、锁等待和数据库执行。
• 能执行止损、根治、三维回归与故障切换验证。

---

17. PostgreSQL 14—18 相关能力

版本	与本章直接相关的能力
[PG14+]	Memoize 可缓存参数化 Nested Loop 内侧结果
[PG16+]	EXPLAIN (GENERIC_PLAN) 可查看带参数语句的通用计划，不能与 ANALYZE 同用
[PG17+]	EXPLAIN 提供 MEMORY 等规划期观测，并可用 SERIALIZE 观察结果序列化成本
[PG18]	AIO 改善部分顺序读取路径；EXPLAIN 与 Join/Incremental Sort 等方面继续增强

版本差异不会改变本章方法：始终比较 Planner 的估算与 Executor 的实际工作，再把工作量放到并发与 HA 环境中验证。

---

18. 官方资料

1. PostgreSQL 18：Using EXPLAIN
2. PostgreSQL 18：EXPLAIN
3. PostgreSQL 18：Parser Stage
4. PostgreSQL 18：Query Tree
5. PostgreSQL 18：Planner/Optimizer
6. PostgreSQL 18：Executor
7. PostgreSQL 18：Query Planning Configuration
8. PostgreSQL 18：Resource Consumption
9. PostgreSQL 18：Parallel Plans
10. PostgreSQL 18：WITH Queries / CTE
11. PostgreSQL 18：Monitoring Statistics
12. PostgreSQL 18：pg_stat_statements
13. PostgreSQL 18：Logging / log_temp_files
14. pgx/v5 官方 API
15. pgx/v5 QueryTracer
16. pgxpool 官方 API