PostgreSQL 架构原理第四期：查询优化器 —— 统计信息与代价模型

引言

在前三期的基础上，我们已经了解了 PostgreSQL 的进程模型、存储引擎以及事务并发控制。从用户提交一条 SQL 到真正执行，中间有一个至关重要的环节——查询优化器。它负责在众多可能的执行路径中，找出预计代价最小的那个执行计划。优化器之所以能做出相对准确的决策，核心依赖于两样东西：表的统计信息 和 代价模型。

本期我们将深入剖析：

统计信息的收集与存储：ANALYZE 命令、pg_statistic 系统表、pg_class 中的基本统计
最常见的数据分布：NULL 分数、唯一值数量、高频值（MCV）、直方图
代价模型中的启动代价、运行代价、总代价以及各种操作的代价估算公式
单表扫描（顺序扫描、索引扫描、位图扫描）的代价计算
连接操作的代价估算（嵌套循环、哈希连接、归并连接）
统计信息过时或不准时的应对策略

一、统计信息的收集：ANALYZE 命令

统计信息是优化器的“眼睛”。PostgreSQL 通过 ANALYZE 命令（包含在 VACUUM ANALYZE 或自动 autovacuum 中）收集表和索引的统计信息。

1.1 基本统计存储位置

pg_class：存储表级和索引级的全局统计，如：
- reltuples：表的总行数估计值
- relpages：表的磁盘页数（1 页 = 8KB）
- relallvisible：可见性映射中标记为全部可见的页数
- relfrozenxid：表的冻结阈值
pg_statistic：存储每个列的具体数据分布统计。为了安全，普通用户只能通过视图 pg_stats 查看，其内容经过脱敏（例如去除隐私数据）。

1.2 ANALYZE 的工作原理

ANALYZE 并不扫描全表（除非表很小），而是采用采样方法：

从每个表或索引中随机抽取约 30000 行（由 default_statistics_target 控制，默认 100，实际采样行数为 300 × 目标值，因为内部乘了 300 的因子）。
对每个目标列进行统计：
- null_frac：NULL 值比例
- n_distinct：不同值的数量（如果为负数，表示比例；例如 -0.1 表示大约有 0.1 * 总行数）
- most_common_vals 和 most_common_freqs：最常见的值及其出现频率
- histogram_bounds：等频直方图边界值（当值种类超过一定数量时，不存储 MCV 而使用直方图）
- correlation：物理行序与逻辑值的相关性（-1 到 1），影响索引扫描代价

采样完成后，将统计信息存入 pg_statistic 并更新 pg_class.reltuples 和 relpages。

1.3 统计信息收集参数

default_statistics_target：默认 100。增大该值会提高采样行数，得到更精确的统计，但 ANALYZE 时间和内存消耗会增加。
可以为特定列设置更高的目标：ALTER TABLE t ALTER COLUMN c SET STATISTICS 500;
自动 ANALYZE 由 autovacuum 触发，当表修改行数达到一定阈值时自动运行。

二、代价模型的基本概念

PostgreSQL 采用基于代价的优化（cost-based optimization），为每个可能的执行计划节点计算一个浮点数值代价。代价的单位并不是真实的时间，而是磁盘顺序页读取的耗时作为基准单位。其他操作（如 CPU 处理、随机页读取）的代价都相对于此进行量化。

2.1 代价的组成部分

每个计划节点都包含三个主要代价字段：

startup_cost：获取第一条返回行之前需要付出的代价。例如对索引扫描，需要先读取索引找到第一匹配项。
total_cost：获取所有行（假设所有行都被检索）的总代价。
run_cost = total_cost – startup_cost，通常不直接显示，但内部计算会用到。

优化器最终选择 total_cost 最小的完整计划树，但当使用游标或限制子句时，startup_cost 可能会更重要（例如希望尽快返回第一行）。

2.2 代价参数

代价公式中可配置的成本因子参数（均位于 postgresql.conf）：

参数	默认值	含义
`seq_page_cost`	1.0	顺序页读取代价（基准）
`random_page_cost`	4.0	随机页读取代价（通常设为 1.5~2.0 对于 SSD）
`cpu_tuple_cost`	0.01	处理一个元组的 CPU 开销
`cpu_index_tuple_cost`	0.005	索引扫描时处理一个索引元组的开销
`cpu_operator_cost`	0.0025	执行一个操作符或函数的开销（如 `=`、`+`）
`parallel_setup_cost`	1000	并行查询启动子进程的代价
`parallel_tuple_cost`	0.1	并行查询中传输一个元组的代价

