查询优化器为什么会选错执行计划

在索引为什么能加速查询中，我们介绍了索引如何缩小扫描范围、提供目标顺序，以及减少数据库取得结果时需要完成的工作。不过，表上存在索引，并不意味着每条查询都应该使用它。对于一条具体的 SQL，优化器还需要判断：通过索引反复查找，还是直接扫描整张表，哪一种方式的成本更低？

这个判断发生在查询执行之前。此时，优化器还不知道各个节点实际会处理多少数据，也不能为了选择计划而先把所有候选方案完整执行一遍。因此，它需要根据统计信息估算数据量，再通过成本模型比较不同方案。

本文继续沿用用户与订单模型，讨论优化器的这两个估算过程。首先，查询实际匹配 100 个用户，优化器却只估算出 10 个；补充统计信息后，行数估算恢复准确，但它选择的计划仍然比另一种方案更慢。我们将结合执行计划分析这两种情况，理解基数估算与成本估算分别怎样影响计划选择。

以下实验使用 PostgreSQL 18.3 和合成数据，性能比较在预热后的缓存条件下进行，完整脚本放在文末。后文的成本曲线使用单独的简化模型说明。不同产品、版本、数据和运行条件下，具体计划与耗时可能不同。

1. 同一条查询，为什么会有两种合理的计划

沿用前两篇的用户与订单模型，现在业务需要取得上海用户的订单明细：

SELECT o.id, o.status, o.created_at
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.country = 'CN'
  AND u.city = 'Shanghai';

假设用户表有 1 万行，订单表有 100 万行，每人恰好 100 条订单，并且存在 users(city) 和 orders(user_id) 索引。数据库至少可以采用下面两种做法：

方案 A：先找用户，再逐个查询订单
Nested Loop
├── 根据 city 筛选 users，再检查 country
└── 使用 user_id 索引查询当前用户的订单

方案 B：建立用户哈希表，再扫描订单
Hash Join
├── 扫描 orders，并用 user_id 查找匹配用户
└── Hash
    └── 筛选符合条件的 users

方案 A 使用嵌套循环连接（Nested Loop）。外层节点每输出一条用户记录，内层节点就查询一次该用户的订单。由于内层可以使用索引，不必为每个用户重新扫描整张订单表。如果只有少量用户符合条件，几次索引查找就可能完成查询。

方案 B 使用哈希连接（Hash Join）。它先把符合条件的用户组织成按连接键查找的哈希表，再扫描订单，逐条判断订单属于哪个用户。这个方案需要读取整张订单表，但可以在扫描过程中完成匹配，不必为每个用户分别查询订单。

两种方案都满足同一查询语义，但需要完成的工作不同。Nested Loop 的内层访问次数随着匹配用户数增长，Hash Join 则需要扫描整张订单表并构建用户哈希表。因此，比较两种方案之前，优化器首先需要估算有多少用户满足查询条件。

实际候选并不限于这两种。表访问可以采用 Seq Scan、Index Scan、Bitmap 路径或条件允许的 Index Only Scan；两侧输入有序时，还可能采用归并连接（Merge Join），沿连接键顺序寻找匹配。多表查询需要选择连接顺序，排序、聚合和并行也会增加组合。下面先比较 Nested Loop 和 Hash Join，说明行数估算如何影响这两种方案的选择。

2. 为什么预计只有 10 个用户，实际却有 100 个

在尚未建立多列统计时，实验中的 PostgreSQL 选择了方案 A，也就是 Nested Loop。理解这个选择，需要先看优化器预计有多少用户满足条件，再将这个估算与实际输出比较。

2.1 从计划里找到第一处偏差

EXPLAIN 展示选中的估算计划，EXPLAIN ANALYZE 则会实际执行语句，并记录各节点的输出和运行信息。先摘录用户表的读取分支，只保留定位行数偏差所需的属性；实验表名带有 optimizer_ 前缀。

