PostgreSQL / 系统课时
使用 MCV 扩展统计
使用 MCV 扩展统计
当两个列各自的频率看起来正常,但某些值组合特别常见时,单列统计会把条件近似当成独立事件,导致行数估算偏差。本节用一个可复现实验创建多列 MCV 统计,并比较创建前后的估算结果。
学习目标
- 识别“单列频率正确、组合频率错误”的估算问题
- 创建并刷新多列 MCV 扩展统计
- 用
EXPLAIN ANALYZE验证估算是否贴近实际行数
热点组合为什么会被低估
假设订单的 region 与 channel 各有一半记录落在 north 和 web。只看单列统计,规划器容易按 0.5 × 0.5 = 0.25 估算 region = 'north' AND channel = 'web',也就是约四分之一数据。
但真实业务里两个条件往往相关:某个区域可能主要来自某个渠道。MCV(Most Common Values)扩展统计记录的是多列值组合的高频分布,因此能够直接表达“north + web 占 40%”这种单列统计无法表示的信息。
创建 MCV 并比较估算
下面构造 10000 行数据,其中 north + web 有 4000 行,但两个单列的边际频率仍各为 50%。先只做普通 ANALYZE,再创建 MCV 统计并重新分析。
DROP TABLE IF EXISTS mcv_orders;
CREATE TABLE mcv_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
region text NOT NULL,
channel text NOT NULL
);
INSERT INTO mcv_orders (region, channel)
SELECT
CASE WHEN i <= 5000 THEN 'north' ELSE 'south' END,
CASE
WHEN i <= 4000 THEN 'web'
WHEN i <= 5000 THEN 'store'
WHEN i <= 6000 THEN 'web'
ELSE 'store'
END
FROM generate_series(1, 10000) AS g(i);
ANALYZE mcv_orders;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT *
FROM mcv_orders
WHERE region = 'north'
AND channel = 'web';
CREATE STATISTICS mcv_orders_region_channel (mcv)
ON region, channel
FROM mcv_orders;
ANALYZE mcv_orders;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT *
FROM mcv_orders
WHERE region = 'north'
AND channel = 'web';预期输出
创建 MCV 前:
Seq Scan on mcv_orders ... rows=2500 ...
(actual rows=4000 loops=1)
创建 MCV 后:
Seq Scan on mcv_orders ... rows=4000 ...
(actual rows=4000 loops=1)成本、行宽等字段会因 PostgreSQL 版本和环境不同而变化;这里应重点比较计划中的 rows 与 actual rows。这个数据集较小,普通统计会准确得到两个单列各 50% 的频率,因此创建 MCV 前约估 2500 行;MCV 记录组合分布后,估算会接近真实的 4000 行。
MCV 实际记录了什么
可以通过 pg_stats_ext 查看统计对象。most_common_freqs 表示组合的实际频率,most_common_base_freqs 表示按各列独立频率相乘得到的基准频率。两者差距越明显,说明列之间的组合相关性越值得被 MCV 捕获。
SELECT
statistics_name,
most_common_vals,
most_common_freqs,
most_common_base_freqs
FROM pg_stats_ext
WHERE tablename = 'mcv_orders'
AND statistics_name = 'mcv_orders_region_channel';预期输出
statistics_name | most_common_vals | most_common_freqs | most_common_base_freqs
mcv_orders_region_channel | <MCV_VALUES> | <MCV_FREQS> | <BASE_FREQS>数组的具体显示格式可能随客户端不同,但应能看到高频组合及其频率;对 north + web 来说,组合频率约为 0.4,而独立基准频率约为 0.25。
什么时候应该使用 MCV
MCV 适合修复同一张表上多个谓词组合的偏斜,例如地区与渠道、状态与类型、租户与业务线。它不是“列越多越好”:只为经常一起过滤且确有估算偏差的列建立统计对象,否则会增加 ANALYZE 的采样和存储成本。
创建统计对象本身不会立即产生统计数据,之后必须执行 ANALYZE,并在数据分布明显变化后继续保持分析任务正常运行。扩展统计主要改善选择性估算,不会替代索引;如果计划已经准确但扫描仍然昂贵,应继续从索引、分区或 SQL 访问路径寻找原因。