Postgresql-SQL-优化：统计信息相关

1 统计信息存储视图

PostgreSQL 的统计信息主要存储在以下系统目录视图（catalog views）中：

1.1 表级统计信息

• pg_stat_all_tables
  所有表的访问统计（包括系统表）

   

  SELECT * FROM pg_stat_all_tables WHERE relname = 'your_table';

  • 关键字段：
    seq_scan (顺序扫描次数)
    idx_scan (索引扫描次数)
    n_tup_ins/del/upd (增/删/改行数)
    last_analyze (最后一次手动 ANALYZE 时间)
    last_autoanalyze (最后一次自动 ANALYZE 时间)

• pg_statio_all_tables
  所有表的 I/O 统计

   

  SELECT * FROM pg_statio_all_tables WHERE relname = 'your_table';

  • 关键字段：
    heap_blks_read (堆块读取次数)
    idx_blks_read (索引块读取次数)

1.2. 列级统计信息

• pg_stats（核心优化器统计视图）

   

  SELECT * FROM pg_stats WHERE tablename = 'your_table';

  • 关键字段：
    attname (列名)
    null_frac (空值比例)
    avg_width (平均字节宽度)
    n_distinct (唯一值数量)
    most_common_vals (MCV：最常见值列表)
    most_common_freqs (MCF：MCV 出现频率)
    histogram_bounds (直方图边界)

• pg_statistic（底层存储，需用 pg_stats 解读）

1.3. Schema 级统计信息

• 通过聚合表级统计获取：

   

  SELECT schemaname, 
         SUM(seq_scan) AS total_seq_scans,
         SUM(idx_scan) AS total_idx_scans
  FROM pg_stat_all_tables
  GROUP BY schemaname;

---

2 统计信息收集方法

使用 ANALYZE 命令收集统计信息，支持不同粒度：

2.1. 收集整个数据库

ANALYZE;  -- 分析当前数据库所有表

2.2. 收集特定表

ANALYZE table_name;  -- 分析单个表
ANALYZE VERBOSE table_name; -- 显示进度信息

2.3. 收集特定列

ANALYZE table_name (column1, column2, ...); 

2.4. 收集整个 Schema

ANALYZE SCHEMA schema_name;  -- 分析整个模式

2.5. 自动收集（推荐）

通过 autovacuum 进程自动维护：

-- 检查 autovacuum 状态
SHOW autovacuum;  -- 默认 ON

-- 调整特定表的分析频率
ALTER TABLE your_table SET (
  autovacuum_analyze_scale_factor = 0.01,  -- 1% 数据变动后触发
  autovacuum_analyze_threshold = 1000      -- 或超过1000行变动
);

---

3 统计信息管理技巧

3.1. 手动更新统计信息场景

• 大数据加载后（如 ETL 作业结束）

• 查询计划突然变差时

• 修改了 random_page_cost 等优化器参数后

3.2. 检查统计信息时效性

SELECT relname, 
       last_analyze, 
       last_autoanalyze 
FROM pg_stat_all_tables 
WHERE relname = 'your_table';

3.3. 控制分析深度

-- 临时增加采样率（默认 300 * 统计目标）
SET default_statistics_target = 500;  -- 提高统计精度（范围 1-10000）
ANALYZE table_name;

-- 永久设置列级统计目标
ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 1000;

---

4 示例工作流

4.1 大数据加载后的统计更新

-- 1. 禁用 autovacuum 避免干扰
ALTER TABLE large_table SET (autovacuum_enabled = off);

-- 2. 加载数据（COPY 或 INSERT）
COPY large_table FROM '/path/to/data.csv';

-- 3. 手动收集全表统计（提高采样率）
SET default_statistics_target = 1000;
ANALYZE VERBOSE large_table;

-- 4. 重新启用 autovacuum
ALTER TABLE large_table SET (autovacuum_enabled = on);

针对性分析高筛选率列

-- 只分析常用于 WHERE 条件的列
ANALYZE orders (customer_id, order_date);

---

5 关键总结

操作类型	命令示例	使用场景
全库分析	ANALYZE;	维护窗口批量更新
单表分析	ANALYZE orders;	重点表优化
列级分析	ANALYZE orders (customer_id);	关键查询列优化
Schema 分析	ANALYZE SCHEMA sales;	业务模块级维护
查看统计信息	SELECT * FROM pg_stats WHERE tablename='orders';	验证分析结果
调整采样率	SET default_statistics_target=1000;	提升复杂查询的统计精度

⚠️ 注意事项：

1. ANALYZE 需要表级读锁，避免在高峰时段对大型表操作

2. 统计信息不实时更新，大数据变更后需手动触发

3. 扩展统计信息（如多列统计）需单独通过 CREATE STATISTICS 创建

 

6 扩展统计信息

PostgreSQL 从版本 10 开始显著增强了其扩展统计信息的功能，以帮助优化器更准确地估计涉及多列相关性、表达式或特定数据类型（如数组、JSON）的查询的选择性。这些扩展对于处理传统单列统计信息无法很好建模的复杂查询条件至关重要。

以下是 PostgreSQL 10 到 16 版本中引入的主要扩展统计信息类型及其应对的场景：

