PostgreSQL中统计信息对查询性能的影响
原文地址：https://boringsql.com/posts/postgresql-statistics/
一、为什么查询会变慢？
- 查询性能取决于执行计划，而执行计划的质量依赖于统计信息的准确性。
- 当统计信息过时（如批量数据加载、迁移、快速增长或VACUUM跟不上），规划器会基于错误的数据规模（如预估500行，实际25000行）做出错误决策（如误选Nested Loop），导致性能崩溃。
二、规划器读取的两个核心元数据来源
pg_class（表级统计）：relpages：表占用的磁盘页数（影响顺序扫描成本）。reltuples：估计的活跃行数（影响连接、聚合等操作的估算）。relallvisible：可见性映射页数（影响仅索引扫描）。- 注意：这些值是由ANALYZE估算的，不是实时计数。
pg_statistic（通过视图pg_stats查看，列级统计）：null_frac：NULL值比例。avg_width：平均字节宽度。n_distinct：唯一值数量。most_common_vals（MCV）和most_common_freqs：最常见值及其频率。histogram_bounds：直方图边界（用于范围查询）。correlation：物理存储顺序与逻辑顺序的相关性（影响索引扫描成本）。
三、选择率（Selectivity）如何估算行数
- 公式：估算行数 = 总行数 × 选择率。
- 等值查询（=）：优先查MCV列表匹配频率；若不在列表中，则假设均匀分布在剩余数据中。
- 范围查询（>, <）：利用
histogram_bounds，计算查询覆盖的桶数比例（假设数据在桶内线性分布）。 - 模式匹配（LIKE）：
- 对于
'%middle%'这类无前缀匹配，规划器只能使用硬编码的默认选择率（0.5%）。 - 对于
'prefix%'这类有前缀匹配，可降级为范围查询利用直方图，精度较高。
- 对于
- 连接估算：依赖
n_distinct，公式为(左表行数 × 右表行数) / max(左表唯一值, 右表唯一值)。
四、当没有统计信息时（回退默认值）
在没有统计信息（如新表、未运行ANALYZE）或特定场景下，PostgreSQL会使用硬编码默认值：
- 等号、BETWEEN、LIKE、IS NULL：默认选择率0.5%
- 不等号（>, <）：默认选择率33.3%
- IS NOT NULL：默认选择率99.5%
无统计信息的常见陷阱：
- CTE和子查询（未被物化时）。
- 临时表（autovacuum不处理）。
- 外部表。
- WHERE子句中的计算表达式（如
WHERE amount * 1.1 > 500），除非创建表达式索引或扩展统计。
五、ANALYZE是如何工作的
- 采样机制：ANALYZE并非读取全表，而是基于
default_statistics_target（默认100）进行采样。- 采样行数 = 300 ×
default_statistics_target= 30,000行。 - 对于大表，采样比例会非常小（如5000万行表只采0.06%）。
- 采样行数 = 300 ×
- 计算过程：
- 随机选择一批页面，读取其中所有活跃行。
- 计算NULL比例、平均宽度。
- 排序并构建MCV列表（高频值）和直方图（剩余值均匀分桶）。
- 计算相关性。
- 写入：结果写入
pg_statistic，并更新pg_class中的relpages/reltuples。
六、如何控制统计质量
- 调整
default_statistics_target：- 提高目标值可增加MCV数量、直方图精度和采样行数，但会消耗更多存储和规划时间。
- 可以针对特定列设置：
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
- 使用扩展统计：
- 解决列之间的相关性（如城市=‘ Edinburgh’和国家=‘UK’），防止规划器独立估算导致严重偏差。
- 类型包括：
dependencies（函数依赖）、ndistinct（组合唯一值）、mcv（组合最常见值）。 - 创建方法：
CREATE STATISTICS ... ON (列1, 列2) FROM 表;
七、诊断与总结
- 当查询慢时，首先使用
EXPLAIN ANALYZE对比预估行数和实际行数。 - 如果偏差超过10倍，通常意味着统计信息不准。
- 解决路径：运行
ANALYZE-> 调整特定列的统计目标 -> 针对多列创建扩展统计。 - 核心结论：规划器的决策质量完全取决于读取的统计信息。当估算错误时，不要责怪规划器，而应检查它所依据的数据是否准确。
