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 时直接报错。
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;预期结果
<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 是否明显偏离。时间和计数会随数据量、缓存状态与机器环境变化。

判断慢在扫描还是过滤
出现 Seq Scan 并不自动等于优化器选错了。当条件会命中表中很大一部分记录时,全表扫描可能比访问索引后再频繁读取堆页更便宜,因此必须结合返回比例判断。
更值得警惕的是“读取很多、返回很少”。例如扫描数百万行后只留下几百行,同时 Rows Removed by Filter 很高,通常说明条件没有在合适的访问阶段缩小候选集合。若 Buffers 中 shared read 也很高,则查询还伴随明显的物理读取压力;如果读取不多但单个节点耗时异常,则应继续检查表达式计算、排序、连接或并发等待,而不是只盯着扫描类型。
主示例包含两种不同访问模式:attributes @> ... 是 JSONB 包含匹配,可以评估 GIN;(attributes ->> 'price')::numeric BETWEEN ... 是标量范围条件,更适合 B-tree 表达式索引。一个面向整个 JSONB 文档的 GIN 索引不会自动优化数值范围和排序。
检查索引是否与谓词匹配
如果颜色过滤具有足够选择性,可以为 JSONB 文档建立支持 @> 的 GIN 索引;如果价格范围是稳定且高频的查询条件,则可以补充 B-tree 表达式索引。部分索引的条件必须与业务查询保持一致,否则优化器不能安全地假定所有查询行都满足该条件。
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;预期结果
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 索引”。