PostgreSQL / 系统课时

分析选择性与统计信息

分析选择性与统计信息

JSONB 查询慢不一定是缺索引,也可能是规划器错误估算了条件命中行数。本课用倾斜的 color 分布对比 EXPLAIN ANALYZE 的估算与实际行数,再为同一提取表达式收集统计信息,观察统计质量如何影响计划成本判断。

学习目标

  • 会对比 EXPLAIN (ANALYZE, BUFFERS) 中的估算行数与实际行数。
  • 理解 JSONB 提取表达式缺少统计信息时为什么容易产生选择性偏差。
  • 会为高频 JSONB 表达式建立统计对象,并用误差变化而非固定数字判断效果。

用倾斜数据建立可复现基线

下面一次性构造 100000 条商品数据,其中 90000 条为 black、10000 条为 red。先只执行表级 ANALYZE,观察表达式过滤的估算;再为完全相同的表达式建立统计对象并重新分析。示例关闭当前会话的并行查询,减少不同环境下计划形态的干扰。

sql
DROP TABLE IF EXISTS product_catalog;

CREATE TABLE product_catalog (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    attributes jsonb NOT NULL
);

INSERT INTO product_catalog (attributes)
SELECT jsonb_build_object(
    'color', CASE WHEN i % 10 = 0 THEN 'red' ELSE 'black' END,
    'category', 'demo'
)
FROM generate_series(1, 100000) AS g(i);

ANALYZE product_catalog;
SET max_parallel_workers_per_gather = 0;

SELECT attributes ->> 'color' AS color, count(*)
FROM product_catalog
GROUP BY 1
ORDER BY 1;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes ->> 'color' = 'red';

CREATE STATISTICS product_color_stats
ON (attributes ->> 'color')
FROM product_catalog;

ANALYZE product_catalog;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM product_catalog
WHERE attributes ->> 'color' = 'red';

RESET max_parallel_workers_per_gather;

预期输出

text
 color | count
-------+-------
 black | 90000
 red   | 10000

-- 第一次 EXPLAIN 的实际命中行数
Seq Scan on product_catalog  (cost=<VALUE> rows=<EST_ROWS_BEFORE> width=<VALUE>) (actual time=<TIME>..<TIME> rows=10000 loops=1)
  Filter: ((attributes ->> 'color'::text) = 'red'::text)
  Rows Removed by Filter: 90000

-- 建立表达式统计并重新 ANALYZE 后
Seq Scan on product_catalog  (cost=<VALUE> rows=<EST_ROWS_AFTER> width=<VALUE>) (actual time=<TIME>..<TIME> rows=10000 loops=1)
  Filter: ((attributes ->> 'color'::text) = 'red'::text)
  Rows Removed by Filter: 90000

<TIME>、<VALUE>、<EST_ROWS_BEFORE> 和 <EST_ROWS_AFTER> 会随 PostgreSQL 版本、统计采样与运行环境变化。稳定的事实是实际命中 10000 行;验收重点是比较两次计划中估算行数与 actual rows 的误差,而不是要求第二次估算固定等于或“必须接近” 10000。

为什么 JSONB 表达式容易估错

ANALYZE 会为普通列收集规划器需要的统计信息,但 attributes ->> 'color' 是查询时计算出的表达式。只有 JSONB 基础列的统计数据,并不意味着规划器已经掌握该提取结果中 red 与 black 的频率,所以等值过滤可能退回通用选择性假设。

选择性是满足条件的行数占输入行数的比例。本例真实选择性为 10%。如果基表过滤节点先低估或高估了行数,这个误差还会继续进入连接、聚合、排序等上层节点的成本计算。

JSONB 条件选择性估算偏差如何传导到执行计划

表达式统计信息解决什么

CREATE STATISTICS 可以让 PostgreSQL 为常用表达式收集统计数据,但真正填充和更新这些数据仍依赖 ANALYZE。统计对象不是索引,不保存可直接定位数据行的访问结构,因此它改善的是选择性与行数估算,不会强制查询改用某种扫描方式;本例前后都可能继续是顺序扫描。

统计数据来自采样,本身就是近似值。即使基础数据不变,重新执行 ANALYZE 后得到的频率和计划估算也可能略有波动,精度还受统计目标、表规模和数据分布影响。因此应看估算误差是否明显缩小,而不是把某个精确的 rows 数字写成稳定预期。

从最早的偏差节点开始排查

规划器会把基表过滤后的预计行数传给上层节点。若预计只返回少量行,可能倾向嵌套循环和重复索引查找;实际却返回上万行时,总工作量会被迅速放大。反过来,高估结果集也可能让规划器过早选择面向大批量数据的路径。

排查 JSONB 慢查询时,不要只问“是否走索引”。先在 EXPLAIN (ANALYZE, BUFFERS) 中寻找最早出现明显 rows 与 actual rows 偏差的节点,再判断是表达式缺少统计、查询表达式与统计对象不一致,还是确实需要索引改变访问成本。对稳定、高频且分布倾斜的 JSONB 键,表达式统计值得长期维护;批量导入或大规模更新后,也应确认 ANALYZE 已及时刷新。

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

目录与阅读设置

记录一个想法

分析选择性与统计信息

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

登录哪都通