这些参数可以根据硬件特性调整，例如在全闪存存储上降低 random_page_cost 可以提高索引扫描的竞争力。

2.3 代价计算公式举例

处理一个元组的 CPU 代价通常为 cpu_tuple_cost。处理一个 WHERE 子句中的操作符代价为 cpu_operator_cost 乘以操作符个数。读取一个数据页的代价根据顺序或随机读取而不同。

三、单表扫描的代价估算

3.1 顺序扫描（Seq Scan）

顺序扫描整个表，读取所有页面。代价公式：

startup_cost = 0
run_cost = (pages × seq_page_cost) + (tuples × cpu_tuple_cost)
total_cost = run_cost

其中 pages = pg_class.relpages，tuples = pg_class.reltuples。

如果表有 WHERE 子句，优化器会计算选择率（selectivity），并调整实际返回的元组数。只有满足条件的元组才需要被处理，所以 run_cost 中的 cpu_tuple_cost 需要乘以选择率，但页读取代价仍然需要读取整表（除非顺序扫描能够提前终止，但通常不会）。PostgreSQL 对于顺序扫描不会跳过页面，因此 pages 始终全读。

选择率的估算依赖于统计信息。例如 WHERE col = constant，若 col 有 MCV 统计，则选择率等于该常量值在 MCV 中的频率；若无，则假设均匀分布 1 / n_distinct。

3.2 索引扫描（Index Scan）

索引扫描先读取索引获得 TID（页号+行指针），然后根据 TID 直接访问堆表页面。代价估算包括：

索引访问代价：读取索引页（随机 I/O）以及处理索引元组的 CPU 代价。
堆表访问代价：每个 TID 对应一个随机 I/O 读取堆页。

简化公式：

startup_cost = 索引查找的启动代价（如 B-tree 根到叶）
run_cost = （索引页数 × random_page_cost） + 
           （匹配的索引元组数 × cpu_index_tuple_cost） +
           （堆表元组数 × (cpu_tuple_cost + random_page_cost)）

需要注意：如果一个索引扫描返回多个元组，且这些元组位于同一个堆页上，PostgreSQL 只计算一次页读取代价，因为可以缓存。实际计算使用更精细的模型，考虑 TID 的聚集度（由 correlation 反映）。

correlation 影响：如果索引键的顺序与物理存储顺序高度相关（correlation 接近 1 或 -1），那么连续访问的 TID 很可能位于相近的堆页，所以随机 I/O 可能退化为顺序 I/O。优化器会使用 random_page_cost 与 seq_page_cost 之间的插值来调整。

3.3 位图扫描（Bitmap Scan）

当索引返回大量元组时，位图扫描可以高效减少随机 I/O。步骤如下：

扫描索引，构造内存中的位图（每个堆页对应一个比特位）。
根据位图中标记的页面，按物理顺序读取堆页，并检查元组。

代价估算分为两部分：建立位图的代价（索引扫描部分）和根据位图扫描堆表的代价（顺序读取标记的页面）。

总代价 = 索引扫描代价 + 堆表扫描代价（按顺序读页，每页 seq_page_cost）

位图扫描适合当选择率中等的情况，避免每个 TID 都触发随机 I/O。

四、连接操作的代价估算

三种主流连接算法：嵌套循环（Nested Loop）、哈希连接（Hash Join）、归并连接（Merge Join）。

4.1 嵌套循环连接

对于外层表的每一行，扫描内层表所有匹配行。代价公式：

total_cost = outer_startup + outer_run + 
             outer_tuples × (inner_startup + inner_run × selectivity)

其中 selectivity 是内层表在外层每行条件下的匹配率。嵌套循环不需要内层表数据预先加载，适合小型内层表或存在高效索引（内层扫描可用索引加速）。

4.2 哈希连接

先读取内层表，创建哈希表（键为连接列）；然后扫描外层表，探测哈希表。通常在内存中进行，超过 work_mem 时会溢写到磁盘。

代价估算：

内层表扫描 + 构建哈希表（写哈希表，CPU 与内存）
外层表扫描 + 探测哈希表

total_cost = 内层表代价 + 外层表代价 + 哈希构建和探测的 CPU 代价（根据行数）

哈希连接适合内层表较大且没有索引，以及等值连接。

