PostgreSQL / 系统课时
计算 ANALYZE 触发阈值
计算 ANALYZE 触发阈值
自动 ANALYZE 的目标是让优化器统计信息跟上数据变化。判断重点不是死元组,而是自上次 ANALYZE 以来被插入、更新或删除的行数。本课用统一公式计算触发点,并把变化量与 n_mod_since_analyze 对上。
学习目标
- 根据表规模计算自动 ANALYZE 的触发阈值
- 正确合并
INSERT、UPDATE、DELETE对变化计数的贡献 - 判断何时需要降低单表的 analyze 比例因子
ANALYZE 看的是数据变化量
自动 ANALYZE 的阈值公式比 VACUUM 简单:
其中 B 是 autovacuum_analyze_threshold,S 是 autovacuum_analyze_scale_factor,N 是 pg_class.reltuples。PostgreSQL 18 默认 B=50、S=0.1,所以估算 1 万行的表,阈值是 50 + 0.1 × 10000 = 1050。
参与比较的是自上次 ANALYZE 以来插入、更新、删除的总变化量。INSERT 不会制造死元组,但会改变列值分布和行数,因此同样会推进自动分析计数。运行一次手工或自动 ANALYZE 后,这个变化计数会重新开始累计。
用变化量判断是否越线
假设一张 1 万行的表在上次分析后发生 200 次插入、200 次更新和 100 次删除,总变化量为 500。下面同时比较默认策略与更积极的表级策略。
WITH sample(policy, reltuples, base_threshold, scale_factor, inserted, updated, deleted) AS (
VALUES
('default', 10000::numeric, 50::numeric, 0.10::numeric, 200::numeric, 200::numeric, 100::numeric),
('custom', 10000::numeric, 200::numeric, 0.02::numeric, 200::numeric, 200::numeric, 100::numeric)
)
SELECT
policy,
reltuples::bigint,
(inserted + updated + deleted)::bigint AS changed_rows,
(base_threshold + scale_factor * reltuples)::bigint AS analyze_threshold,
(inserted + updated + deleted) > (base_threshold + scale_factor * reltuples) AS eligible
FROM sample
ORDER BY policy;预期输出
policy | reltuples | changed_rows | analyze_threshold | eligible
---------+-----------+--------------+-------------------+----------
custom | 10000 | 500 | 400 | t
default | 10000 | 500 | 1050 | f这里最重要的不是“更新了多少行”,而是三种 DML 的合计变化量。对于数据分布变化快、查询计划敏感但表规模又很大的表,较低的 autovacuum_analyze_scale_factor 往往比频繁手工 ANALYZE 更容易形成稳定策略。
从统计视图观察当前进度
pg_stat_user_tables.n_mod_since_analyze 表示自上次分析以来修改行数的估算值,可以直接用来判断当前表距离阈值还有多远。下面的查询使用实例当前的全局 analyze 参数,为所有普通用户表计算一个观察值。
WITH cfg AS (
SELECT
current_setting('autovacuum_analyze_threshold')::numeric AS base_threshold,
current_setting('autovacuum_analyze_scale_factor')::numeric AS scale_factor
)
SELECT
s.schemaname,
s.relname,
c.reltuples::bigint AS estimated_rows,
s.n_mod_since_analyze,
(cfg.base_threshold + cfg.scale_factor * c.reltuples)::bigint AS analyze_threshold,
s.n_mod_since_analyze > (cfg.base_threshold + cfg.scale_factor * c.reltuples) AS eligible_by_global_rule
FROM pg_stat_user_tables AS s
JOIN pg_class AS c ON c.oid = s.relid
CROSS JOIN cfg
ORDER BY
s.n_mod_since_analyze / GREATEST(cfg.base_threshold + cfg.scale_factor * c.reltuples, 1) DESC,
s.schemaname,
s.relname
LIMIT 10;预期输出
schemaname | relname | estimated_rows | n_mod_since_analyze | analyze_threshold | eligible_by_global_rule
------------+---------+----------------+---------------------+-------------------+-------------------------
<SCHEMA> | <TABLE> | <VALUE> | <VALUE> | <VALUE> | <VALUE>实际值取决于数据库中的表和统计状态。这个查询按全局参数计算;如果某张表设置了表级 autovacuum_analyze_threshold 或 autovacuum_analyze_scale_factor,应以表级值为准。
为计划敏感表设置独立阈值
表级配置适合“总体行数很大,但少量变化就可能显著改变数据分布”的表,例如状态列、高偏斜分类列或快速切换冷热数据的业务表。下面创建演示表并设置独立阈值。
CREATE TABLE IF NOT EXISTS public.analyze_policy_demo (
id bigint PRIMARY KEY,
status text
);
ALTER TABLE public.analyze_policy_demo SET (
autovacuum_analyze_threshold = 200,
autovacuum_analyze_scale_factor = 0.02
);
SELECT option_name, option_value
FROM pg_class AS c
CROSS JOIN LATERAL pg_options_to_table(c.reloptions)
WHERE c.oid = 'public.analyze_policy_demo'::regclass
AND option_name IN ('autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor')
ORDER BY option_name;预期输出
option_name | option_value
------------------------------------+--------------
autovacuum_analyze_scale_factor | 0.02
autovacuum_analyze_threshold | 200降低阈值会让统计信息更及时,但也会增加 ANALYZE 的 CPU 与 I/O 消耗,因此应以查询计划稳定性和数据变化速度为依据,而不是统一把所有表调得更激进。
最终判断可以记成一句话:VACUUM 主要关心可清理版本的积累,而 ANALYZE 关心“统计信息是否已经落后于数据变化”。看到 n_mod_since_analyze 越过按当前表规模计算出的阈值,就说明自动分析已经满足触发条件;实际启动时间仍会受 autovacuum 调度周期和 worker 可用性影响。