三月 4, 2024
摘要：在本教程中，您将学习如何优化非重复值的估计数量。
介绍
一个列中的非重复值的数量，存储在pg_stats中的n_distinct字段中。
如果n_distinct为负，则其绝对值表示非重复值数量的占比。例如，值为 -1 意味着列中的每一项都是唯一的。当非重复值的数量达到行数的 10% 或更多时，分析器将切换到比率模式。在这点上，它会假设修改数据时，比例通常保持不变。
示例
让我们开始设置一个小的测试用例，从连续高斯随机分布的数值序列中生成一些样本数据：
CREATE TABLE test6 (id integer, val integer);
INSERT INTO test6 (id, val)
SELECT i,
CAST(cos(2 * pi() * random()) * sqrt(-2 * ln(random())) * 100 AS integer)
FROM generate_series(1, 100000) AS s(i);
ANALYZE test6;
在数据均匀分布的情况下，非重复值的数量很有用。考虑 “列变量 = 表达式” 子句的基数估计。如果表达式的值在规划阶段是未知的，规划器会假设表达式可能从列中以相同的概率返回任何值。
EXPLAIN
SELECT * FROM test6 WHERE val = (
SELECT CAST(random() * 200 AS integer)
);
QUERY PLAN
----------------------------------------------------------
Seq Scan on test6 (cost=0.02..1498.02 rows=154 width=8)
Filter: (val = $0)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=4)
(4 rows)
InitPlan 节点只执行一次，然后使用节点返回值代替主计划中的 $0。
SELECT round(reltuples / s.n_distinct) AS rows
FROM pg_class
JOIN pg_stats s ON s.tablename = relname
WHERE s.tablename = 'test6'
AND s.attname = 'val';
rows
------
154
(1 row)
如果所有数据均匀分布，这些统计数值（连同最小值和最大值）将足以进行准确的估计。不幸的是，这种估计对非均匀分布的情况不太有效，这样的分布也更常见：
SELECT min(cnt), round(avg(cnt)) AS avg, max(cnt) FROM (
SELECT val, count(*) AS cnt
FROM test6 GROUP BY val
) t;
min | avg | max
-----+-----+-----
1 | 138 | 438
(1 row)
优化非重复值数量的统计信息
在n_distinct的设置不是很理想时，通过对其进行调整，您可以影响查询规划器以生成更好的计划。之所以需要这样做，主要是对于一些大表，统计信息收集器不会查询整个表来确定统计值。统计信息收集器通常最多查询一个表的 10-30%。
让统计收集器来自动地做所有这些事情总是很好的，特别是如果您有一个不断更新的表，并且非重复值的计数可能波动很大。对于静态表，您可能会想要手动设置它们。所以你怎么知道你是否应该进行干涉呢？其实，你可以检查存储在pg_stats中的n_distinct字段中的当前值，以及表中的实际非重复值的数量：
-- estimated number of distinct values
SELECT n_distinct FROM pg_stats
WHERE tablename = 'test6' AND attname = 'val';
-- actual number of distinct values
SELECT count(DISTINCT val) FROM test6;
如果非重复值的数量计算不准确（可能因为样本碰巧不具有代表性），您可以像这样设置该值：
ALTER TABLE table_name ALTER COLUMN column_name SET (n_distinct = 100);