PostgreSQL / 系统课时

使用 ndistinct 扩展统计

使用 ndistinct 扩展统计

当查询按多个相关列分组时,单列统计只能知道每一列各有多少不同值,却不知道这些值实际组成了多少种组合,结果常把分组数量估得过高。本课创建 ndistinct 扩展统计,并用 GROUP BY 的执行计划验证组合基数估算如何改善。

学习目标

  • 识别多列组合不同值数量估算错误
  • 创建 ndistinct 扩展统计并查看收集结果
  • 用聚合节点的估算行数验证 GROUP BY 基数是否修正

ndistinct 解决的是组合基数

假设 a 有 100 个不同值,b 也有 100 个不同值。只看单列统计,优化器无法知道二者是自由组合、部分组合,还是几乎一一对应。对 GROUP BY a, b 来说,这个差别决定了最终可能产生 100、1000 甚至 10000 个组。

ndistinct 保存的是两列或多列组合后的不同值数量。它特别适合修复 GROUP BY 等需要估算“会产生多少组”的场景。估算更准确后,聚合节点的成本、内存需求以及候选计划比较也会更可信。

观察 GROUP BY 的错误估算

下面仍构造 10000 行数据,但让 a 与 b 完全相同。虽然两列各有 100 个不同值,真实的 (a, b) 组合也只有 100 种,而不是两个单列基数的简单组合。

sql
DROP TABLE IF EXISTS planner_nd_lab;

CREATE TABLE planner_nd_lab (
    a integer,
    b integer
);

INSERT INTO planner_nd_lab (a, b)
SELECT i % 100, i % 100
FROM generate_series(1, 10000) AS s(i);

ANALYZE planner_nd_lab;

EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*)
FROM planner_nd_lab
GROUP BY a, b;

预期输出中,HashAggregate 的 actual rows 是 100,也就是实际分组数;没有多列 n-distinct 信息时,估算行数通常会明显偏高,在这个数据集上可见约 1000 的估算。

text
HashAggregate  (cost=<COST> rows=1000 width=16) (actual rows=100 loops=1)
  Group Key: a, b
  ->  Seq Scan on planner_nd_lab  (cost=<COST> rows=10000 width=8) (actual rows=10000 loops=1)

这里关注的是聚合节点的 rows,不是底层扫描的 10000 行。扫描行数本来就是准确的,真正出错的是优化器对“扫描完成后会形成多少个组”的判断。

创建 ndistinct 并验证组合统计

创建统计对象后要再次执行 ANALYZE。对于包含两列的对象,pg_stats_ext.n_distinct 会记录这两列组合的估算不同值数量;如果统计对象包含更多列,PostgreSQL 会为其中多个两列及以上组合收集 n-distinct 信息。

sql
CREATE STATISTICS planner_nd_stats (ndistinct)
ON a, b
FROM planner_nd_lab;

ANALYZE planner_nd_lab;

SELECT statistics_name, attnames, kinds, n_distinct
FROM pg_stats_ext
WHERE statistics_name = 'planner_nd_stats';

EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*)
FROM planner_nd_lab
GROUP BY a, b;

预期结果应看到统计类型 d,组合 (a,b) 的不同值数量约为 100;随后 HashAggregate 的估算行数会从约 1000 收敛到 100。成本数值可能因环境不同而变化。

text
statistics_name  | attnames | kinds | n_distinct
-----------------+----------+-------+----------------
planner_nd_stats | {a,b}    | {d}   | {"1, 2": 100}

HashAggregate  (cost=<COST> rows=100 width=16) (actual rows=100 loops=1)
  Group Key: a, b
  ->  Seq Scan on planner_nd_lab  (cost=<COST> rows=10000 width=8) (actual rows=10000 loops=1)

n_distinct 中的 1, 2 是该表内对应列的属性编号,而不是 SQL 查询中的位置序号。实际治理时通常优先通过 pg_stats_ext.attnames 核对统计对象覆盖了哪些列,再把组合数量与业务数据关系对照。

和 dependencies 不要混用概念

dependencies 主要回答“一个过滤列是否能决定另一个过滤列”,用于修正相关等值条件的选择率;ndistinct 主要回答“这些列放在一起到底有多少种不同组合”,用于修正多列分组等组合基数。二者可以定义在同一个统计对象中,但解决的问题不同。

如果慢查询的关键误差出现在 HashAggregate、GroupAggregate 等节点的分组行数,优先检查 ndistinct;如果误差出现在多个相关等值过滤条件合并后,则优先考虑 dependencies 或 MCV。不要因为两列“看起来相关”就盲目创建所有统计类型。

统计对象建立后仍需要持续维护:数据分布变化越快,越要确认自动分析是否足够及时。验收时始终回到同一条 EXPLAIN (ANALYZE),比较扩展统计前后的估算组数与实际组数,而不是只检查 pg_stats_ext 里是否出现了一行记录。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用 ndistinct 扩展统计

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通