PostgreSQL / 技术文章

PostgreSQL JSONB 查询变慢怎么排查

JSONB 查询变慢时,最危险的做法是看到 Seq Scan 就立刻加索引,或看到 GIN 索引很大就直接重建。本文面向负责 PostgreSQL 性能治理的 DBA、SRE 与后端工程师,给出一条从真实慢 SQL、执行计划和缓冲区证据出发的排查路径,最终把问题归因到查询谓词、索引匹配、选择性估算、数据增长或写入膨胀,并据此选择可验证的修复动作。

先确认到底是哪条 SQL 变慢

排查应从线上真实 SQL 开始,而不是先研究表结构。先固定完整查询文本、绑定参数、执行频率、典型耗时和返回行数,因为同一个 SQL 模板在不同参数下可能走完全不同的访问路径。

如果启用了 pg_stat_statements,可以先找总耗时高、平均耗时异常或近期调用次数明显增加的语句:

sql
SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
WHERE query ILIKE '%jsonb%'
   OR query ILIKE '%metadata%'
   OR query ILIKE '%attributes%'
ORDER BY total_exec_time DESC
LIMIT 20;

不要只看平均耗时。一次执行 2 秒但每天只跑一次的后台语句,与平均 40 毫秒却每秒执行数百次的接口 SQL,对系统的风险完全不同。还要确认问题是单条 SQL 本身变慢,还是调用量上升后暴露了原有低效访问路径。

拿到目标 SQL 后,保留真实谓词。例如下面三类写法虽然都在访问 JSONB,但需要的索引完全不同:

sql
SELECT id
FROM event_store
WHERE metadata @> '{"country":"US"}'::jsonb;

SELECT id
FROM event_store
WHERE metadata->>'country' = 'US';

SELECT id
FROM event_store
WHERE metadata ? 'country';

第一条是包含查询,第二条是标量表达式等值查询,第三条是键存在查询。排查时必须以线上真实操作符为准,不能看到字段是 JSONB 就默认 GIN 索引一定适用。

用 EXPLAIN ANALYZE 判断时间花在哪里

拿到可安全复现的 SQL 后,优先执行带缓冲区信息的执行计划。生产环境使用 EXPLAIN ANALYZE 前要确认语句没有副作用;对于 UPDATE、DELETE 等写操作,应在测试环境复现,或使用不执行语句的 EXPLAIN 先观察计划。

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, metadata
FROM event_store
WHERE metadata @> '{"country":"US"}'::jsonb;

重点不是机械寻找 Seq Scan,而是同时观察四组证据:实际行数与估算行数是否接近、扫描了多少行才留下目标结果、shared hit 与 shared read 是否异常、时间主要消耗在哪个执行节点。

例如出现下面的计划特征时,应优先怀疑扫描范围过大,而不是数据库 CPU 本身不足:

text
Seq Scan on event_store
  Filter: (metadata @> '{"country": "US"}'::jsonb)
  Rows Removed by Filter: 4800000
  Buffers: shared hit=12000 read=86000
  Execution Time: 1840.000 ms

大量 Rows Removed by Filter 表示 PostgreSQL 读取了很多无关记录;大量 shared read 表示这些数据还需要从存储层读取。此时应继续判断为什么没有形成更小的候选集合。

如果计划使用 GIN,常见路径是 Bitmap Index Scan 加 Bitmap Heap Scan:

text
Bitmap Heap Scan on event_store
  Recheck Cond: (metadata @> '{"country": "US"}'::jsonb)
  -> Bitmap Index Scan on idx_event_metadata_gin
       Index Cond: (metadata @> '{"country": "US"}'::jsonb)

出现这条路径并不代表性能一定正常。如果条件本身命中几十个百分点的数据,即使成功使用索引,也可能需要访问大量 heap page,性能仍然会随着表增长而下降。

检查查询谓词是否真的匹配索引

JSONB 慢查询中很常见的一类问题,是“表上明明有索引,执行计划却不用”。这时要对照索引定义和查询表达式逐字判断,而不是只比较字段名。

先检查现有索引:

sql
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'event_store';

如果索引是普通 GIN:

sql
CREATE INDEX idx_event_metadata_gin
ON event_store
USING gin (metadata);

它适合 JSONB 支持的包含、存在等操作,但下面这种标量提取条件通常不能直接把同一个 GIN 当作普通 B-tree 使用:

sql
SELECT id
FROM event_store
WHERE metadata->>'country' = 'US';

如果这类查询稳定、高频,并且 country 的选择性足够好,更直接的候选方案是表达式索引:

sql
CREATE INDEX idx_event_country
ON event_store ((metadata->>'country'));

然后必须用目标 SQL 再次验证:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM event_store
WHERE metadata->>'country' = 'US';

还要检查 GIN 的 operator class。jsonb_ops 支持范围较广,而 jsonb_path_ops 更偏向包含类查询,通常索引更紧凑,但不能简单替代所有 JSONB 操作符。决定是否调整 operator class 时,必须先列出真实查询集合,再判断它们使用的是 @>、?、JSONPath 还是标量表达式。

判断是不是选择性和统计信息出了问题

即使索引和谓词完全匹配,规划器也可能因为估算结果认为顺序扫描更便宜。此时关键证据是计划中的 rows 估算值与 actual rows 的差距。

