【YUNBEE云贝-技术文章】PostgreSQL多元统计信息

本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

PostgreSQL多元统计信息

PostgreSQL作为一款功能强大的开源关系型数据库管理系统，以其卓越的数据处理能力和丰富的统计特性受到广大用户的青睐。其中，多元统计信息（Most Common Values, MCV）是PostgreSQL用于优化查询性能的一项重要技术。本文旨在深入探讨多元统计信息（MCV）的概念、工作原理、应用场景及其对查询性能的影响，帮助读者全面理解和有效利用这一功能。

一、MCV概念解析

Most Common Values (MCV)，直译为“最常见的值”，是一种针对数据库表中某一列的统计信息，用于记录该列中最常出现的值及其出现频率。MCV是PostgreSQL自动收集并维护的统计信息之一，旨在辅助查询优化器更准确地估算查询计划的成本，从而选择最高效的执行方案。MCV统计信息通常包括以下两部分：

1. 常见值（Common Values）：列中出现频率较高的具体数值或离散值。这些值被记录在统计信息中，每个值对应一个条目。

2. 频率（Frequencies）：与每个常见值相对应的出现次数或占比。这些数据提供了常见值在列中出现的相对频繁程度，有助于量化其代表性。

二、MCV工作原理

PostgreSQL的统计信息收集机制会定期（或在特定触发条件下）对表进行分析，以更新MCV等统计信息。对于某列，系统会选择一定数量（默认为100个）的最常见值以及它们对应的频率进行记录。选择的标准通常是基于这些值在数据集中的相对频率，确保所选值能够代表大部分数据。MCV信息在查询优化过程中的作用主要体现在以下几个方面：

1. 查询代价估算

查询优化器在规划查询执行计划时，需要对不同操作（如索引扫描、全表扫描、连接操作等）的代价进行估算。MCV信息可以帮助优化器更准确地估计基于特定条件筛选数据时可能返回的行数。例如，对于一个带有WHERE子句的查询，如果条件涉及到的列有MCV信息，优化器可以根据筛选值在MCV列表中的频率来近似计算满足条件的行数，从而更好地判断是否应该使用索引或进行全表扫描。

2. 索引选择与利用率

MCV信息对索引选择有直接影响。当查询条件中的值属于MCV集合时，优化器更倾向于选择相关的索引来加速查询，因为这些值在数据中较为普遍，索引扫描很可能带来显著的性能提升。反之，如果查询条件中的值在MCV中未出现或出现频率极低，优化器可能会认为全表扫描更为高效，因为索引在这种情况下可能覆盖的数据比例较小。

3. 数据分布假设验证

MCV信息还能帮助优化器验证或修正对数据分布的假设。在缺乏精确统计信息的情况下，优化器可能需要基于某些通用假设（如均匀分布）来估算查询成本。然而，实际数据往往具有特定的模式和偏斜，MCV信息能揭示这种非均匀性，使优化器能够做出更符合实际情况的决策。

三、MCV的应用场景与实践

1. 查询性能优化

对于包含大量数据且查询条件频繁涉及特定列的应用场景，MCV信息的价值尤为突出。例如，在电商系统中，商品分类、品牌等字段可能存在明显的热点值，查询时经常按照这些字段进行过滤。通过维护MCV信息，优化器可以更准确地预估查询结果集大小，合理选择索引，从而提高查询响应速度。

2. 统计信息维护策略

为了确保MCV信息的有效性，应定期更新统计信息，或者在数据发生显著变化后手动触发更新。可以使用ANALYZE命令对单个表或整个数据库进行分析。对于动态更新频繁的表，可以考虑设置更短的统计信息自动更新周期，确保MCV信息紧跟数据变化趋势。

3. 调优案例分析

在进行查询性能调优时，可以通过检查查询计划（使用EXPLAIN或EXPLAIN ANALYZE命令）来观察MCV信息是否被有效利用。如果发现查询计划选择不当，可以对比实际数据分布与MCV信息，检查是否存在统计信息过时、不准确或未被充分利用的情况，据此调整查询语句、索引结构或统计信息更新策略。

四、MCV的应用示例

4.1、功能依赖

多元相关性可以用一个非常简单的数据集来演示——一个有两列的表，两列都包含相同的值：

CREATE TABLE t (a INT, b INT);

INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);

ANALYZE t;

规划器可以使用从 pg_class 获得的页数和行数来确定 t 的基数：

SELECT relpages, reltuples FROM pg_class WHERE relname = 't';

relpages | reltuples

----------+-----------

45 |    10000

数据分布非常简单；每列中只有 100 个不同的值，均匀分布。

以下示例显示了估计 a 列上的 WHERE 条件的结果：

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1;

QUERY PLAN

-------------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1)

Filter: (a = 1)

Rows Removed by Filter: 9900

规划者检查条件并确定该子句的选择性为 1%。通过比较这个估计值和实际行数，我们发现估计值非常准确（实际上是准确的，因为表非常小）。更改 WHERE 条件以使用 b 列，会生成相同的计划。但是观察如果我们对两列应用相同的条件，并用 AND 将它们组合起来会发生什么：

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;

QUERY PLAN

-----------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1)

Filter: ((a = 1) AND (b = 1))

Rows Removed by Filter: 9900

