MIT Notes by Ron

中文

English

中文

English

Appearance

PostgreSQL 查询系列 — 2. 统计信息 ​

原文：https://habr.com/en/companies/postgrespro/articles/576100/ （作者 Egor Rogov，PostgresPro）

引言 ​

上一篇文章讲到，优化器选择执行计划的整个决策链条最终都落在"这一步大概会产出多少行"这个基数估算问题上，而基数估算的原材料就是统计信息。本篇文章围绕 ANALYZE 收集了哪些统计信息、这些统计信息存在哪里、每一项分别解决什么估算问题，以及 PostgreSQL 10 之后引入的"扩展统计"如何弥补朴素统计模型在处理列间相关性时的短板，做了系统的梳理。

表级别的粗粒度统计 ​

系统目录 pg_class 里保存着几个描述表整体规模的字段，它们是优化器估算"扫一整张表大概要付出多少代价"的起点：

  • reltuples：表的估计行数；
  • relpages：表在磁盘上占用的页数；
  • relallvisible：可见性图（visibility map）中被标记为"整页可见"的页数，这个值会直接影响 Index-Only Scan 是否需要回表。

这几个数字并不是实时精确统计出来的，而是在执行 ANALYZE（包括 autovacuum 自动触发的 ANALYZE）以及 VACUUM FULL、CLUSTER、CREATE INDEX 等命令时"顺带"重新计算得到的。ANALYZE 并不会去读整张表，而是做分页随机抽样：默认会抽取大约 300 × default_statistics_target 行（default_statistics_target 默认为 100，所以默认约抽样 30000 行），这些行是从大致相同数量的随机页面中采集出来的。这意味着表越大，抽样比例越低，统计信息本质上只是一个近似值。

列级别统计：pg_stats 视图 ​

真正描述"某一列的值长什么样"的信息存放在系统目录 pg_statistic 中，但这张表的字段设计比较底层、不直观，日常查看和理解建议使用它的可读视图 pg_stats。pg_stats 里针对每一列会给出以下几项关键统计量：

空值比例（null_frac） ​

表示这一列里 NULL 值所占的比例。文章举了一个很贴切的例子：航班表里"起飞时间"这一列，对于那些尚未真正起飞（或被取消）的航班，这个字段就是 NULL，也就是所谓的"未测量"值。优化器在估算 WHERE col IS NULL 或者反过来估算"非 NULL 谓词能保留多少行"时，直接用总行数乘以 null_frac（或 1 - null_frac）。

不同值个数（n_distinct） ​

描述这一列大概有多少个不同的取值。这里有一个容易让人困惑的编码约定：

  • 如果 n_distinct 是正数，表示"大概有这么多个不同值"（一个绝对数字）；
  • 如果 n_distinct 是负数，表示的是一个比例——例如 -1 表示"这一列的值全部互不相同"（每行一个独立值，比如主键列），-0.5 表示"不同值的数量大约是总行数的一半"。

之所以在"高基数"场景下用比例而不是绝对值，是因为当不同值的数量占总行数的比例较高（文章给出的经验阈值是超过 10%）时，用比例表示的统计量在表增删数据后依然相对稳定，不需要频繁重新 ANALYZE 来维持准确度；反之如果列的基数本来就低（比如状态字段只有几个取值），用绝对个数更稳。

高频值列表（most_common_vals / most_common_freqs） ​

如果一列的值分布并不均匀——某些值出现得特别频繁，另一些则很少见——单纯假设"均匀分布"会让选择率估算出现很大偏差。为此 PostgreSQL 会把抽样中出现频率最高的一批值连同它们各自的出现频率，分别存进 most_common_vals 和 most_common_freqs 这两个平行数组里。当查询条件命中的正好是某个高频值时，优化器可以直接查表得到精确得多的选择率，而不必依赖假设。这两个数组的最大长度也受 default_statistics_target 控制。

直方图（histogram_bounds） ​

对于不适合塞进 MCV 列表的、剩余的"长尾"值，PostgreSQL 用等频直方图来近似描述它们的分布：把值域划分成若干个桶，每个桶里包含的（抽样）行数大致相等，但只存储桶的边界值，不存储每个桶具体有多少行（因为设计上每个桶行数都差不多）。对于范围类型的查询条件（如 col > x 或 col BETWEEN a AND b），优化器就可以用这些边界值做线性插值，估算出条件覆盖了直方图的哪一段、对应多大比例的行。

平均宽度（avg_width） ​

记录变长类型（比如 text、bytea）在这一列上的平均字节数，主要用于估算排序、哈希等需要缓冲数据的操作大概要占用多少内存。

物理/逻辑顺序相关性（correlation） ​

