PostgreSQL / 系统课时

排查 JSONB 慢查询

排查 JSONB 慢查询

JSONB 查询变慢时,不应先假定“缺一个 GIN 索引”。更可靠的做法是从真实慢 SQL 出发,用实际执行计划核对扫描方式、过滤规模、估算偏差和缓冲区访问,再判断问题来自索引不匹配、低选择性、类型设计还是 I/O 压力。

学习目标

  • 用 EXPLAIN ANALYZE 和缓冲区统计定位 JSONB 查询的主要耗时节点
  • 根据 JSONB 谓词和访问方式判断现有索引是否真正匹配
  • 区分扫描、过滤、估算偏差和 I/O 问题,并用同一条 SQL 验证修复效果

从真实慢 SQL 保留证据

排查时先固定业务实际使用的 WHERE 条件、参数和排序方式,不要为了测试方便把 SQL 改成另一种查询。JSONB 的 @>、键存在、路径取值和类型转换对应不同索引能力,一个操作符变化就可能改变计划。

下面以“筛选黑色商品,再限制 JSONB 中价格区间”为主示例。假设已有 product_catalog(id bigint primary key, attributes jsonb),因此这是依赖现有业务表的片段示例。价格条件同时限定 JSON 值必须为数字,避免历史脏数据在转换为 numeric 时直接报错。

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id
FROM product_catalog
WHERE attributes @> '{"color":"black"}'::jsonb
  AND jsonb_typeof(attributes -> 'price') = 'number'
  AND (attributes ->> 'price')::numeric BETWEEN 100 AND 300;

预期结果

text
<PLAN NODE> (actual time=<TIME> rows=<VALUE> loops=1)
  Filter: <JSONB_FILTER>
  Rows Removed by Filter: <VALUE>
  Buffers: shared hit=<VALUE> read=<VALUE>
Planning Time: <TIME>
Execution Time: <TIME>

节点名称取决于当前索引、统计信息和数据分布,可能是 Seq Scan、Bitmap Heap Scan 或其他扫描节点。不要只看最末尾的 Execution Time,还要找出哪个节点读取的数据最多、过滤掉多少行,以及估算行数与 actual rows 是否明显偏离。时间和计数会随数据量、缓存状态与机器环境变化。

JSONB 慢查询的分层定位路径

判断慢在扫描还是过滤

出现 Seq Scan 并不自动等于优化器选错了。当条件会命中表中很大一部分记录时,全表扫描可能比访问索引后再频繁读取堆页更便宜,因此必须结合返回比例判断。

更值得警惕的是“读取很多、返回很少”。例如扫描数百万行后只留下几百行,同时 Rows Removed by Filter 很高,通常说明条件没有在合适的访问阶段缩小候选集合。若 Buffers 中 shared read 也很高,则查询还伴随明显的物理读取压力;如果读取不多但单个节点耗时异常,则应继续检查表达式计算、排序、连接或并发等待,而不是只盯着扫描类型。

主示例包含两种不同访问模式:attributes @> ... 是 JSONB 包含匹配,可以评估 GIN;(attributes ->> 'price')::numeric BETWEEN ... 是标量范围条件,更适合 B-tree 表达式索引。一个面向整个 JSONB 文档的 GIN 索引不会自动优化数值范围和排序。

检查索引是否与谓词匹配

如果颜色过滤具有足够选择性,可以为 JSONB 文档建立支持 @> 的 GIN 索引;如果价格范围是稳定且高频的查询条件,则可以补充 B-tree 表达式索引。部分索引的条件必须与业务查询保持一致,否则优化器不能安全地假定所有查询行都满足该条件。

sql
CREATE INDEX idx_product_catalog_attributes_gin
ON product_catalog USING gin (attributes);

CREATE INDEX idx_product_catalog_price
ON product_catalog (((attributes ->> 'price')::numeric))
WHERE jsonb_typeof(attributes -> 'price') = 'number';

ANALYZE product_catalog;

预期结果

text
CREATE INDEX
CREATE INDEX
ANALYZE

这里的价格索引只收录 JSON 数字,因此主查询也显式包含 jsonb_typeof(attributes -> 'price') = 'number'。如果历史数据存在 "price":"unknown" 等字符串,应该先治理类型漂移,而不是依靠强制转换或索引掩盖数据模型问题。

判断估算偏差和数据分布

慢查询也可能来自优化器严重低估或高估结果行数。若计划估算与 actual rows 相差很大,连接顺序、扫描方式以及内存使用都可能因此选择不佳。先执行 ANALYZE 排除统计信息过旧,再判断数据是否高度倾斜。

例如某个 color 值占据绝大多数记录时,即使存在索引,对这个值也可能没有足够选择性。对于长期稳定参与过滤的 JSONB 标量,比不断给整个文档增加索引更可靠的治理方式,是固定表达式和类型,或把真正稳定、关键的业务字段提升为普通关系列。

用相同 SQL 验证修复

索引创建和统计信息更新后,重新执行前面的同一条 EXPLAIN (ANALYZE, BUFFERS, COSTS OFF),不要换参数或改写谓词。实际计划可能组合 GIN 与 B-tree,也可能只使用其中一个索引再过滤另一个条件,这取决于数据分布和成本估算。

验收重点不是强求出现某个固定节点,而是比较修复前后的候选行规模、Rows Removed by Filter、缓冲区读取和执行时间。如果读取与过滤规模明显下降,并且索引访问方式与查询谓词匹配,才说明优化真正命中了瓶颈。

JSONB 慢查询应按固定顺序处理:锁定真实 SQL,查看实际执行计划和缓冲区访问,判断扫描与过滤规模,再核对谓词、索引和统计信息,最后才决定补索引还是调整数据模型。这样可以避免把所有性能问题都误诊为“缺 GIN 索引”。

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

目录与阅读设置

记录一个想法

排查 JSONB 慢查询

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

登录哪都通