4.3 归并连接

要求连接列已排序。如果两个表都按连接列排序，则可以像归并两个有序列表那样连接。代价 = 两个表的排序代价（如果未提前排序） + 归并本身。

归并本身的代价：cpu_tuple_cost 乘以两个表总行数，加上一些比较开销。归并连接适合大数据量且数据已有序的场景。

4.4 连接顺序与搜索空间

当多个表连接时，优化器需要搜索不同的连接顺序（左深树、右深树、浓密树）。from_collapse_limit 和 join_collapse_limit 控制是否展开子查询连接。对于超过 12 个表的连接，优化器会采用遗传算法（GEQO），以近似最优。

五、统计信息过时与应对

统计信息并非实时更新：ANALYZE 是采样和异步的。当表频繁更新时，pg_class.reltuples 和列分布可能严重过时，导致优化器选择错误计划。

5.1 识别统计过时

查看执行计划中的 actual rows 与 estimated rows 差距巨大。
查询 pg_stat_user_tables 中的 n_mod_since_analyze，表示自上次 ANALYZE 后修改的行数。当该值超过一定阈值（如 10%）时，建议重新 ANALYZE。

5.2 强制更新统计

手动执行 ANALYZE [table]。
调整 autovacuum_analyze_scale_factor（默认 0.1）和 autovacuum_analyze_threshold（默认 50），让自动 ANALYZE 更敏感。

5.3 扩展统计信息

PostgreSQL 10+ 引入了扩展统计（CREATE STATISTICS），用于解决列之间相关性强导致联合选择率被低估的问题。

例如 WHERE city = 'Beijing' AND zipcode = '100000'，如果 city 和 zipcode 高度相关，独立假设会低估选择率（乘积太大）。扩展统计可以收集函数依赖或多变量 MCV/直方图，帮助优化器更准确估算。

六、实践：如何分析和调优

6.1 使用 `EXPLAIN` 分析代价

EXPLAIN [ANALYZE] 显示计划节点的代价估算与实际执行时间。
关注 cost=xxx..xxx 中的启动代价和总代价。
buffers 参数（需要开启 track_io_timing）显示实际读写的块数，可对比估算的页数。

6.2 常见优化器误区

低估索引扫描成本：当 random_page_cost 过高时，索引扫描可能劣于顺序扫描。对于 SSD，建议设置 random_page_cost = 1.0 或 1.1。
高估并发查询：代价模型是单查询视角，不考虑缓存和并发争用。
参数敏感：某些查询在特定参数值下会走不同计划，因为选择率估算受常数值影响。可以使用 pg_hint_plan 或 plan_cache_mode 来干预。

6.3 强制计划

虽然 PostgreSQL 没有像 Oracle 的 HINT 语法，但可以通过以下方式影响计划：

设置 enable_* 参数（enable_seqscan、enable_indexscan 等）为 off。
调整 random_page_cost 等全局成本因子。
使用 SET LOCAL 在会话级别修改参数。
使用扩展 pg_hint_plan 插入伪注释。

总结与预告

本期我们详细解剖了 PostgreSQL 查询优化器的核心：

ANALYZE 收集列统计信息（NULL 分数、不同值数量、MCV、直方图、相关性等），存储在 pg_statistic 中。
代价模型以顺序页读取为基准，使用可配置的成本因子计算顺序扫描、索引扫描、位图扫描的代价。
连接操作（嵌套循环、哈希连接、归并连接）各有不同代价公式，优化器会搜索最优连接顺序。
统计信息过时会导致劣质计划，可以通过手动 ANALYZE 或扩展统计来改善。

理解优化器如何工作，有助于解读执行计划、发现选择性估算错误，并合理调整数据库参数。

下一期我们将进入 备份与恢复 的主题，详细讲解 PostgreSQL 的物理备份（PITR）、逻辑备份、流复制与高可用架构，以及如何设计可靠的容灾方案。

思考题

对于一个大表，WHERE status = 'active'，如果 status 列只有两个值（’active’, ‘inactive’），分别占总行数的 10% 和 90%，索引扫描是否总是优于顺序扫描？为什么？
为什么 PostgreSQL 的 n_distinct 如果是负数表示比例？设计成负数的好处是什么？
在执行 EXPLAIN ANALYZE 时，你发现实际返回的行数比估计行数多 10 倍，但执行时间仍然很快，这需要处理吗？为什么？