PostgreSQL / 系统课时
验证扩展统计收益
验证扩展统计收益
扩展统计不是“建了就算优化”。这一课用可复现的数据先建立估算基线,再创建统计对象并执行 ANALYZE,最后从估算误差、计划与执行代价、维护成本三个维度决定保留还是删除。
学习目标
- 用
EXPLAIN (ANALYZE)对比扩展统计前后的估算误差 - 判断“估算更准但计划不变”是否仍然有价值
- 建立扩展统计对象的验收、观察与清理标准
建立没有扩展统计的基线
先构造两个完全相关的列:country='CN' 时 province 一定是 ZJ,country='US' 时一定是 CA。如果规划器只把两列当作独立条件,CN 与 ZJ 各约占 50%,组合选择率会被近似为 25%,而真实命中率是 50%。
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';预期结果
插入后共有 100000 行。
没有扩展统计时,扫描节点的 estimated rows 通常会接近 25000,
而 actual rows 为 50000;具体 cost、time 与 buffer 数量会因环境不同而变化。评估时不要只看总耗时。更稳定的指标是“误差倍数”:取 actual rows / estimated rows 与其倒数中的较大值。上例基线约为 2 倍误差。若同一节点被多次执行,还要结合 loops 理解总行数,不能只比较表面上的单次 rows。
创建统计对象并重新采样
这里使用 dependencies,因为问题来自列之间的函数依赖关系。CREATE STATISTICS 只定义对象,真正的数据要等下一次 ANALYZE 才会采集。
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';预期结果
第二次 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 会继续维护普通列统计。
DROP STATISTICS IF EXISTS st_extstats_lab_dep;预期输出
DROP STATISTICS一个可执行的治理标准是:先保存扩展统计前的 EXPLAIN (ANALYZE, BUFFERS),创建对象并 ANALYZE,再用同一组代表性参数复测;只有估算误差稳定下降且维护成本可接受时才保留。这样扩展统计就从一次性的调参动作,变成可以复查、验收和清理的治理对象。