如果计划估算只返回几百行,实际却返回几十万行,后续连接方式、扫描路径和内存使用都可能被错误估算带偏。对于频繁作为标量条件使用的 JSONB 路径,可以考虑把表达式变成规划器更容易统计的对象,而不是长期依赖对整个 JSONB 文档的粗粒度估算。

先确认统计信息是否近期更新:

sql
SELECT
    relname,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'event_store';

必要时在确认维护窗口和资源影响后执行:

sql
ANALYZE event_store;

然后重新获取计划并比较估算。如果 ANALYZE 后估算仍长期严重偏离,而且某个 JSONB 路径已经成为稳定、高频、强业务语义的查询字段,就要重新评估它是否仍应只存在于 JSONB 中。将热点字段提升为普通列,通常能获得更直接的统计信息、约束和 B-tree 索引能力。

不要通过临时关闭 enable_seqscan 来证明索引“应该被使用”。这种设置只能用于诊断候选计划,不是生产修复方案。真正需要比较的是索引路径与顺序扫描在真实数据分布下的总成本。

区分查询变慢和数据体量增长

一条 SQL 没有改动,并不代表它应该一直保持相同耗时。表从几十万行增长到几千万行、单行 JSONB 变大、匹配比例升高或索引扩大后,原来的访问路径可能自然失效。

可以先观察表和索引尺寸:

sql
SELECT
    pg_size_pretty(pg_table_size('event_store')) AS table_size,
    pg_size_pretty(pg_indexes_size('event_store')) AS indexes_size,
    pg_size_pretty(pg_total_relation_size('event_store')) AS total_size;

再查看具体索引大小:

sql
SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'event_store'
ORDER BY pg_relation_size(indexrelid) DESC;

如果查询计划与历史一致,但 Buffers、实际扫描行数和 relation size 同时持续增长,问题更可能是容量与数据分布变化,而不是某次发布突然导致索引失效。

还要特别关注低选择性条件。例如 metadata @> '{"enabled":true}' 如果最终命中表中绝大多数记录,那么 GIN 索引即使存在,也未必比顺序扫描更便宜。此时更有效的改造可能是增加更具选择性的关系型条件、创建合适的部分索引,或调整数据模型,而不是不断增加新的 JSONB 索引。

检查更新、死元组和 GIN 膨胀

JSONB 查询越来越慢有时并不是读取逻辑变化,而是写入模式长期改变了底层存储。PostgreSQL 的 UPDATE 会产生新的 MVCC 行版本;如果被索引的 JSONB 值发生变化,相关索引也需要维护新的条目。高频更新、大型 JSONB 文档和长期清理不及时叠加后,heap 与 GIN 索引都可能持续变大。

先查看表级死元组与 vacuum 状态:

sql
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'event_store';

如果 n_dead_tup 长期偏高,并伴随表尺寸持续增长,要继续检查是否存在长事务阻碍旧版本回收、autovacuum 是否跟不上写入速度,以及 JSONB 是否包含被频繁修改的热点字段。

对于 GIN,不应只因为索引很大就直接 REINDEX。先确认索引是否被目标查询实际使用、覆盖的 JSONB 文档是否过大、是否索引了大量业务从不检索的键和值,以及索引增长是否与写入量同步。重建只能回收部分历史空间或整理结构,不能修复“整个大型 payload 都被索引但查询只访问两个字段”这样的设计问题。

如果一个字段既稳定、高频过滤又经常参与排序、连接或约束,它往往更适合作为普通列。JSONB 应保留真正动态、稀疏、结构可能变化的属性,而不是成为所有字段的默认容器。

用证据闭环决定修复动作

完成排查后,应把问题归入可以验证的类别,而不是笼统记录为“JSONB 性能差”。如果操作符与索引不匹配,就改查询或设计匹配真实谓词的索引;如果索引已经命中但返回比例过高,就优化选择性和访问模式;如果估算严重偏差,就治理统计信息或结构化热点表达式;如果表、TOAST 或 GIN 持续膨胀,就处理写入模式、vacuum 和数据模型;如果只是数据规模增长,则重新建立当前容量下的性能基线。

每次调整后都应使用同一组真实参数重新执行基线计划:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM event_store
WHERE metadata @> '{"country":"US"}'::jsonb;

预期不是强制出现某一种扫描节点,而是看到总执行时间下降、读取和处理的无关数据减少、实际行数与估算更接近,并且写入成本、索引体积和维护压力仍在可接受范围内。

text
验证重点:
- Execution Time 是否稳定下降
- shared read 与总 buffer 访问是否减少
- Rows Removed by Filter 是否明显下降
- estimated rows 与 actual rows 是否更加接近
- 新索引是否被目标 SQL 使用
- 写入延迟与索引空间是否出现不可接受增长

JSONB 慢查询治理的核心不是追求“所有查询都走索引”,而是让真实谓词、数据分布、索引结构和存储成本重新匹配。只要始终从执行计划和容量证据出发,就能避免用一次性的索引重建掩盖长期的数据模型问题。

目录与阅读设置

记录一个想法

PostgreSQL JSONB 查询变慢怎么排查

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

登录哪都通