68.2. 多变量统计例子

68.2.1. 函数依赖
68.2.2. N 个不同变量的计数

68.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

68.2.2. N 个不同变量的计数

估计多列集合的基数时会出现类似的问题,例如由GROUP BY 子句生成的组的数量。当GROUP BY列出单个列时, n个不同估计值(作为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)