函数依赖
可以用非常简单的数据集演示多变量相关性：创建一张有两列的表，两列包含完全相同的值。
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, BUFFERS 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.00 loops=1)
Filter: (a = 1)
Rows Removed by Filter: 9900
规划器检查条件，确定该子句的选择率为 1%。将估算与实际行数比较，可见估算非常准确；这张表很小，因此这里甚至完全一致。如果把 WHERE 条件改为针对 b 列，执行计划仍然相同。但是，如果将相同条件同时应用于两列，并用 AND 连接，会发生什么？
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS 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.00 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, BUFFERS 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.00 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900
多变量不同值数
估算多列集合的基数时，也会出现类似问题。例如，GROUP BY 子句将产生多少组？当 GROUP BY 只有一列时，不同值数（n-distinct）的估算非常准确；该估算显示在 HashAggregate 节点预计返回的行数中：
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a;
QUERY PLAN
-----------------------------------------------------------------------------------------
HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100.00 loops=1)
Group Key: a
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=4) (actual rows=10000.00 loops=1)
但是，在没有多变量统计时，下面这个按两列进行 GROUP BY 的查询，其组数估算偏差达到一个数量级：
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100.00 loops=1)
Group Key: a, b
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1)
重新定义统计对象，使其包含这两列的联合不同值数，估算就会明显改善：
DROP STATISTICS stts;
CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100.00 loops=1)
Group Key: a, b
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000.00 loops=1)
MCV 列表
前面的函数依赖统计成本低、效率高，但主要限制是它只追踪列层面的全局依赖关系，而不追踪各个具体列值之间的关系。
多变量 MCV（most-common values，最常见值）列表是单列统计中 MCV 列表的直接扩展。它通过保存具体值来突破上述限制，但相应成本也更高，包括在 ANALYZE 中构建统计的成本、存储成本和规划时间。
再次查看函数依赖示例中的查询，这次在相同的列集合上创建 MCV 列表。务必先删除函数依赖统计，确保规划器使用新创建的统计：
DROP STATISTICS stts;
CREATE STATISTICS stts2 (mcv) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS 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.00 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%。base_frequency 是根据单列统计、在没有多列统计的假设下计算出的频率。如果任意一列有空值，则会在 nulls 列中标识。
估算选择率时，规划器会把所有条件应用到 MCV 列表中的各项，再将匹配项的频率相加。实现细节可查看 PostgreSQL 源码 src/backend/statistics/mcv.c 中的 mcv_clauselist_selectivity。
与函数依赖统计相比，MCV 列表有两个主要优势。首先，它保存实际值，因此可以判断哪些值组合相容：
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS 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.00 loops=1)
Filter: ((a = 1) AND (b = 10))
Rows Removed by Filter: 10000
其次，MCV 列表支持更广泛的子句类型，而函数依赖统计只支持等值条件等有限形式。比如，对相同表执行以下范围查询：
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS 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.00 loops=1)
Filter: ((a <= 49) AND (b > 49))
Rows Removed by Filter: 10000
以上执行计划与输出均为 PostgreSQL 官方文档中的示例，具体成本、格式和估算结果会随版本、数据和统计样本变化。
来源：PostgreSQL 18 文档 69.2：Multivariate Statistics Examples。中文改编日期：2026-10-03；正文已翻译，SQL 与原文示例输出保持不变。许可证：PostgreSQL License。
Portions Copyright © 1996-2026, The PostgreSQL Global Development Group Portions Copyright © 1994, The Regents of the University of California Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
暂无评论内容