PostgreSQL / 系统课时

计算 ANALYZE 触发阈值

计算 ANALYZE 触发阈值

自动 ANALYZE 的目标是让优化器统计信息跟上数据变化。判断重点不是死元组,而是自上次 ANALYZE 以来被插入、更新或删除的行数。本课用统一公式计算触发点,并把变化量与 n_mod_since_analyze 对上。

学习目标

  • 根据表规模计算自动 ANALYZE 的触发阈值
  • 正确合并 INSERT、UPDATE、DELETE 对变化计数的贡献
  • 判断何时需要降低单表的 analyze 比例因子

ANALYZE 看的是数据变化量

自动 ANALYZE 的阈值公式比 VACUUM 简单:

Tanalyze=B+S×NT_{analyze}=B+S\times N

其中 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。下面同时比较默认策略与更积极的表级策略。

sql
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;

预期输出

text
 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 参数,为所有普通用户表计算一个观察值。

sql
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;

预期输出

text
 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,应以表级值为准。

为计划敏感表设置独立阈值

表级配置适合“总体行数很大,但少量变化就可能显著改变数据分布”的表,例如状态列、高偏斜分类列或快速切换冷热数据的业务表。下面创建演示表并设置独立阈值。

sql
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;

预期输出

text
            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 可用性影响。

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

目录与阅读设置

阅读设置

17

记录一个想法

计算 ANALYZE 触发阈值

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

登录哪都通