Bitmap Heap Scan on optimizer_users u
  (rows=10) (actual rows=100.00 loops=1)
  Recheck Cond: (city = 'Shanghai'::text)
  Filter: (country = 'CN'::text)
  -> Bitmap Index Scan on optimizer_users_city_idx
       (rows=100) (actual rows=100.00 loops=1)
       Index Cond: (city = 'Shanghai'::text)

这里的 rows 是预计输出行数，actual rows 是实际输出行数。沿用户表的读取路径从下向上看，城市索引预计找到 100 条候选记录，实际也是 100 条；上面的 Bitmap Heap Scan 取得这些记录后检查国家条件，预计只剩下 10 行，实际却仍有 100 行。

Bitmap Index Scan 收集候选位置，Bitmap Heap Scan 再按数据页访问候选。Index Cond 表明哪些条件参与索引访问，Filter 表明哪些条件在取得候选后继续判断。这里第一处显著偏差出现在检查国家之后，并不在城市索引定位的那一步。

这说明偏差出现在两个条件的组合过滤中，但还不能仅凭计划确定原因。统计信息陈旧、采样误差，以及两个条件之间的相关性，都可能影响估算。要继续分析，需要先理解这 10 行是怎样估算出来的。

2.2 把两个正确的比例相乘，也可能得到错误答案

本例有 1,000 名中国用户，其中 100 名在上海、900 名在北京；另外 9,000 名用户位于纽约，国家和城市两列都没有空值。收集到的单列统计能够准确描述各自的分布：

country: US = 0.90, CN = 0.10
city:    New York = 0.90, Beijing = 0.09, Shanghai = 0.01

统计信息是数据库对数据分布保存的摘要。PostgreSQL 通过 ANALYZE 收集这些信息，其中一项是常见值列表（Most Common Values，简称 MCV），用于记录常见值及其频率。其他统计信息还包括空值比例、不同值数量和直方图等，可以通过 pg_stats 查看这些列统计。PostgreSQL 列统计信息

对于 city = 'Shanghai'，优化器可以直接使用常见值频率 0.01。这个条件预计保留输入的 1%，该比例就是选择率（Selectivity）；它描述条件返回 TRUE 的行数占输入行数的比例。

如果暂时把国家与城市看成独立条件，两个条件同时成立的选择率便近似为：

s(country = 'CN' AND city = 'Shanghai')
≈ 0.10 × 0.01 = 0.001

预计留下的用户数 = 10,000 × 0.001 = 10

一个结果包含的行数称为基数（Cardinality）。对于上面的过滤条件，输入行数乘以选择率，就得到预计输出的基数；根据统计信息估计行数的过程，就是基数估算（Cardinality Estimation）。这里的输入行数本身也来自估算：PostgreSQL 保存表的行数、页面数等信息，并可能根据当前页面规模调整行数估算，不会为每次查询规划重新扫描整表计数。优化器使用的统计信息

问题出在两个条件相互独立的假设上。本例中，上海用户全部属于中国，已经满足城市条件的记录也一定满足国家条件，因此第二个条件不会进一步减少结果。两个条件同时成立的实际比例是 0.01，对应 10,000 × 0.01 = 100 个用户。

更一般地，P(A AND B) = P(A) × P(B | A)。选出上海用户之后，其国家为中国的比例是 1，而不是全体用户中的 0.1。单列统计分别准确，也无法完整表达这两个字段之间的关系；重新采集同样类型的单列统计，并不会自动补上缺失的联合分布。

这也不意味着优化器对所有 AND 都机械相乘。同一列的上下界可以合并为范围，适用的多列统计也可能参与估算。独立性只是缺少更合适信息时的一种近似。PostgreSQL 组合条件估算实现

2.3 用户数的低估如何影响订单连接

现在把用户分支放回连接中，再展开订单分支；下面仍然只保留行数、重复次数与访问条件：

