PostgreSQL / 系统课时

验证扩展统计收益

验证扩展统计收益

扩展统计不是“建了就算优化”。这一课用可复现的数据先建立估算基线,再创建统计对象并执行 ANALYZE,最后从估算误差、计划与执行代价、维护成本三个维度决定保留还是删除。

学习目标

  • 用 EXPLAIN (ANALYZE) 对比扩展统计前后的估算误差
  • 判断“估算更准但计划不变”是否仍然有价值
  • 建立扩展统计对象的验收、观察与清理标准

建立没有扩展统计的基线

先构造两个完全相关的列:country='CN' 时 province 一定是 ZJ,country='US' 时一定是 CA。如果规划器只把两列当作独立条件,CN 与 ZJ 各约占 50%,组合选择率会被近似为 25%,而真实命中率是 50%。

sql
DROP TABLE IF EXISTS extstats_lab;

CREATE TABLE extstats_lab (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    country text NOT NULL,
    province text NOT NULL,
    amount numeric(10, 2) NOT NULL
);

INSERT INTO extstats_lab (country, province, amount)
SELECT
    CASE WHEN g <= 50000 THEN 'CN' ELSE 'US' END,
    CASE WHEN g <= 50000 THEN 'ZJ' ELSE 'CA' END,
    100.00
FROM generate_series(1, 100000) AS g;

ANALYZE extstats_lab;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM extstats_lab
WHERE country = 'CN'
  AND province = 'ZJ';

预期结果

text
插入后共有 100000 行。
没有扩展统计时,扫描节点的 estimated rows 通常会接近 25000,
而 actual rows 为 50000;具体 cost、time 与 buffer 数量会因环境不同而变化。

评估时不要只看总耗时。更稳定的指标是“误差倍数”:取 actual rows / estimated rows 与其倒数中的较大值。上例基线约为 2 倍误差。若同一节点被多次执行,还要结合 loops 理解总行数,不能只比较表面上的单次 rows。

创建统计对象并重新采样

这里使用 dependencies,因为问题来自列之间的函数依赖关系。CREATE STATISTICS 只定义对象,真正的数据要等下一次 ANALYZE 才会采集。

sql
CREATE STATISTICS st_extstats_lab_dep (dependencies)
ON country, province
FROM extstats_lab;

ANALYZE extstats_lab;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM extstats_lab
WHERE country = 'CN'
  AND province = 'ZJ';

SELECT statistics_name, attnames, kinds
FROM pg_stats_ext
WHERE tablename = 'extstats_lab';

预期结果

text
第二次 EXPLAIN 中,estimated rows 应明显向 50000 靠近,actual rows 仍为 50000。
pg_stats_ext 中可看到:
st_extstats_lab_dep | {country,province} | {f}
其中 {f} 表示 functional dependencies 统计已被采集。

如果估算已经明显改善,但计划仍然是顺序扫描,这并不代表扩展统计“无效”。统计对象首先修复的是基数估算;只有当新的行数估计跨过某个代价阈值时,连接顺序、连接算法、索引访问方式等计划节点才会随之变化。

用三类证据决定是否验收

建议把验收拆成三类证据,而不是只比较一次执行时间。

证据 应该观察什么 可以接受的结论
估算质量 关键节点的 estimated rows 与 actual rows 误差持续缩小,且覆盖真实参数组合
计划与执行 节点类型、连接顺序、Buffers、执行时间 计划变好最好;计划不变但估算显著修复也可保留
维护成本 ANALYZE 耗时、统计对象数量、涉及列数 采样成本可进入现有自动维护窗口

验收应使用代表性参数,而不是只测一个“最好看的”条件。对于既有热点组合又有长尾组合的表,至少各测一组;如果扩展统计只改善一个极少出现的查询,却让 ANALYZE 明显变慢,就需要重新评估收益。

清理无收益或失效的统计对象

当业务查询已经下线、相关列不再相关、对象从未改善目标估算,或者维护成本超过收益时,应删除统计对象。删除后规划器会立即停止使用它;后续常规 ANALYZE 会继续维护普通列统计。

sql
DROP STATISTICS IF EXISTS st_extstats_lab_dep;

预期输出

text
DROP STATISTICS

一个可执行的治理标准是:先保存扩展统计前的 EXPLAIN (ANALYZE, BUFFERS),创建对象并 ANALYZE,再用同一组代表性参数复测;只有估算误差稳定下降且维护成本可接受时才保留。这样扩展统计就从一次性的调参动作,变成可以复查、验收和清理的治理对象。

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

目录与阅读设置

记录一个想法

验证扩展统计收益

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

登录哪都通