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 统计并重新分析。

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

预期输出

text
创建 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 捕获。

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

预期输出

text
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 访问路径寻找原因。

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

目录与阅读设置

记录一个想法

使用 MCV 扩展统计

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

登录哪都通