Nested Loop (rows=1000) (actual rows=10000.00 loops=1)
  -> Bitmap Heap Scan on optimizer_users u
       (rows=10) (actual rows=100.00 loops=1)
       ... 用户过滤分支见上文
  -> Bitmap Heap Scan on optimizer_orders o
       (rows=100) (actual rows=100.00 loops=100)
       Recheck Cond: (u.id = user_id)
       -> Bitmap Index Scan on optimizer_orders_user_idx
            (rows=100) (actual rows=100.00 loops=100)
            Index Cond: (user_id = u.id)

内层预计每次返回 100 条订单，实际每次也是 100 条；这一层的单次行数没有显著偏差。差异在于外层实际提供了 100 个用户，所以内层运行了 100 次：

估算连接输出 = 10 个用户 × 每人 100 条订单 = 1,000 行
实际连接输出 = 100 个用户 × 每人 100 条订单 = 10,000 行

PostgreSQL 的 actual rows 在多次执行时按每次平均显示，loops 表示执行次数。不能把内层单次估算的 100 行与累计的 10,000 行比较，误判成每次订单查询都低估了 100 倍。EXPLAIN 的行数与 loops

由此可以看到，用户过滤的估算偏差会影响连接输入，连接输出的估算偏差又可能影响后续排序或聚合。不过，各个节点对行数的影响并不相同：排序通常保持行数，分组输出取决于分组键组合的不同值数量，半连接和外连接也有各自规则。因此，分析误差的传播时，需要结合每个节点的操作，不能给整棵计划树统一乘一个误差倍数。

2.4 用联合统计修正组合条件的估算

为了验证相关性假设，我们分别查询国家、城市和两者组合，得到下面的结果：

可以看到，两个条件单独使用时估算准确，组合后才出现低估。结合本例“上海用户全部属于中国”的数据分布，问题在于单列统计没有表达国家和城市之间的关系。为了让优化器使用这层关系，我们为两列建立联合 MCV 统计：

CREATE STATISTICS optimizer_users_region_stats (mcv)
ON country, city FROM optimizer_users;

ANALYZE optimizer_users;

CREATE STATISTICS 声明需要收集哪些列的组合信息，随后的 ANALYZE 才实际收集。它与 EXPLAIN ANALYZE 中记录执行信息的 ANALYZE 不是同一件事。再次查询后，用户估算从 10 行变成 100 行，连接估算从 1,000 行变成 10,000 行，都与本例实际输出一致。PostgreSQL 多列统计

这次修正解决的是用户表内部的相关性。如果少数特定用户拥有大部分订单，即使用户过滤行数准确，沿用平均订单数仍可能错误。PostgreSQL 18 的扩展统计尚不用于表连接选择率估算；本例通过改善用户过滤估算间接改善连接输入，不能推广成解决了任意跨表相关性。CREATE STATISTICS 的适用边界

联合统计修正了行数估算，但这并不说明原来的 Nested Loop 执行得更慢。基数估算描述的是数据量，执行时间还取决于计划怎样处理这些数据。因此，接下来需要比较两种计划的估算成本和实际执行时间。

3. 行数估算准确，为什么仍没选到更快的计划

补充联合统计后，优化器自动选择的计划变成了 Hash Join。由于预计匹配的用户数增加，Nested Loop 的内层访问次数也随之增加，成本模型因此认为扫描订单并进行哈希连接的总成本更低。下面通过同一条件下的对照，检查这个估算是否符合实际执行情况。

3.1 在相同统计下比较两种计划

我们保留自动选择的 Hash Join，再通过当前事务的配置临时限制连接算法，使优化器选择 Nested Loop。三个实验阶段及其计划如下：

接下来比较后两行。两组使用相同 SQL、数据、索引、统计和资源参数，分别预热两次，然后记录各 10 次，每轮交换两种方案的先后顺序，使用 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) 记录执行时间。

两种方案的行数估算都准确，但估算成本更高的 Nested Loop 实际更快。

