DeepSeek总结PostgreSQL中统计信息对查询性能的影响

PostgreSQL中统计信息对查询性能的影响
原文地址：https://boringsql.com/posts/postgresql-statistics/

一、为什么查询会变慢？

• 查询性能取决于执行计划，而执行计划的质量依赖于统计信息的准确性。
• 当统计信息过时（如批量数据加载、迁移、快速增长或VACUUM跟不上），规划器会基于错误的数据规模（如预估500行，实际25000行）做出错误决策（如误选Nested Loop），导致性能崩溃。

二、规划器读取的两个核心元数据来源

1. pg_class（表级统计）：

   • relpages：表占用的磁盘页数（影响顺序扫描成本）。
   • reltuples：估计的活跃行数（影响连接、聚合等操作的估算）。
   • relallvisible：可见性映射页数（影响仅索引扫描）。
   • 注意：这些值是由ANALYZE估算的，不是实时计数。

2. 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%）。
• 计算过程：
  1. 随机选择一批页面，读取其中所有活跃行。
  2. 计算NULL比例、平均宽度。
  3. 排序并构建MCV列表（高频值）和直方图（剩余值均匀分桶）。
  4. 计算相关性。
• 写入：结果写入pg_statistic，并更新pg_class中的relpages/reltuples。

六、如何控制统计质量

1. 调整default_statistics_target：
   • 提高目标值可增加MCV数量、直方图精度和采样行数，但会消耗更多存储和规划时间。
   • 可以针对特定列设置：ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
2. 使用扩展统计：
   • 解决列之间的相关性（如城市=‘ Edinburgh’和国家=‘UK’），防止规划器独立估算导致严重偏差。
   • 类型包括：dependencies（函数依赖）、ndistinct（组合唯一值）、mcv（组合最常见值）。
   • 创建方法：CREATE STATISTICS ... ON (列1, 列2) FROM 表;

七、诊断与总结

• 当查询慢时，首先使用EXPLAIN ANALYZE对比预估行数和实际行数。
• 如果偏差超过10倍，通常意味着统计信息不准。
• 解决路径：运行ANALYZE-> 调整特定列的统计目标 -> 针对多列创建扩展统计。
• 核心结论：规划器的决策质量完全取决于读取的统计信息。当估算错误时，不要责怪规划器，而应检查它所依据的数据是否准确。