PostgreSQL 教程: 优化多变量匹配的选择率估计

三月 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;

在该示例中,规划器将idval的可能的配对的数量,估计为不同的id值的数量与不同的val值的数量的乘积。然而,实际的配对数量要低得多,因为idval之间存在一些相关性:

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 还允许您在定义多变量和表达式统计时,不仅使用列名,还可以使用任意表达式。

了解更多

PostgreSQL 优化