在本例的热缓存条件下，Nested Loop 的实际执行时间更短，但这个结果还不足以确定偏差由哪个成本参数造成，也不意味着生产环境应该禁用 Hash Join。要解释为什么估算成本更低的计划实际执行得更慢，还需要进一步理解两种计划各自完成了哪些工作，以及优化器如何估算这些工作的成本。

3.2 相同的输出行数，为什么有不同的执行成本

Hash Join 最终输出 1 万行，却需要先扫描并处理 100 万条订单。完整顺序扫描的主要成本可以简化为页面访问和逐行处理两部分。本次订单表占 7,353 个页面，在 seq_page_cost = 1、cpu_tuple_cost = 0.01 下，忽略上层连接等额外工作：

扫描成本 ≈ 页面数 × 顺序页面成本 + 扫描行数 × 每行处理成本
         = 7,353 × 1 + 1,000,000 × 0.01
         = 17,353

这与执行计划中的 Seq Scan cost=0.00..17353.00 一致。在扫描之外，连接节点还需要进行哈希探测、匹配和输出。如果扫描节点带有过滤条件，还需要逐行计算条件是否成立。最终输出较少，并不表示前面的扫描和条件计算也同样少。

Nested Loop 则通过订单表的 user_id 索引反复取得匹配订单，主要关系是：

Nested Loop 成本
≈ 外层访问成本
 + 外层行数 × 每次内层访问成本
 + 连接检查与输出成本

补充联合统计后的受控 Nested Loop 计划中，外层总成本为 62.48，内层单次总成本为 229.99，预计外层输出 100 行。连接键匹配已经用于内层索引访问，本例连接节点另按每条匹配记录 0.01 计算处理成本。将计划中显示的数值代入上面的关系，可以近似计算总成本：

外层访问                     62.48
100 次内层访问       100 × 229.99 = 22,999
连接记录处理      10,000 × 0.01 = 100
合计                         23,161.48

这与计划显示的 23,161.50 接近，0.02 的差异来自节点成本显示时的舍入。229.99 已包含内层索引与表访问，不能再把其子节点成本加一次。优化器因此把它排在总成本 20,042.84 的 Hash Join 之后：这里比较的是各自完成查询的总成本，而非某一个扫描节点的成本。

这个简化计算说明了本例的成本比较过程，PostgreSQL 的完整实现还会考虑更多因素。内层访问包含索引树查找、候选记录扫描和从表中取得记录，优化器也会考虑重复访问的缓存收益；如果计划包含物化或结果复用节点，重复执行的成本还会变化。PostgreSQL 成本估算实现

再看补充统计之前的用户表扫描。城市索引取得 100 条候选记录，国家条件预计只保留其中 10 条。扫描节点仍然需要取得这 100 条候选记录并检查条件，不能按照最终输出的 10 行计算全部访问成本。这里需要区分扫描行数、输出行数和重复次数，它们分别影响不同部分的工作量。

热缓存对照也体现了这种区别：Hash Join 的 shared hit 较少，但要处理 100 万条订单；Nested Loop 有更多重复块访问，却只取得 1 万条匹配订单。多行可以位于同一页，同一页也可能被访问多次。块访问次数和处理的行数并不相同，单看其中一个无法判断快慢。

Buffers 统计块访问次数，shared hit 表示访问时块已经位于 PostgreSQL shared buffers 中；shared read 仍可能命中操作系统缓存，不能直接当作物理磁盘读取次数。块访问次数已经累计，不应再乘 loops，父节点还包含子节点的贡献，不能沿计划树逐层相加；节点时间在多次执行时则按每次平均显示。EXPLAIN 输出选项

成本模型通过相对权重比较页面访问、条件计算、键比较和结果输出等工作，成本单位默认不是毫秒。这些权重本身包含缓存假设，但未必符合当前查询的热缓存条件，CPU 工作的相对影响也会随之变化。优化器不会在选择计划前逐页检查缓存，再精确预测整个执行期间的缓存状态。PostgreSQL 页面与 CPU 成本参数

