多元相关性可以通过一个非常简单的数据集来演示——一个有两列的表,这两列都包含相同的值
CREATE TABLE t (a INT, b INT); INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i); ANALYZE t;
如 第 14.2 节 中所述,规划器可以使用从 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
对多列集合基数的估计也会出现类似的问题,例如 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-distinct 计数,估计值得到了很大改善
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)
如 第 76.2.1 节 中所述,函数依赖是一种非常便宜且高效的统计类型,但它们的主要限制是其全局性(仅跟踪列级别的依赖性,而不是各个列值之间的依赖性)。
本节介绍了 MCV(最常见值)列表的多元变体,这是 第 76.1 节 中描述的按列统计的直接扩展。这些统计信息通过存储单个值来解决此限制,但它自然会更昂贵,无论是构建 ANALYZE
中的统计信息、存储还是规划时间方面。
让我们再次查看 第 76.2.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%)。基本频率是从每列统计数据中计算出的频率,就好像没有多列统计数据一样。如果任一列中存在任何空值,则会在nulls
列中识别出这一点。
在估计选择性时,规划器对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