PostgreSQL / 系统课时

更新统计信息

更新统计信息

规划器不会先执行查询再决定计划,而是依赖统计信息估算行数和数据分布。本课通过数据突增场景观察统计信息过期的影响,并使用 ANALYZE 恢复更准确的估算。

学习目标

  • 理解统计信息与执行计划的关系
  • 使用 ANALYZE 更新表和指定列的统计
  • 判断何时需要手动分析或调整自动分析策略

制造统计信息过期

下面先写入少量旧数据并分析,再一次性插入大量新状态数据。第二次 EXPLAIN 之前,规划器仍主要依赖旧分布。

sql
CREATE TEMP TABLE stats_tasks (
    id integer PRIMARY KEY,
    status text NOT NULL
);

INSERT INTO stats_tasks (id, status)
SELECT n, 'DONE'
FROM generate_series(1, 100) AS n;

ANALYZE stats_tasks;

INSERT INTO stats_tasks (id, status)
SELECT n, 'NEW'
FROM generate_series(101, 10100) AS n;

EXPLAIN
SELECT * FROM stats_tasks WHERE status = 'NEW';

ANALYZE stats_tasks;

EXPLAIN
SELECT * FROM stats_tasks WHERE status = 'NEW';

预期结果

text
第一次计划对 status = 'NEW' 的 rows 估算通常明显偏离 10000
执行 ANALYZE 后,第二次计划的 rows 估算应接近 10000

具体估算数字会受版本和临时表统计行为影响,但更新后应更接近真实数据分布。

ANALYZE 收集什么

ANALYZE 会抽样统计空值比例、不同值数量、常见值及其频率、直方图边界等信息。规划器据此估算过滤条件和连接条件会返回多少行。

它不会扫描并保存每一行的完整分布,因此统计结果是近似值。数据高度倾斜时,默认统计目标可能不足以描述关键列。

更新整表或指定列

可对整张表执行分析,也可只更新参与关键条件的列。

sql
ANALYZE stats_tasks;

ANALYZE stats_tasks (status);

预期结果

text
ANALYZE
ANALYZE

两条命令都会刷新统计信息;第二条只针对 status 列。成功时 PostgreSQL 返回命令标签 ANALYZE。

何时手动执行

PostgreSQL 的 autovacuum 机制通常会自动触发分析。批量导入、一次性更新大量行、数据分布突然变化,或刚创建关键索引后需要立即验证计划时,可以手动执行 ANALYZE。

若业务表变化很快,应检查自动分析阈值是否过高,而不是依赖人工定时执行全部表分析。

提高关键列统计精度

对于值分布极不均匀、又频繁用于过滤或连接的列,可以提高统计目标,再重新分析。

sql
ALTER TABLE stats_tasks
ALTER COLUMN status SET STATISTICS 500;

ANALYZE stats_tasks (status);

预期结果

text
ALTER TABLE
ANALYZE

更高统计目标可能改善估算,但会增加分析时间和系统目录中的统计数据量,应只对关键列使用。

验证更新是否有效

再次执行 EXPLAIN 或 EXPLAIN ANALYZE,比较估算行数与实际行数。若统计已更新但偏差仍大,应继续检查列相关性、复杂表达式、参数化查询和数据倾斜。

统计信息是优化基础,但它不能修复缺失索引、低效 SQL 或不合理的数据模型。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

更新统计信息

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通