不同计算的增长方式也不同：普通比较排序的工作量约随 N log N 增长；在不溢写且匹配分布合理时，哈希构建与探测大致随两侧输入规模增长，大量匹配产生的输出另有成本。归并连接需要有序输入，如果现有路径不提供顺序，还要支付排序成本。

内存是否足够也会影响执行成本。排序需要比较和保存记录，哈希连接需要保存构建侧的状态，所需空间取决于行数、行宽和数据结构开销。空间不足时可能将部分数据写入临时文件，也就是溢写，增加磁盘排序或分批处理的成本。PostgreSQL 的排序主要受 work_mem 约束，哈希操作还结合 hash_mem_multiplier。如果查询涉及排序或更大的哈希表，就需要进一步检查这些额外成本，不能只根据前面的热缓存对照推断。PostgreSQL 操作内存限制

3.3 估算误差如何改变计划选择

上面的对照说明，行数估算准确，并不保证成本较低的计划实际执行得更快。反过来，即使成本模型能够描述当前运行条件，错误的行数也可能影响选择。下面用一个简化模型，说明这种影响在什么情况下会发生。

假设外层结果有 N 行，忽略两种方案共同承担的工作，它们的成本关系如下：

A：逐行查询，成本 C_A(N) = 40 × N
B：批量处理，成本 C_B(N) = 6,000 + 2 × N

这个模型独立于前面的实测数据，常数仅用于说明成本关系，不是 PostgreSQL 参数或实测毫秒数。A 的启动成本较低，但每增加一行的处理成本较高；B 有一部分固定成本，每增加一行的处理成本较低。两条曲线在约 158 行处相交。

按估算的 100 行计算，A 的成本为 4,000，低于 B 的 6,200；代入实际的 1,000 行，A 的成本变成 40,000，高于 B 的 8,000。估算行数与实际行数位于交点两侧，两种方案的成本高低关系因此发生变化。如果两者仍处于同一种方案成本更低的区间，即使行数存在误差，也可能选出合适的计划。

因此，基数估算有误差，不一定会选错计划；基数估算准确，也不保证计划最优。前面的实验中，联合统计修正了用户数的低估，而同一统计条件下的执行对照说明，成本模型仍可能选择实际执行更慢的方案。这两个问题需要分别分析。

4. 计划选择还受到哪些限制

从上面的查询可以看到，基数估算影响预计处理的数据量，成本模型决定如何比较不同的处理方式。不过，优化器能比较哪些计划、是否针对当前参数重新规划，也会影响最终选择。

4.1 更好的方案，也可能没有进入比较

如果没有 orders(user_id) 索引，本例中按用户查找订单的索引路径就不存在。连接表较多时，访问路径、连接顺序和连接算法会形成大量组合。优化器还需要控制查询规划本身的时间和内存开销，因此不能无限搜索。

PostgreSQL 使用 Path 结构表示规划阶段的候选路径，淘汰不占优势的路径，同时保留对后续操作有用的路径。例如，一条路径当前的成本略高，但能够提供有序结果，就可能省去后续的 Sort。优化器选定路径后才生成完整计划，不会先构造所有完整计划再逐个执行。PostgreSQL 优化器内部说明

连接数量达到相应阈值后，PostgreSQL 可以使用遗传查询优化器，在有限开销下搜索连接顺序。如果一个方案没有进入比较，即使它实际更快，优化器也无法选择它。这属于搜索范围的限制，单纯更新列统计不一定能解决。PostgreSQL Planner/Optimizer

4.2 复用的计划，未必适合当前参数

如果把查询换成 WHERE user_id = $1，普通用户与拥有大量订单的大客户可能适合不同路径。每次都根据参数重新规划有额外开销，复用计划可以减少这部分工作，却可能无法照顾每一种分布。

