PostgreSQL EXPLAIN 精读：先看行数，再谈优化

“加了索引为什么不用"之前，先学会读计划

最熟悉的场景：慢查询告警拉群，有人瞄了一眼发现走了 Seq Scan，第一反应是"优化器又蠢了”，然后开始研究怎么强制走索引。PostgreSQL 原生没有 hint，于是装 pg_hint_plan，或者改写 SQL 去骗优化器。折腾一圈，第二天另一个查询又慢了。

问题在于跳过了最重要的一步：读懂计划本身。优化器选错计划几乎总是有原因的，而原因就印在输出里：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM   events
WHERE  tenant_id = 7
AND    created_at >= now() - interval '1 day';

输出：

Seq Scan on events  (cost=0.00..189932.00 rows=41 width=156)
                     (actual time=0.402..931.144 rows=48213 loops=1)
  Filter: ((tenant_id = 7) AND (created_at >= now() - '1 day'::interval))
  Rows Removed by Filter: 9951787
  Buffers: shared hit=23104 read=121588

索引 (tenant_id, created_at) 明明存在，优化器却选了全表扫。先别急着下结论——第一行就藏着答案的一半：估算 41 行，实际 48213 行，差了三个数量级。

第一步永远是看行数：estimated 对不上 actual

优化器的一切决策都建立在基数估算上：用哪个索引、join 选什么算法、要不要并行。估算错，后面全错。它不是蠢，是被喂了错误的信息。

估算偏差最常见的三个来源，按概率排序：

1. 统计信息过期。昨晚跑完一批大导入，autovacuum 还没来得及 ANALYZE 这张表。
2. 数据倾斜没被采样到。tenant 7 是个大租户，但 MCV（most common values）列表里没有它，优化器只能拿直方图平均一下。
3. 组合条件的关联性失准。单列统计不知道 tenant_id 和 created_at 的组合分布。

先看统计信息长什么样：

-- 这张表的统计概况
SELECT attname, n_distinct, correlation
FROM   pg_stats
WHERE  schemaname = 'public'
AND    tablename  = 'events';

-- tenant_id 的高频值与频率：大租户在不在列表里，频率给了多少
SELECT most_common_vals, most_common_freqs
FROM   pg_stats
WHERE  tablename = 'events'
AND    attname   = 'tenant_id';

n_distinct 为负数时表示"占表的比例"，-1 代表全列唯一。correlation 是物理存储顺序与逻辑顺序的相关度，接近 0 时范围扫描的随机读代价会被放大，直接影响优化器愿不愿意用索引。

处理顺序永远是：先 ANALYZE，再调采样，最后才怀疑优化器。

-- 一次 ANALYZE 就能解决相当一部分"昨天还好好的"
ANALYZE events;

-- 倾斜仍然估不准时，提高这一列的采样精度（默认 target 是 100，采样越多估得越细）
ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE events;

回到开头的案例：tenant 7 占了全表千分之五的数据，默认采样没抓住这个倾斜，优化器以为只要 41 行，算出来的代价里 Seq Scan 反而便宜。统计修正后，同一句 SQL 什么都不用改，Index Scan 自然胜出。

计划树怎么读：内层先执行，loops 要做乘法

EXPLAIN 输出是一棵缩进树，缩进越深越靠近数据源。执行顺序不是从上往下，而是内层节点先产出数据，外层节点消费。读计划时从缩进最深的节点开始，自下而上拼出数据流。

ANALYZE 输出里最容易误读的是 loops：

Nested Loop  (actual time=0.412..412.902 rows=1000 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..18.50 rows=1000 width=64)
                             (actual time=0.018..1.204 rows=1000 loops=1)
  ->  Index Scan using idx_payments_order_id on payments p
        (actual time=0.281..0.384 rows=2 loops=1000)
        Index Cond: (order_id = o.id)

