二月 28, 2024
摘要：在本教程中，您将学习如何优化等值的选择率估计。
目录
介绍
对于列值非均匀分布的情况，为了提高条件谓词匹配行数的估计精度，分析器会收集关于最常见值（MCV）及其频率的统计信息。这些值会存储在 pg_stats 系统表的most_common_vals和most_common_freqs列中。
示例
让我们开始设置一个小的测试用例，从连续高斯随机分布的数值序列中生成一些样本数据：
CREATE TABLE test1 (id integer, val integer);
INSERT INTO test1 (id, val)
SELECT i,
CAST(cos(2 * pi() * random()) * sqrt(-2 * ln(random())) * 100 AS integer)
FROM generate_series(1, 10000) AS s(i);
ANALYZE test1;
SHOW default_statistics_target;
default_statistics_target
---------------------------
100
(1 row)
创建了一个包含 10000 行的简单表。让我们检查下表列val的统计信息中最常见的值：
WITH valrows AS (
SELECT val, count(*) AS num FROM test1
GROUP BY val
ORDER BY num DESC
), attstats AS (
SELECT reltuples, s.most_common_freqs AS mcf,
s.most_common_vals::text::integer[] AS mcv,
s.n_distinct
FROM pg_class JOIN pg_stats s ON s.tablename = relname
WHERE s.tablename = 'test1' AND s.attname = 'val'
)
SELECT val, num AS actual_rows,
CASE WHEN pos IS NULL
THEN round(reltuples *
(1 - (SELECT sum(freq) FROM unnest(mcf) AS freq)) /
(n_distinct - 100))
ELSE round(reltuples * mcf[pos])
END AS estimated_rows
FROM (
SELECT array_position(mcv, val) AS pos, *
FROM valrows, attstats
) AS res
LIMIT 200;
这会输出如下结果：
val | actual_rows | estimated_rows
------+-------------+----------------
33 | 54 | 54
-28 | 51 | 51
19 | 50 | 50
-9 | 49 | 49
39 | 48 | 48
... -- skip to 95 line --
-35 | 35 | 35
55 | 35 | 35
-20 | 35 | 35
62 | 35 | 13
93 | 35 | 13
56 | 35 | 13
-37 | 35 | 35
38 | 35 | 35
24 | 35 | 35
75 | 35 | 13
44 | 34 | 13
15 | 34 | 13
...
(200 rows)
估计 “column = expression” 的选择率非常简单：规划器只需从most_common_vals数组中找到对应的值，然后将总行数乘以most_common_freqs数组中相同位置的频率。
EXPLAIN (analyze) SELECT * FROM test1 WHERE val = 33;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..150.00 rows=54 width=8) (actual time=0.044..1.299 rows=54 loops=1)
Filter: (val = 33)
Rows Removed by Filter: 9946
Planning time: 0.183 ms
Execution time: 1.325 ms
(5 rows)
EXPLAIN (analyze) SELECT * FROM test1 WHERE val = 62;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..150.00 rows=13 width=8) (actual time=0.049..1.262 rows=35 loops=1)
Filter: (val = 62)
Rows Removed by Filter: 9965
Planning time: 0.121 ms
Execution time: 1.278 ms
(5 rows)
如果列值出现在 MCV 列表中，则估计行数会和实际行数很接近。
MCV 列表也可用于不等式的选择率估计：为了得到 “column < value” 的选择率，规划器搜索most_common_vals中所有低于给定值的值，然后将它们在most_common_freqs中的频率加在一起。
优化表列的统计质量
当非重复值的数量较少时，常见值统计信息的效果最好。MCV 数组的最大大小由 default_statistics_target 定义，该参数在分析期间控制行样本的大小。
在某些情况下，将值（即数组大小）增加到超过默认值，可提供更准确的估计。您可以对表列设置此值，例如：
ALTER TABLE test1 ALTER COLUMN val SET STATISTICS 1000;
行样本大小也会随之增加，但仅限于此表。
常见值数组会存储值本身，并且根据值的不同，可能会占用大量空间。这就是为什么会将大小超过 1 kB 的值排除在分析和统计信息之外的原因。这让pg_statistic系统表的大小保持在可控范围内，而不让规划器处理负担过大。如此大的值通常是非重复的，也不太需要包含在most_common_vals中。
设置更高的估计精度，只有在可提高规划质量时才是有益的。如果没有合理的理由，增加 default_statistics_target 可能会降低分析和规划的速度，但对优化没有影响。
另一方面，降低参数（一直降低到零）可能会提高分析和规划速度，但也可能导致产生低质量的执行计划，因此这种“时间上的节省”很少是合理的。