前面的 SQL 执行流程文章已经介绍过，预编译语句不一定始终复用同一份计划。PostgreSQL 可以结合本次参数生成定制计划（custom plan），也可以复用不针对本次具体参数值生成的通用计划（generic plan）。其他数据库的计划缓存规则可能不同。遇到同一条 SQL 只有某些参数执行较慢时，需要同时检查参数分布和实际使用的计划，不能只看 SQL 文本。PostgreSQL 预编译语句与计划复用

4.3 如何判断慢查询的问题来源

本文的查询完整读取结果，并在会话内关闭并行，以便比较行数。如果实际查询带有 LIMIT 或 EXISTS，下层节点可能提前停止，actual rows 小于完整执行时的估算并不一定意味着统计错误。never executed 表示节点没有执行，不能据此判断实际行数；并行计划也需要结合工作进程和汇总节点理解。

“更优”的目标同样需要明确。cost=启动成本..总成本 区分取得首行前的工作与完整运行的工作；只需要少量结果时，启动快但完整运行更贵的路径也可能合适。PostgreSQL EXPLAIN 基础

理解这些差异以后，就可以结合执行计划中的信息逐步分析：

锁等待、并发负载或客户端传输也可能让一次请求变慢。EXPLAIN ANALYZE 默认不向客户端发送原查询结果集，且自身带有观测开销，因此不能将其 Execution Time 直接等同于应用响应时间。判断优化器选错，需要有语义正确、当前可用、在可比条件下更快的候选作为依据。

5. 统计摘要还能告诉优化器什么

前面的案例通过常见值频率和国家、城市之间的相关性，解释了用户数为什么会被低估。实际查询还可能查找非常见值、筛选范围或连接不同的表，优化器需要借助其他统计摘要完成估算。接下来分别看这些估算使用了什么信息、依赖什么假设，以及误差可能从哪里产生。

5.1 非常见值：把剩余比例分给剩余不同值

pg_stats 中的 null_frac 表示空值比例，n_distinct 估计非空不同值数量，MCV 保存常见值及其占全部行的频率。n_distinct 为负数时表示相对于表行数的比例编码，例如 -1 表示估计不同值数与行数相同。

如果目标值不在 MCV 中，一种基础估算是：

选择率 ≈ (1 - 空值比例 - MCV 频率之和)
         / (非空不同值总数 - MCV 值的数量)

假设某一列有 1,000 种非空值，其中 10 个常见值占 40%，空值占 10%，剩余 990 种值共享另外 50%。某个非常见值的基础估算约为 0.50 / 990 = 0.000505。这是均匀分摊剩余数据的近似，实际估算器还可能进行边界修正；长尾部分严重倾斜时，它仍可能失真。PostgreSQL 等值选择率实现

5.2 范围条件：累计直方图的桶，再估算桶内比例

histogram_bounds 用桶边界描述分布，各桶覆盖的数据量近似相等。假设某一列没有空值，也没有单独统计的 MCV，十个等频桶恰好以 0、10、20……100 为边界。估算 < 35 时，累计前三个完整桶，再取第四个桶的一半：

选择率 ≈ 3 / 10 + (35 - 30) / (40 - 30) × 1 / 10
       = 0.35

等频不表示区间宽度相等，也不保证桶内部均匀。如果数据集中在某个桶的一小段区间，按桶内均匀分布推算出的比例就可能偏离实际。PostgreSQL 的直方图排除了单独统计的 MCV；存在 MCV 时，还要把满足范围的常见值频率与直方图覆盖部分的估计加权组合，避免重复计算。PostgreSQL 行数估算示例

5.3 等值连接：从两侧行数估算匹配的组合

普通内连接可以从结果规模来理解：连接输出基数 = 左侧基数 × 右侧基数 × 连接选择率。这里的连接选择率，是所有左右行组合中满足连接条件的比例，不要求执行器真的先生成笛卡尔积。

在忽略空值、连接键均匀分布、较小取值域被较大取值域覆盖的简化模型中，等值连接选择率可以近似为两侧连接键不同值数较大者的倒数。沿用本例数据，这相当于：