内层 Index Scan 的 actual time 0.384 不是它的总耗时，而是每一次执行的平均耗时。它跑了 1000 次（loops=1000），贡献的总时间约为 0.384ms × 1000 ≈ 384ms，占了整棵树的九成。如果只按数字大小找瓶颈，你会误以为问题在外层那个 1.2ms 的 Seq Scan 上。

两个细节：

• actual time 的两个值分别是启动时间（产出第一行之前）和总时间，都按"每次执行"计。
• BUFFERS 的计数是跨所有 loops 聚合好的总量，不需要再乘——只有时间要乘。

三种 Join，优化器到底在选什么

行数读准之后，join 算法的选择逻辑就透明了。

Nested Loop：外层每来一行，去内层查一次。内层有索引、外层行数少时是碾压性的最优；反过来，外层十万行、内层没有合适索引时就是灾难。

Hash Join：把较小的一边建成哈希表，另一边流式探测。不要求索引、不要求有序，是大数据量的默认选择。要盯的信号是哈希表有没有装进内存：

Hash Join  (actual time=88.10..510.42 rows=48213 loops=1)
  Hash Cond: (e.user_id = u.id)
  ->  Index Scan using idx_events_tenant_created on events e
        (actual time=0.310..92.200 rows=48213 loops=1)
        Index Cond: ((tenant_id = 7) AND (created_at >= now() - '1 day'::interval))
  ->  Hash  (actual time=80.20..80.22 rows=910000 loops=1)
        Buckets: 65536  Batches: 24  Memory Usage: 2871kB
        ->  Seq Scan on users u  (actual time=0.012..58.901 rows=910000 loops=1)

Batches: 24 说明哈希表在 work_mem 里装不下，被切成 24 批落盘、分轮探测——这是明确的"内存不足"信号。会话级放大会立竿见影：

BEGIN;
SET LOCAL work_mem = '256MB';  -- 只影响本事务，别改全局
-- 重跑查询，Batches 应回到 1
ROLLBACK;

Merge Join：两边都按 join key 有序（索引提供顺序，或显式 Sort）时两两归并。适合大表对大表且本身有序的场景，还能顺便产出排序结果。它的坏味道是两边各挂一个大 Sort——排序的代价可能比换算法省下的还多。

BUFFERS：比时间更稳定的证据

actual time 受机器负载、缓存冷热影响很大，BUFFERS 的数字稳定得多，适合做优化前后的对照：

• shared hit：从 shared_buffers 命中的块数。
• shared read：未命中、需要发起 I/O 的块数。注意 PostgreSQL 分不清"真磁盘"和"操作系统 page cache"，read 高不等于一定在磨盘。
• temp read/written：排序、哈希溢出到 work_mem 之外产生的临时文件 I/O，出现了就是 work_mem 不够。

Sort  (actual time=1204.31..1218.90 rows=48213 loops=1)
  Sort Key: e.created_at
  Sort Method: external merge  Disk: 9840kB
  Buffers: shared hit=144816 read=2044, temp read=1231 written=1236

Sort Method: external merge 加上 temp written，两个信号互相印证：这个排序该放进内存。调 work_mem 之前先算账——每个排序节点、每个并发连接各占一份，按最坏情况估总量再改。

一眼坏味道：三件事先查

拿到计划先扫这三样，多数问题当场现形：

1. Seq Scan 加巨大的 Rows Removed by Filter。过滤掉上千万行才留下几万行，索引缺失或不可用。不可用的常见原因：条件里对列套了函数（lower(email) = ... 需要表达式索引）、隐式类型转换、复合索引的左前缀列不在条件里。
2. 外层行数巨大的 Nested Loop。loops 乘内层单次耗时的乘法一爆炸，p99 就没了。根源通常还是行数估算错——优化器以为外层只有 1 行。
3. estimated rows = 1。组合条件估成 1 行是偏差重灾区，顺藤摸瓜查 pg_stats，十有八九是统计信息或相关性问题。

参数化 SQL 的暗坑：generic plan 切换

