PostgreSQL / 系统课时
使用 dependencies 统计
使用 dependencies 统计
当两个过滤列存在“一个值几乎决定另一个值”的关系时,优化器若仍把条件当作独立事件相乘,往往会明显低估结果行数。本课用可复现实验创建 dependencies 扩展统计,并通过执行计划验证估算如何被修正。
学习目标
- 判断多列等值过滤是否适合使用 functional dependencies 统计
- 创建
dependencies统计对象并执行ANALYZE - 对比扩展统计前后的估算行数,并识别它的适用边界
为什么独立性假设会低估
普通列统计分别描述每一列。若 a = 1 命中 1% 的行,b = 1 也命中 1%,在没有跨列信息时,优化器通常会把两个选择率相乘,得到约 0.01%。但如果 a 与 b 基本同步变化,第二个条件并没有再把结果缩小 100 倍。
dependencies 记录列之间的 functional dependency。若知道 a 的值几乎就能确定 b,优化器可以把相互冗余的等值条件识别出来,避免重复折减基数。它修的是估算模型,不会创建索引,也不会改变表中数据。
先观察没有扩展统计时的估算
下面创建 10000 行数据,a 与 b 始终相等,因此每个 a 值都能完全决定 b。先只收集普通统计,再查看两个等值条件同时出现时的执行计划。
DROP TABLE IF EXISTS planner_dep_lab;
CREATE TABLE planner_dep_lab (
a integer,
b integer
);
INSERT INTO planner_dep_lab (a, b)
SELECT i % 100, i % 100
FROM generate_series(1, 10000) AS s(i);
ANALYZE planner_dep_lab;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT *
FROM planner_dep_lab
WHERE a = 1
AND b = 1;预期输出中,actual rows 应为 100,而没有多列依赖信息时,rows 估算通常只有约 1。成本数值会随 PostgreSQL 版本与环境变化。
Seq Scan on planner_dep_lab (cost=<COST> rows=1 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900这正是独立性假设造成的误差:单列条件各自约命中 1%,相乘后只剩约 1 行,但真实数据中两个条件实际上描述的是同一组 100 行。
创建 dependencies 并重新收集统计
CREATE STATISTICS 只创建统计对象定义,不会立即计算统计数据。要让优化器真正获得依赖信息,必须随后执行 ANALYZE,或者等待自动分析覆盖该表。
CREATE STATISTICS planner_dep_stats (dependencies)
ON a, b
FROM planner_dep_lab;
ANALYZE planner_dep_lab;
SELECT statistics_name, attnames, kinds, dependencies
FROM pg_stats_ext
WHERE statistics_name = 'planner_dep_stats';
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT *
FROM planner_dep_lab
WHERE a = 1
AND b = 1;预期输出会显示统计类型 f,并记录双向的完全依赖;随后执行计划中的估算行数应接近实际的 100 行。显示格式中的空格可能因客户端不同而略有差异。
statistics_name | attnames | kinds | dependencies
------------------+----------+-------+----------------------------------------
planner_dep_stats | {a,b} | {f} | {"1 => 2": 1.000000, "2 => 1": 1.000000}
Seq Scan on planner_dep_lab (cost=<COST> rows=100 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900生产数据不必达到 1.0 的完全依赖。只要某列在很大程度上由另一列决定,例如地区编码与国家编码、产品子类与大类,ANALYZE 也可以记录部分依赖,从而改善匹配查询的选择率估算。
什么时候该用,什么时候别用
适合 dependencies 的信号是:同一张表的多个列经常一起出现在过滤条件中,而且业务规则使它们存在稳定的决定关系。先用 EXPLAIN (ANALYZE) 确认是估算误差,再创建统计对象,最后重新执行同一条查询验证 rows 与 actual rows 是否收敛。
它不是任意“列相关性”的万能修复。functional dependencies 主要用于列与常量之间的简单等值条件以及常量 IN 条件;对连接条件、范围条件、LIKE 等场景不能指望它修正选择率。对于某些互相矛盾的值组合,它还可能因为把条件视为冗余而无法估成 0,此时多列 MCV 往往更合适。
当依赖关系列发生显著分布变化后,应让自动分析或手工 ANALYZE 及时重建统计。判断是否生效的最终标准不是统计对象“存在”,而是目标查询的基数估算是否持续更接近实际行数。