三月 3, 2024
摘要：在本教程中，您将学习如何优化多变量匹配的选择率估计。
目录
PostgreSQL 10 引入了同时从多个列收集统计信息的能力，也称为多变量统计信息。这需要手动来生成需要的扩展统计信息。
有三种类型的多变量统计信息。
列之间存在函数式依赖
当某一列中的值由另一列中的值（全部或部分）确定时，并且在查询中存在引用这两列的条件时，结果行数的计算会出现低估。让我们开始设置一个小的测试用例：
CREATE TABLE test3 (id integer, val varchar);
INSERT INTO test3 (id, val)
SELECT i % 100, 'PG00' || i % 100
FROM generate_series(1, 10000) AS s(i);
CREATE INDEX idx_test3_id ON test3 (id);
ANALYZE test3;
下面是一个有两个条件的例子：
SELECT count(*) FROM test3
WHERE val = 'PG0027' AND id = 27;
count
-------
100
(1 row)
估计值明显低于实际值，仅为 1 行：
EXPLAIN SELECT * FROM test3
WHERE val = 'PG0027' AND id = 27;
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test3 (cost=5.04..62.45 rows=1 width=10)
Recheck Cond: (id = 27)
Filter: ((val)::text = 'PG0027'::text)
-> Bitmap Index Scan on idx_test3_id (cost=0.00..5.04 rows=100 width=0)
Index Cond: (id = 27)
(5 rows)
这就是典型的谓词相关性问题。规划器预期谓词之间是相互独立的，并将结果选择率计算为多个条件选择率的乘积，这些条件由AND组合。在位图堆扫描中应用val条件后，位图索引扫描中计算id条件的估计行数大幅下降。
很自然地，一个id数已经明确地定义了val值，因此val条件实际上是多余的。这就是扩展的函数式依赖统计信息，可以帮助改进估计的地方。
让我们来为这两个列创建扩展的函数式依赖统计信息：
CREATE STATISTICS test3_dep (dependencies) ON id, val FROM test3;
再次分析表，现在有了新的统计信息，估计得到了改善：
ANALYZE test3;
EXPLAIN SELECT * FROM test3
WHERE val = 'PG0027' AND id = 27;
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test3 (cost=5.06..62.48 rows=100 width=10)
Recheck Cond: (id = 27)
Filter: ((val)::text = 'PG0027'::text)
-> Bitmap Index Scan on idx_test3_id (cost=0.00..5.04 rows=100 width=0)
Index Cond: (id = 27)
(5 rows)
统计信息存储在系统表中，可以使用以下命令来显示：
SELECT dependencies
FROM pg_stats_ext WHERE statistics_name = 'test3_dep';
dependencies
------------------------------------------
{"1 => 2": 1.000000, "2 => 1": 1.000000}
(1 row)
数字 1 和 2 是来自pg_attribute的表列编号。它们旁边的值表示函数式依赖程度，从 0（独立）到 1（第二列中的值完全由第一列中的值确定）。
多变量的不同值数目
对来自多个列的值的不同组合的数量进行统计，可以显著提高对多个列的GROUP BY操作的基数估计的精确性。让我们开始设置一个小的测试用例：
CREATE TABLE test4 (id integer, val varchar);
INSERT INTO test4 (id, val)
SELECT i % 100, 'PG00' || (i % 40 + 1)
FROM generate_series(1, 100000) AS s(i);
ANALYZE test4;
在该示例中，规划器将id和val的可能的配对的数量，估计为不同的id值的数量与不同的val值的数量的乘积。然而，实际的配对数量要低得多，因为id和val之间存在一些相关性：
SELECT count(*) FROM (
SELECT DISTINCT id, val FROM test4
) t;
count
-------
200
(1 row)
EXPLAIN
SELECT DISTINCT id, val FROM test4;
QUERY PLAN
--------------------------------------------------------------------
HashAggregate (cost=2041.00..2081.00 rows=4000 width=10)
Group Key: id, val
-> Seq Scan on test4 (cost=0.00..1541.00 rows=100000 width=10)
(3 rows)
让我们为不同值的数量创建一个扩展统计：
CREATE STATISTICS test4_nd(ndistinct) ON id, val FROM test4;
ANALYZE test4;
EXPLAIN
SELECT DISTINCT id, val FROM test4;
QUERY PLAN
--------------------------------------------------------------------
HashAggregate (cost=2041.00..2043.00 rows=200 width=10)
Group Key: id, val
-> Seq Scan on test4 (cost=0.00..1541.00 rows=100000 width=10)
(3 rows)
统计信息会存储在系统表中：
SELECT n_distinct
FROM pg_stats_ext WHERE statistics_name = 'test4_nd';
n_distinct
---------------
{"1, 2": 200}
(1 row)
多变量的最常见值列表
当值的分布不均匀时，单独的函数式依赖统计数据可能还不够，因为估计值会根据特定的配对值而显著变化。让我们开始设置一个小的测试用例：
CREATE TABLE test5 (id integer, val varchar);
INSERT INTO test5 (id, val)
SELECT i % 50, 'PG00' || (i % 20 + 1)
FROM generate_series(1, 10000) AS s(i);
ANALYZE test5;
考虑一个这样的例子，设定id等于 35，val等于 ‘PG0016’，规划器会不准确地估计行数：
SELECT count(*) FROM test5
WHERE val = 'PG0016' AND id = 35;
count
-------
100
(1 row)
EXPLAIN SELECT * FROM test5
WHERE val = 'PG0016' AND id = 35;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test5 (cost=0.00..205.00 rows=10 width=10)
Filter: (((val)::text = 'PG0016'::text) AND (id = 35))
(2 rows)
我们可以使用多变量 MCV 列表统计信息来改进估计的精确性：
CREATE STATISTICS test5_mcv(mcv) ON id, val FROM test5;
ANALYZE test5;
EXPLAIN SELECT * FROM test5
WHERE val = 'PG0016' AND id = 35;
QUERY PLAN
----------------------------------------------------------
Seq Scan on test5 (cost=0.00..205.00 rows=100 width=10)
Filter: (((val)::text = 'PG0016'::text) AND (id = 35))
(2 rows)
现在系统表中会有频率数据供规划器来使用：
SELECT values, frequency
FROM pg_statistic_ext stx
JOIN pg_statistic_ext_data stxd ON stx.oid = stxd.stxoid,
pg_mcv_list_items(stxdmcv) m
WHERE stxname = 'test5_mcv'
AND values = '{35,PG0016}';
values | frequency
-------------+-----------
{35,PG0016} | 0.01
(1 row)
一个多变量的最常见值列表可存储 default_statistics_target 个值，就像常规的 MCV 列表一样。如果参数是在列级别定义的，则使用最高的值。
与扩展表达式统计一样，您可以更改列表的大小（在 PostgreSQL 13 及更高版本中）：
ALTER STATISTICS ... SET STATISTICS ...;
在这些示例中，多变量统计信息只收集了两个列，但您可以根据需要为任意多个列收集这些信息。
您还可以将不同类型的统计信息汇总到单个扩展统计对象中。要做到这一点，只需在创建对象时列出所需的统计信息类型，并以逗号分隔。如果没有定义特定的统计信息类型，系统会一次性收集所有可用的统计信息。
PostgreSQL 14 还允许您在定义多变量和表达式统计时，不仅使用列名，还可以使用任意表达式。