应用跑的都是 PREPARE 过的参数化语句（JDBC、ORM 的默认行为），这引入了另一层不确定性：同一条 SQL 可能有两套计划。

PREPARE tenant_events(bigint) AS
SELECT id, payload
FROM   events
WHERE  tenant_id = $1
AND    created_at >= now() - interval '1 day';

-- 前 5 次执行用 custom plan：代入真实参数做估算
EXECUTE tenant_events(7);
EXECUTE tenant_events(7);
EXECUTE tenant_events(7);
EXECUTE tenant_events(7);
EXECUTE tenant_events(7);

-- 第 6 次起，优化器比较 generic plan 的估算成本与前几次 custom plan 的平均成本，
-- 不明显更差就切到 generic plan：不再代入具体值，只用全表平均选择性
EXECUTE tenant_events(7);

放到 tenant 7 是大租户的场景：custom plan 估出 48213 行走索引，generic plan 用平均选择性估出几十行，计划随之劣化。典型症状是"同一条 SQL 有时快有时慢"，慢的时机和连接复用次数相关（PgJDBC 的 prepareThreshold 默认 5，恰好对上这个切换点）。

PG12 起可以干预：

-- 会话级强制每次代入参数、生成 custom plan
SET plan_cache_mode = force_custom_plan;
-- 看当前值：auto / force_generic_plan / force_custom_plan
SHOW plan_cache_mode;

更根本的解法是让优化器有可信的平均值可用——又绕回了统计信息。

让解读变成日常：auto_explain 与 pg_stat_statements

人肉 EXPLAIN 只能覆盖你恰好在看的语句，两件工具把覆盖面铺开：

# postgresql.conf；shared_preload_libraries 的变更需要重启
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# 执行超过 500ms 的语句自动把计划写进日志
auto_explain.log_min_duration = '500ms'
auto_explain.log_buffers = on
# log_analyze 能带出 actual 行数与 loops，但逐语句有可观开销：
# 排查窗口再开，且可用 auto_explain.log_timing = off 去掉逐节点计时的干扰
auto_explain.log_analyze = off

再用 pg_stat_statements 找 top SQL，决定精力花在哪：

-- 按总耗时排序（PG13+ 叫 total_exec_time，PG12 及以下是 total_time）
SELECT round(total_exec_time::numeric) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       left(query, 90) AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

排序依据用总耗时而不是单次最慢：一条每次 80ms、每天百万次调用的查询，比一条偶尔 8 秒的报表更值得先治。

常见坑点

EXPLAIN ANALYZE 是真执行

ANALYZE 选项会真的跑这条语句。对 SELECT 是读；对 UPDATE、DELETE 是实打实的修改。包在事务里可以回滚数据，但序列已经前进，触发器里的外部副作用（发消息、写通知）不会消失。生产上分析写语句，先确认没有不可逆副作用，或者干脆在只读副本上做。

开发库的行数不等于生产

十 GB 的开发库和十 TB 的生产库，即使 schema 完全一致，倾斜度和数据量也不同，计划完全可以合法地不同。在开发库调出来的"最优计划"到了生产可能根本不会被选中。认真做优化时，用生产的脱敏副本。

JIT 对小查询可能纯属浪费

JIT 编译有启动成本，按总成本阈值触发（jit_above_cost，默认 100000）。刚过阈值的查询经常是"编译花几十毫秒，执行省几毫秒"。以短查询为主的 OLTP 库，直接 SET jit = off 是常见且合理的默认。

总结

解读顺序比记忆算法名重要得多：先对行数，修正统计；再读树和 loops，算清每个节点真实的贡献；然后看 BUFFERS 拿 I/O 证据；坏味道清单过一遍；参数化语句记得有 generic plan 这一层。最后用 auto_explain 和 pg_stat_statements，把这件事从人肉救火变成日常流水线。

计划不会说谎——它只是把你没告诉优化器的那些事，原样演给你看。