估算连接输出 = 10 × 1,000,000 × 1 / 10,000 = 1,000 行
实际连接输出 = 100 个用户 × 每人 100 条订单 = 10,000 行

这与本次计划吻合，但不能据此认定 PostgreSQL 内部只使用了这一条公式。真实估算还可能利用外键、唯一性和常见值等信息。连接结果的规模通常先在逻辑结果层估算，再用于比较物理算法。PostgreSQL 连接估算示例

如果两侧连接键分布不均，或较小的取值域未被较大的取值域覆盖，这个简化公式就可能偏离实际。因此，除了两侧输入行数，还需要检查连接键的分布与匹配关系。

5.4 组合条件：估算重叠的部分

多个过滤条件还需要估算它们的重叠部分。例如，在独立近似下，OR 条件的选择率可以写成 s(A) + s(B) - s(A) × s(B)。如果两个条件存在相关性，重叠比例就未必等于 s(A) × s(B)，组合后的行数估算也会受到影响。

统计陈旧、采样遗漏、桶内分布不均、表达式缺少适用统计，都可能让这些估算偏离实际。ANALYZE 改善的是数据摘要，无法消除有限摘要和近似模型本身的边界。

6. 回顾优化器的选择过程

现在可以把统计信息、基数估算、成本比较和执行结果重新放在一起，回顾优化器选择计划的过程：

在本例中，单列统计无法表达国家与城市的相关性，导致用户数被低估；联合统计修正了这一问题，但估算成本更低的计划，实际执行时间却更长。这说明，查询优化需要同时考虑数据分布、访问方式和运行条件。更新统计信息、调整查询或索引、评估成本参数，都需要针对具体原因进行。

优化器是在有限的信息和规划时间内，根据估算成本选择执行计划。理解它的工作机制，可以帮助我们把预计处理的数据量、计划需要完成的工作和实际执行结果对应起来，判断问题出在统计信息、成本估算，还是计划搜索与复用等环节。

附录：复现实验

可以下载完整实验包，其中包含造数 SQL、自动测量脚本、本文的原始测量记录、完整执行计划与运行说明。脚本依次记录统计修正前后的计划、预热、交替执行两种方案，再计算中位数和范围，新结果写入独立目录。

以下 SQL 可以用于逐步观察前面的实验。示例使用普通表，以便记录 shared 缓冲访问次数。请在独立、可销毁的本地测试数据库中执行，表名统一使用 optimizer_ 前缀，避免与应用业务表混淆。实验不需要应用数据。

本次环境是 PostgreSQL 18.3、shared_buffers=128MB、work_mem=4MB、effective_cache_size=4GB、seq_page_cost=1、random_page_cost=4、default_statistics_target=100。关闭 JIT 与并行只用于本次受控比较，不是生产调优建议。

SET max_parallel_workers_per_gather = 0;
SET jit = off;

CREATE TABLE optimizer_users (
  id integer PRIMARY KEY,
  country text NOT NULL,
  city text NOT NULL
);