1. 多列 MCV 列表 (mcv)

   • 引入版本：PostgreSQL 10

   • 场景： 解决多列组合值分布不均的问题。当两个或多个列的值高度相关或频繁以特定组合出现时，传统的依赖统计(dependencies)可能不够精细。

   • 问题示例：

     • 列 country 和 city：country='USA' 和 city='New York' 的组合非常常见，但 country='USA' 和 city='London' 的组合根本不存在。单列统计或依赖统计可能无法精确捕获这种常见组合的频率或排除不可能的组合。

     • 列 model 和 manufacturer：特定 manufacturer 只生产特定 model。

   • 作用： 直接存储最常见（Most Common Value）的列组合及其出现频率。这使优化器能更准确地估算形如 WHERE col1 = A AND col2 = B 或 WHERE (col1, col2) IN ((A, B), (C, D)) 等条件的联合选择性。

   • 创建命令：

     CREATE STATISTICS stat_name (mcv) ON col1, col2 FROM table_name;
     ANALYZE table_name; -- 收集数据

2. 表达式统计信息 (expr)

   • 引入版本：PostgreSQL 11

   • 场景： 优化器无法直接对基于表达式或函数的查询条件（尤其是出现在 WHERE 子句中）进行准确的选择性估计。

   • 问题示例：

     • WHERE lower(name) = 'alice'

     • WHERE (salary / 1000) > 50

     • WHERE date_trunc('month', order_date) = '2023-10-01'

   • 作用： 允许为表达式的结果创建统计信息（包括 MCV 列表和直方图）。优化器不再需要基于底层列统计和猜测函数行为来估计，而是直接使用表达式结果值的分布。

   • 创建命令：

     CREATE STATISTICS stat_name (expr) ON (lower(name)), (salary / 1000) FROM table_name;
     ANALYZE table_name; -- 收集数据

3. 数组字段的 MCV 列表 (mcv)

   • 引入版本：PostgreSQL 14

   • 场景： 优化涉及数组列元素组合条件的选择性估计。

   • 问题示例：

     • WHERE tags @> ARRAY['tag1', 'tag2'] (数组包含所有指定元素)

     • WHERE tags && ARRAY['tag1', 'tag2'] (数组包含任意指定元素)

   • 作用： 为数组类型的列创建扩展统计时，MCV 列表会存储整个数组值（而不仅仅是单个元素）的频率。这使得优化器能够识别最常见的特定数组值（例如，['red', 'large'] 比 ['red'] 或 ['large'] 更具体），从而大幅提高 @> (包含所有) 和 && (重叠) 操作符的选择性估计准确性。注意：这依赖于对整个数组值进行统计，而不是数组元素的独立统计。

   • 创建命令：

     CREATE STATISTICS stat_name (mcv) ON array_column FROM table_name;
     ANALYZE table_name; -- 收集数据

4. JSON 字段的扩展统计 (mcv)

   • 引入版本：PostgreSQL 16 (当前最新稳定版)

   • 场景： 优化涉及JSONB 文档中多个键值对组合条件的选择性估计。JSONB 的灵活性使得其内部结构难以用传统列统计建模。

   • 问题示例：

     • WHERE jdata->>'name' = 'Alice' AND jdata->>'department' = 'Sales'

     • WHERE jdata @> '{"name": "Alice", "active": true}'

   • 作用： 类似于多列 MCV，但专门针对从 JSONB 列中提取的标量值。在创建扩展统计时指定 JSONB 列和一系列键路径（如 (jdata->>'name'), (jdata->>'department')），系统会为这些提取出的标量值的组合构建 MCV 列表。这极大地优化了涉及 JSONB 中多个字段联合条件的查询计划。

   • 创建命令：

     CREATE STATISTICS stat_name (mcv) ON (jdata->>'name'), (jdata->>'department') FROM table_name;
     ANALYZE table_name; -- 收集数据

总结关键点：

• 核心目标： 所有扩展统计信息都旨在解决传统单列统计信息在处理列间相关性、表达式结果或复杂数据类型内部结构时选择性估计不准的问题，从而帮助优化器生成更优的执行计划（尤其是避免选择错误的连接顺序或扫描方法）。

• 主动创建： 这些统计信息不会自动收集。DBA 或开发者需要根据查询模式（特别是那些执行缓慢且怀疑是由于错误行数估计导致的查询）使用 CREATE STATISTICS 命令显式定义需要收集哪种类型(mcv, expr)的统计信息以及涉及哪些列或表达式。

• 依赖 ANALYZE： 创建 STATISTICS 对象后，必须运行 ANALYZE（手动或通过 autovacuum）来实际收集数据并填充 pg_statistic_ext 系统目录。

• 版本演进： PG10 解决了基础的多列组合问题，PG11 扩展到了表达式，PG14 深入到了数组内部结构，PG16 则聚焦于日益重要的 JSONB 数据。

选择使用哪种扩展统计：

1. 查询慢且怀疑优化器估算行数错误？ 使用 EXPLAIN (ANALYZE, BUFFERS) 查看估算行数(rows)与实际行数(Actual Rows)是否差异巨大。

2. 错误估计发生在哪里？

   • 多列等值条件组合 (col1=A AND col2=B)： 优先考虑 (mcv) 统计。

   • 基于表达式/函数的条件 (func(col)=X)： 使用 (expr) 统计。

   • 数组包含/重叠条件 (arr @> Y, arr && Z)： 对数组列使用 (mcv) 统计。

   • JSONB 中多个键值对组合条件： 对提取出的标量路径使用 (mcv) 统计 (PG16+)。

通过明智地使用这些扩展统计信息，可以显著提升 PostgreSQL 优化器对复杂查询条件的判断能力，从而获得更好的查询性能。