衡量这一列的取值顺序与其在磁盘上物理存储顺序的吻合程度，取值范围是 −1 到 1。接近 ±1 说明数据在物理上几乎是按这一列的值排好序存放的（比如按自增主键插入、几乎不更新的表），接近 0 说明物理顺序和逻辑顺序几乎没有关系。这个指标对索引扫描的代价估算影响很大：相关性高时，按索引顺序访问对应的表页也大致是顺序 I/O，代价接近顺序扫描；相关性低时，同样的索引扫描会退化成大量随机 I/O，代价显著上升（这个话题会在第 4 篇"索引扫描"里详细展开）。

扩展统计（PostgreSQL 10+）：应对列间相关性 ​

上面这些统计都是"单列"的，隐含的假设是各列之间相互独立。但现实中许多列之间存在明显的相关性或函数依赖，单列统计乘积会严重低估或高估联合条件的选择率。为此 PostgreSQL 10 起引入了 CREATE STATISTICS 语句来创建"扩展统计对象"，覆盖以下几种场景：

函数依赖（dependencies） ​

当一列的值几乎能唯一确定另一列的值时（比如"航班号"基本决定了"出发机场"），如果按独立性假设分别计算两个条件的选择率再相乘，往往会严重低估联合条件的实际选择率（低估结果行数）。函数依赖统计会记录一个 0 到 1 之间的"依赖度"数值，供优化器在估算联合等值条件时做修正：

sql
CREATE STATISTICS flights_dep (dependencies)
ON flight_no, departure_airport FROM flights;

多列不同值计数（ndistinct） ​

记录多个列组合在一起时，大概有多少种不同的取值组合。这对涉及多列 GROUP BY 时估算分组数特别有用——如果按各列独立假设去乘，往往会大幅高估实际的组合数（因为很多组合根本不会同时出现，或者出现频率极不均衡）。

多列高频值列表（MCV） ​

和单列的 MCV 列表类似，但存储的是"列值组合"及其联合出现频率，用于在多个相关列同时出现在过滤条件中、且值分布明显倾斜（skewed）时提供比独立性假设更准的估计。

表达式统计（PostgreSQL 14+） ​

如果查询条件不是直接作用在某一列上，而是作用在一个函数/表达式的结果上（比如按时区转换后再取月份），默认情况下优化器对这类"看不懂"的表达式条件只能给出一个非常粗糙的兜底选择率（约 0.5%）。PostgreSQL 14 起可以针对表达式本身建立统计对象：

sql
CREATE STATISTICS flights_expr ON (
  extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow')
) FROM flights;

这样优化器就能像对待普通列一样，为这个表达式维护 MCV、直方图等统计，从而给出精确得多的估算。

统计信息的自动兜底与手工调优 ​

自动兜底：即便两次 ANALYZE 之间表发生了大量增删，reltuples 短时间内不会重新计算，但优化器并不会完全"失明"——它会按当前表文件的真实大小相对于上次记录的 relpages 的变化比例，去等比例缩放 reltuples，从而在没有重新 ANALYZE 的情况下，依然能得到一个不至于离谱的行数估计，直到下一次 ANALYZE 把统计刷新为止。

手工调优的两个入口：

  • ALTER TABLE ... ALTER COLUMN col SET STATISTICS n：为某一列单独调高（或调低）抽样力度和 MCV/直方图数组长度，而不必对整张表、整个数据库都调整 default_statistics_target。适合那些统计特别关键、默认精度不够用的列。
  • ALTER TABLE ... ALTER COLUMN col SET (n_distinct = value)：当自动估计的 n_distinct 明显不靠谱时（常见于抽样比例低、但该列基数其实很高的大表），可以手工写死一个更符合实际的值。

关键参数：default_statistics_target ​

这个参数（默认 100）同时控制着两件事：一是 ANALYZE 抽样的行数（大约 300 倍于该值），二是 MCV 列表和直方图数组的最大长度上限。文章提醒不要盲目地把这个参数调得很大：抽样越大、统计维护和存储的开销越高，而在实践中，MCV 列表 + 直方图这套组合，即便面对基数很大的表，通常也已经能提供足够用的估算精度，"越大越好"并不成立，调整这个参数应该是针对具体列、具体问题的定向操作，而不是全局蛮力加大。

小结 ​

统计信息是优化器"猜"行数的全部依据：表级的 reltuples/relpages 决定了整表扫描代价的量级；列级的 null_frac、n_distinct、MCV 列表、直方图、avg_width、correlation 分别对应缺失值估算、基数估算、倾斜分布下的精确匹配估算、范围条件估算、内存估算、索引扫描代价估算等具体问题；而 PostgreSQL 10+ 的扩展统计（函数依赖、多列 ndistinct、多列 MCV、表达式统计）则是对"列与列之间并非相互独立"这一现实的补丁。所有这些统计都只是基于随机抽样的近似值，default_statistics_target 控制着这份近似的精细程度，ANALYZE 的执行时机决定着这份近似的新鲜程度——理解这套体系，才能理解为什么有的查询计划"猜错了"，以及应该往哪个方向去修（调大统计粒度、建扩展统计，还是先老老实实跑一次 ANALYZE）。