CREATE TABLE optimizer_orders (
  id bigint PRIMARY KEY,
  user_id integer NOT NULL REFERENCES optimizer_users(id),
  status text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO optimizer_users
SELECT i,
  CASE WHEN i % 100 < 10 THEN 'CN' ELSE 'US' END,
  CASE WHEN i % 100 = 0 THEN 'Shanghai'
       WHEN i % 100 < 10 THEN 'Beijing'
       ELSE 'New York' END
FROM generate_series(1, 10000) AS s(i);

INSERT INTO optimizer_orders
SELECT i,
  (i % 10000) + 1,
  CASE WHEN i % 3 = 0 THEN 'paid' ELSE 'pending' END,
  timestamptz '2026-01-01 00:00:00+00' + i * interval '1 second'
FROM generate_series(1, 1000000) AS s(i);

CREATE INDEX optimizer_users_city_idx ON optimizer_users(city);
CREATE INDEX optimizer_orders_user_idx ON optimizer_orders(user_id);
ANALYZE optimizer_users;
ANALYZE optimizer_orders;

先检查单列统计与过滤结果。TIMING OFF 关闭逐节点计时以降低观测开销，仍保留整条语句的 Execution Time：

SELECT attname, null_frac, n_distinct,
       most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'optimizer_users';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM optimizer_users WHERE country = 'CN';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM optimizer_users WHERE city = 'Shanghai';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM optimizer_users
WHERE country = 'CN' AND city = 'Shanghai';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, o.status, o.created_at
FROM optimizer_users u
JOIN optimizer_orders o ON o.user_id = u.id
WHERE u.country = 'CN' AND u.city = 'Shanghai';

记录上述输出后，进入扩展统计阶段。下面按完整实验顺序执行正文中的统计修正；如果此前已经运行过同名 CREATE STATISTICS，不要重复创建：

CREATE STATISTICS optimizer_users_region_stats (mcv)
ON country, city FROM optimizer_users;
ANALYZE optimizer_users;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM optimizer_users
WHERE country = 'CN' AND city = 'Shanghai';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, o.status, o.created_at
FROM optimizer_users u
JOIN optimizer_orders o ON o.user_id = u.id
WHERE u.country = 'CN' AND u.city = 'Shanghai';

最后比较两种连接方式。下面是一个比较轮次，先运行自动选择的计划，再在事务内限制连接算法。SET LOCAL 设置在事务结束后恢复，ROLLBACK 结束的是本次只读对照事务：

-- 自动选择的计划：检查本机是否得到 Hash Join。
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, o.status, o.created_at
FROM optimizer_users u
JOIN optimizer_orders o ON o.user_id = u.id
WHERE u.country = 'CN' AND u.city = 'Shanghai';

-- 受控 Nested Loop：仅在本次事务限制连接算法，并检查实际计划。
BEGIN;
SET LOCAL enable_hashjoin = off;
SET LOCAL enable_mergejoin = off;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, o.status, o.created_at
FROM optimizer_users u
JOIN optimizer_orders o ON o.user_id = u.id
WHERE u.country = 'CN' AND u.city = 'Shanghai';
ROLLBACK;

前两轮作为预热，再记录 10 轮，每轮交换自动计划与受控 Nested Loop 的先后顺序，分别计算中位数与范围；同时检查计划、实际行数与块读取是否保持可比。附件中的 benchmark.cjs 自动执行这套测量流程，recorded/ 保存本文原始记录，新的测量不会覆盖它们。

统计抽样、版本、机器和配置可能改变计划及耗时。如果本机没有自动选择 Hash Join，应记录这个差异，并根据实际计划解释结果，无须为了得到相同计划而修改全局参数。EXPLAIN ANALYZE 包含观测开销，默认不会向客户端发送原查询结果集，因此这里比较的是数据库中的执行时间，不是应用端完整响应时间。

实验完成后，在这一个测试库内清理本文创建的对象并恢复会话设置：

DROP TABLE optimizer_orders;
DROP TABLE optimizer_users; -- 相关索引与扩展统计随表删除
RESET max_parallel_workers_per_gather;
RESET jit;

参考资料

• PostgreSQL 18：Planner/Optimizer
• PostgreSQL 优化器内部说明
• PostgreSQL 18：Statistics Used by the Planner
• PostgreSQL 18：pg_stats
• PostgreSQL 18：Row Estimation Examples
• PostgreSQL 18：Multivariate Statistics Examples
• PostgreSQL 18：CREATE STATISTICS
• PostgreSQL 成本估算实现 costsize.c
• PostgreSQL 18：Query Planning
• PostgreSQL 18：Resource Consumption
• PostgreSQL 18：Using EXPLAIN
• PostgreSQL 18：EXPLAIN
• PostgreSQL 18：PREPARE