规划者单独估计每个条件的选择性，得到与上述相同的 1% 估计值。然后它假设条件是独立的，因此将它们的选择性相乘，产生仅为 0.01% 的最终选择性估计。这是一个严重的低估，因为匹配条件 (100) 的实际行数要高出两个数量级。

这个问题可以通过创建一个统计对象来解决，该对象指示 ANALYZE 计算两列上的函数依赖多元统计信息：

CREATE STATISTICS stts (dependencies) ON a, b FROM t;

ANALYZE t;

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;

QUERY PLAN

-------------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)

Filter: ((a = 1) AND (b = 1))

Rows Removed by Filter: 9900

4.2、多元 N 不同计数

估计多列集的基数时也会出现类似的问题，例如由 GROUP BY 子句生成的组数。当 GROUP BY 列出单个列时，n-distinct 估计（作为 HashAggregate 节点返回的估计行数可见）非常准确：

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a;

QUERY PLAN

-----------------------------------------------------------------------------------------

HashAggregate  (cost=195.00..196.00 rows=100 width=12) (actual rows=100 loops=1)

Group Key: a

->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=4) (actual rows=10000 loops=1)

但如果没有多元统计信息，GROUP BY 中包含两列的查询中的组数估计值（如下例所示）会偏离一个数量级：

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;

QUERY PLAN

--------------------------------------------------------------------------------------------

HashAggregate  (cost=220.00..230.00 rows=1000 width=16) (actual rows=100 loops=1)

Group Key: a, b

->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1)

通过重新定义统计对象以包含两列的 n 个不同计数，估计值得到了很大改善：

DROP STATISTICS stts;

CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;

ANALYZE t;

EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;

QUERY PLAN

--------------------------------------------------------------------------------------------

HashAggregate  (cost=220.00..221.00 rows=100 width=16) (actual rows=100 loops=1)

Group Key: a, b

->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1)

4.3、MCV Lists

函数依赖是非常廉价且高效的统计类型，但它们的主要限制是其全局性（仅跟踪列级别的依赖关系，而不是各个列值之间的依赖关系）。

本节介绍 MCV（最常见值）列表的多变量变体，这是每列统计信息的直接扩展。这些统计数据通过存储单个值来解决这一限制，但无论是在分析中构建统计数据、存储还是规划时间方面，它的成本自然都更高。

让我们再次查看3.1的查询，但这次使用在同一列集上创建的 MCV 列表（确保删除函数依赖性，以确保规划器使用新创建的统计信息）。

DROP STATISTICS stts;

CREATE STATISTICS stts2 (mcv) ON a, b FROM t;

ANALYZE t;

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;

QUERY PLAN

-------------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)

Filter: ((a = 1) AND (b = 1))

Rows Removed by Filter: 9900

该估计与函数依赖项一样准确，这主要是因为该表相当小，并且分布简单，不同值的数量较少。在查看第二个查询（函数依赖关系处理得不是特别好）之前，让我们先检查一下 MCV 列表。

可以使用 pg_mcv_list_items 设置返回函数检查 MCV 列表。

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),

pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts2';

index |  values  | nulls | frequency | base_frequency

-------+----------+-------+-----------+----------------

0 | {0, 0}  | {f,f} |      0.01 |        0.0001

1 | {1, 1}  | {f,f} |      0.01 |        0.0001

...

49 | {49, 49} | {f,f} |      0.01 |        0.0001

50 | {50, 50} | {f,f} |      0.01 |        0.0001

...

97 | {97, 97} | {f,f} |      0.01 |        0.0001

98 | {98, 98} | {f,f} |      0.01 |        0.0001

99 | {99, 99} | {f,f} |      0.01 |        0.0001

(100 rows)

这证实了两列中有 100 种不同的组合，并且所有组合的可能性大致相同（每种组合的频率为 1%）。基本频率是根据每列统计数据计算的频率，就好像没有多列统计数据一样。如果任一列中存在任何空值，则会在空值列中进行标识。

在估计选择性时，规划器将所有条件应用于 MCV 列表中的项目，然后对匹配项的频率进行求和。有关详细信息，请参阅 src/backend/statistics/mcv.c 中的 mcv_clauselist_selectivity。

与函数依赖相比，MCV 列表有两大优点。

• 首先，该列表存储实际值，从而可以决定哪些组合是兼容的。

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 10;

QUERY PLAN

---------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1)

Filter: ((a = 1) AND (b = 10))

Rows Removed by Filter: 10000

• 其次，MCV 列表处理更广泛的子句类型，而不仅仅是函数依赖等相等子句。

例如，考虑对同一个表进行以下范围查询：

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a <= 49 AND b > 49;

QUERY PLAN

---------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual rows=0 loops=1)

Filter: ((a <= 49) AND (b > 49))

Rows Removed by Filter: 10000

五、结论

PostgreSQL的多元统计信息（MCV）作为一种重要的数据库内部机制，为查询优化器提供了关于数据列常见值及其频率的关键信息。

通过对MCV的深入理解与有效运用，数据库管理员和应用程序开发者可以显著提升查询性能，确保数据库系统在面对复杂查询和大规模数据时仍能保持高效运作。通过定期更新统计信息、合理设计索引并结合实际业务特点，充分发挥MCV在查询优化中的作用，是构建高性能PostgreSQL数据库应用的关键实践之一。

想了解更多PG相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。

想了解更多PG相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。

想了解更多PG相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。