统计信息的收集

统计信息的收集

目录

信息主要是AutoVacuum进程收集的，用于查询优化时的代价估算。

表和索引的行数、块数等统计信息记录在系统表“pg_class”中，其他的统计信息主要收集在系统表“pg_statistic”中。而StatsCollector子进程是PostgreSQL中专门的性能统计数据收集器进程，其收集的性能数据可以通过“pg_stat_*”视图来查看，这些性能统计数据对数据库活动的监控及分析性能有很大的帮助。

手动收集统计信息

手动收集统计信息的命令是ANALYZE命令，此命令用于收集表的统计信息，然后把结果保存在系统表“pg_statistic”中。优化器可以
使用收集到的统计信息来确定最优的执行计划。

在默认的PostgreSQL配置中，AutoVacuum守护进程是打开的，它能自动分析表、收集表的统计信息。当AutoVacuum进程关闭时，需要周期性地，或者在表的大部分内容变更后运行ANALYZE命令。准确的统计信息能帮助优化器生成最优的执行计划，从而改善查询的性能。比较常用的一种策略是每天在数据库比较空闲的时候运行一次VACUUM和ANALYZE命令。

ANALYZE命令的语法格式如下：

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]
命令中的选项说明如下。
·VERBOSE：增加此选项将显示处理的进度以及表的一些统计信
息。
·table：要分析的表名，如果不指定，则对整个数据库中的所有
表进行分析。
·column：要分析的特定字段的名称。默认分析所有字段。

ANALYZE命令的应用示例如下。
1.只分析表“test01”中的“id2”列：

osdba=# ANALYZE test01(id2);

2.分析表“test01”中的“id1”和“id2”两个列：

osdba=# ANALYZE test01(id1,id2);

3.分析表“test01”中的所有列：

osdba=# ANALYZE test01;

ANALYZE命令只需在表上加一个读锁，因此它可以与表上的其他SQL命令并发执行。ANALYZE命令会收集表的每个字段的直方图和最常用数值的列表。

对于大表，ANALYZE命令只读取表的部分内容做一个随机抽样，不读取表的所有内容，这样就保证了即使是在很大的表上也只需要很少时间就可以完成统计信息的收集。统计信息只是近似的结果，即使表内容实际上没有改变，运行ANALYZE命令后EXPLAIN命令显示的执行计划中的COST值也会有一些变化。为了增加所收集的统计信息的准确度，可以增大随机抽样比例，这可以通过调整参数“default_statistics_target”来实现，该参数可在session级别设置，比如在分析不同的表时设置不同的值。在下面的示例中，假设表
“test01”的行数较少，设置“default_statistics_target”为“500”，然后分析test01表，表“test02”行数较多，设置
“default_statistics_target”为“10”，再分析test02表，命令如下：

osdba=# set default_statistics_target to 500;
SET
osdba=# analyze test01;
ANALYZE
osdba=# set default_statistics_target to 10;
SET
osdba=# analyze test02;
ANALYZE

也可以直接设置表的每个列的统计target值，命令如下：

osdba=# ALTER TABLE test01 ALTER COLUMN id2 SET STATISTICS 200;
ALTER TABLE

总结

下面把 PostgreSQL 的两种统计信息收集方式——自动与手工——放在一张时间轴里说明：
（以下参数默认值基于 16/17 版本，中文社区也叫“统计信息”= 日文里的“統計情報”）

一、自动收集：Auto-Analyze（autovacuum 的子任务）

1.触发进程

autovacuum launcher → 根据公式计算 → 启动 autovacuum worker → worker 里先 VACUUM 再 ANALYZE（或仅 ANALYZE）。

2.触发公式（analyze 部分）

变更行数 ≥ autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × pg_class.reltuples
默认 50 + 0.1 × 表总行数。
例：1 000 万行表，大约 50 + 1 000 万 × 0.1 ≈ 100 万行被 INSERT/UPDATE/DELETE 后才会自动 ANALYZE。

3.可调整参数

  • autovacuum_analyze_threshold -- 固定行数门槛（默认 50）
  • autovacuum_analyze_scale_factor -- 比例因子（默认 0.1）
  • autovacuum_analyze_insert_threshold / *_scale_factor -- v17 新增，对仅 INSERT 的表更友好
  • autovacuum -- 总开关，on|off
  • log_autovacuum_min_duration -- 把耗时超过阈值的 autovacuum/analyze 记入日志，方便审计

4.特点与注意

  • 同步执行：analyze 运行时短暂持有表的 SHARE UPDATE EXCLUSIVE 锁，阻塞 DDL，不阻塞读写(DML)。
  • 不会“异步”：必须等本次 analyze 结束，新计划才能用上新统计；没有 SQL Server 的“异步更新”功能。
  • 对超大表可能“滞后”：因为阈值是 10 % 量级，1 亿行表要改 1 千万行才触发，容易导致统计老化。
  • 父表分区表陷阱：autovacuum 只盯父表自身变化，子表变化再多也不会自动触发父表 analyze，需要手工或在父表上再跑 ANALYZE。

二、手工收集：ANALYZE / VACUUM (ANALYZE)

语法

ANALYZE [ ( option [, ...] ) ] [ table_name [ ( column_name [, ...] ) ] ]
常用变体：
ANALYZE;                          -- 当前库所有表（实际抽样，很快）
ANALYZE bigtbl;                   -- 单表
ANALYZE bigtbl(col1, col2);       -- 只更新指定列的统计
VACUUM (ANALYZE) bigtbl;          -- 先回收死元组，再更新统计

典型使用场景

  • 批量加载/ETL 后立即想让新数据进计划：COPY、INSERT ... SELECT 结束后手动 ANALYZE。
  • 新建或重建索引后：确保 planner 知道索引存在及其选择性。
  • 创建函数索引、表达式索引、扩展统计对象（CREATE STATISTICS）后：必须手动 analyze，autovacuum 不会识别这些新对象。
  • 分区表父表：子表数据大变后，父表统计没更新，手工 ANALYZE parent。
  • 做性能对比 / 紧急救火：怀疑走错计划，先 ANALYZE 排除统计过期因素。

并行与速度

  • 14 版以后支持并行 analyze（需设置 max_parallel_maintenance_workers）。
  • 默认采样 300 × default_statistics_target 行（target 默认 100），远小于表大小，因此一般几秒完成。

锁与并发

同样拿 SHARE UPDATE EXCLUSIVE 锁，不阻塞 SELECT / DML，仅阻塞对同表的 DDL。

一句话总结

PostgreSQL 的“位图索引”式统计信息更新并不存在；想保持计划精准，打开 autovacuum 做日常保洁，关键加载后顺手 ANALYZE，超大表调低比例因子，分区表记得刷父表——自动为主，手工兜底，二者结合即可

posted @ 2026-05-19 11:02  数据库小白（专注）  阅读(17)  评论(0)    收藏  举报