PostgreSQL 教程

Appearance

76.2. 多元统计示例# ​

76.2.1. 函数依赖性
76.2.2. 多元 N-Distinct 计数
76.2.3. MCV 列表

76.2.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;

如第 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

76.2.2. 多元 N-Distinct 计数# ​

对多列集合基数的估计也会出现类似的问题，例如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.3. MCV 列表# ​

如第 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