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 或不合理的数据模型。
按自己的节奏,确认真正理解。