PostgreSQL JSONB 查询优化：GIN 索引建了，为什么还是慢

我在排查一条事件日志查询时，遇到过一个挺典型的问题：表上已经给 JSONB 字段建了 GIN 索引，SQL 里也确实查的是 JSONB 内容，但执行计划还是没有按预期走，接口耗时稳定在 1s 以上。

这张表主要记录业务事件，像支付成功、优惠券领取、页面曝光、设备信息都会塞进 attrs 这个 JSONB 字段。日志表数据量大概 900 多万行，单个租户下最近 30 天有 60 多万行。

数据库环境是 PostgreSQL 15.6，表结构做过简化：

CREATE TABLE app_event_log (
    id           BIGSERIAL PRIMARY KEY,
    tenant_id    BIGINT      NOT NULL,
    user_id      BIGINT      NOT NULL,
    occurred_at  TIMESTAMPTZ NOT NULL,
    attrs        JSONB       NOT NULL
);

其中一条事件数据大概是这样：

{
  "event": "pay_success",
  "channel": "huawei",
  "device": {
    "os": "HarmonyOS",
    "model": "Pura X"
  },
  "amount": 168.90,
  "tags": ["new_user", "coupon"]
}

接口要查某个租户最近的支付成功事件，并限制设备系统：

SELECT
    id,
    user_id,
    occurred_at,
    attrs
FROM app_event_log
WHERE tenant_id = 42
  AND attrs ->> 'event' = 'pay_success'
  AND attrs #>> '{device,os}' = 'HarmonyOS'
  AND occurred_at >= '2026-01-01 00:00:00+08'
ORDER BY occurred_at DESC
LIMIT 50;

当时表上已经有两个索引：

CREATE INDEX idx_app_event_log_tenant_time
ON app_event_log (tenant_id, occurred_at DESC);

CREATE INDEX idx_app_event_log_attrs_gin
ON app_event_log USING gin (attrs);

看起来索引不少，实际执行计划并不理想。

慢在 JSONB 条件没有被索引接住

先看原始 SQL 的执行计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    user_id,
    occurred_at,
    attrs
FROM app_event_log
WHERE tenant_id = 42
  AND attrs ->> 'event' = 'pay_success'
  AND attrs #>> '{device,os}' = 'HarmonyOS'
  AND occurred_at >= '2026-01-01 00:00:00+08'
ORDER BY occurred_at DESC
LIMIT 50;

脱敏后的结果如下：

Limit  (cost=0.56..421.31 rows=50 width=386)
       (actual time=41.823..1286.472 rows=50 loops=1)
  Buffers: shared hit=47218 read=9134
  ->  Index Scan using idx_app_event_log_tenant_time on app_event_log
        (cost=0.56..389421.63 rows=46255 width=386)
        (actual time=41.821..1286.451 rows=50 loops=1)
        Index Cond: ((tenant_id = 42)
                 AND (occurred_at >= '2026-01-01 00:00:00+08'::timestamp with time zone))
        Filter: (((attrs ->> 'event'::text) = 'pay_success'::text)
             AND ((attrs #>> '{device,os}'::text[]) = 'HarmonyOS'::text))
        Rows Removed by Filter: 318642
        Buffers: shared hit=47218 read=9134
Planning Time: 0.481 ms
Execution Time: 1286.519 ms

这条计划走了 idx_app_event_log_tenant_time，没有走 idx_app_event_log_attrs_gin。

原因在 SQL 写法上。GIN 建在整个 attrs JSONB 字段上，比较擅长处理 JSONB 的包含、键存在这类查询，比如 @>、?。但这条 SQL 用的是：

attrs ->> 'event' = 'pay_success'
attrs #>> '{device,os}' = 'HarmonyOS'

->> 和 #>> 取出来的是文本。对于 PostgreSQL 来说，这已经变成了表达式上的文本比较，不会自动等价到整个 JSONB 字段的 GIN 索引。

所以执行过程变成了：先按租户和时间倒序扫描，再逐行取出 JSONB 字段里的内容做文本比较。LIMIT 50 没有帮太多，因为命中数据比较稀疏，数据库为了凑够 50 条，过滤掉了 31 万多行。

这也是 JSONB 慢查询里最容易误判的地方。表上有 GIN 索引，不代表所有 JSONB 写法都能用上它。

GIN 索引能救一部分，但救不了排序

第一步我先把查询改成 JSONB 包含条件：

SELECT
    id,
    user_id,
    occurred_at,
    attrs
FROM app_event_log
WHERE tenant_id = 42
  AND attrs @> '{
        "event": "pay_success",
        "device": {
          "os": "HarmonyOS"
        }
      }'::jsonb
  AND occurred_at >= '2026-01-01 00:00:00+08'
ORDER BY occurred_at DESC
LIMIT 50;

这个写法和 GIN 索引更匹配。重新看执行计划：

Limit  (cost=18652.41..18652.53 rows=50 width=386)
       (actual time=438.262..438.276 rows=50 loops=1)
  Buffers: shared hit=9824 read=1147
  ->  Sort  (cost=18652.41..18678.92 rows=10604 width=386)
            (actual time=438.260..438.267 rows=50 loops=1)
        Sort Key: occurred_at DESC
        Sort Method: top-N heapsort  Memory: 64kB
        Buffers: shared hit=9824 read=1147
        ->  Bitmap Heap Scan on app_event_log
              (cost=624.39..18300.15 rows=10604 width=386)
              (actual time=91.438..432.982 rows=6821 loops=1)
              Recheck Cond: (attrs @> '{"event": "pay_success", "device": {"os": "HarmonyOS"}}'::jsonb)
              Filter: ((tenant_id = 42)
                    AND (occurred_at >= '2026-01-01 00:00:00+08'::timestamp with time zone))
              Rows Removed by Filter: 3917
              Heap Blocks: exact=9328
              Buffers: shared hit=9824 read=1147
              ->  Bitmap Index Scan on idx_app_event_log_attrs_gin
                    (cost=0.00..621.74 rows=15834 width=0)
                    (actual time=88.913..88.914 rows=10738 loops=1)
                    Index Cond: (attrs @> '{"event": "pay_success", "device": {"os": "HarmonyOS"}}'::jsonb)
Planning Time: 0.526 ms
Execution Time: 438.341 ms

从 1.2s 降到 400ms 多，说明 GIN 索引确实起作用了。但这个计划还有两个问题。

第一，GIN 先找出满足 JSONB 条件的记录，再回表过滤 tenant_id 和 occurred_at。如果这个事件在其他租户里也很多，还是会多扫一批数据。

第二，结果要按 occurred_at DESC 排序。GIN 索引不能直接提供这个排序顺序，所以计划里出现了 Sort。

对临时分析、低频检索来说，这已经能接受。对接口列表来说，400ms 仍然偏高，而且数据继续增长后还会抖动。

如果主要查询都是 JSONB 包含，可以把原来的 GIN 索引换成 jsonb_path_ops：

CREATE INDEX CONCURRENTLY idx_app_event_log_attrs_gin_path
ON app_event_log USING gin (attrs jsonb_path_ops);

这个索引更偏向 @> 查询，通常比默认的 jsonb_ops 更小，包含查询也更快。代价是支持的操作类型更窄，例如 ?、?|、?& 这类键存在操作就不适合它。

我当时比较了一下两个索引的体积：

SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relid = 'public.app_event_log'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

结果大概是：

 index_name                         | index_size
------------------------------------+-----------
 idx_app_event_log_attrs_gin        | 8126 MB
 idx_app_event_log_attrs_gin_path   | 3428 MB

jsonb_path_ops 的体积小了不少。可这条接口最终没有只靠 GIN 解决，因为它是稳定的列表查询，需要同时满足租户、JSONB 条件和时间倒序。

高频条件改成表达式索引

这条接口每分钟都会被调用，查询字段也很固定：

tenant_id
attrs ->> 'event'
attrs #>> '{device,os}'
occurred_at DESC

这种场景更适合 B-tree 表达式索引。它不再把整个 JSONB 当成一个整体，而是把常用 JSONB 路径提取出来参与索引排序。

索引这样建：

CREATE INDEX CONCURRENTLY idx_app_event_log_pay_list
ON app_event_log (
    tenant_id,
    (attrs ->> 'event'),
    (attrs #>> '{device,os}'),
    occurred_at DESC,
    id DESC
);

SQL 也补上 id DESC，保证同一时间下排序稳定：

SELECT
    id,
    user_id,
    occurred_at,
    attrs
FROM app_event_log
WHERE tenant_id = 42
  AND attrs ->> 'event' = 'pay_success'
  AND attrs #>> '{device,os}' = 'HarmonyOS'
  AND occurred_at >= '2026-01-01 00:00:00+08'
ORDER BY occurred_at DESC, id DESC
LIMIT 50;

执行计划变成：

Limit  (cost=0.56..89.41 rows=50 width=386)
       (actual time=1.284..28.716 rows=50 loops=1)
  Buffers: shared hit=83 read=11
  ->  Index Scan using idx_app_event_log_pay_list on app_event_log
        (cost=0.56..21483.90 rows=12086 width=386)
        (actual time=1.282..28.699 rows=50 loops=1)
        Index Cond: ((tenant_id = 42)
                 AND ((attrs ->> 'event'::text) = 'pay_success'::text)
                 AND ((attrs #>> '{device,os}'::text[]) = 'HarmonyOS'::text)
                 AND (occurred_at >= '2026-01-01 00:00:00+08'::timestamp with time zone))
        Buffers: shared hit=83 read=11
Planning Time: 0.612 ms
Execution Time: 28.764 ms

这次没有 Bitmap Heap Scan，也没有额外 Sort。PostgreSQL 能直接沿着表达式索引找到目标租户、目标事件、目标系统下的数据，再按时间倒序读够 50 条。

优化前后对比：

写法	主要索引	耗时	问题
attrs ->> 'event' = ...	租户时间索引	1286ms	JSONB 条件逐行过滤
attrs @> ...	GIN 索引	438ms	仍然要回表过滤和排序
表达式索引	B-tree 表达式索引	28ms	更适合固定列表查询

这里的取舍比较清楚。GIN 更适合灵活 JSONB 检索，表达式索引更适合固定字段的高频查询。

字段类型和写法要对齐

JSONB 查询还有一个坑：类型不能凭感觉写。

比如金额字段在 JSON 里是数字：

{
  "amount": 168.90
}

下面这个包含条件查得到：

SELECT *
FROM app_event_log
WHERE attrs @> '{"amount": 168.90}'::jsonb;

这个查不到：

SELECT *
FROM app_event_log
WHERE attrs @> '{"amount": "168.90"}'::jsonb;

JSONB 里数字和字符串是两种类型。168.90 和 "168.90" 对 PostgreSQL 来说不是一个值。

如果用 ->> 取出来比较，拿到的是文本：

attrs ->> 'amount'

这时候直接做大小比较容易出错：

WHERE attrs ->> 'amount' > '100'

这是字符串比较，不是数值比较。'9' > '100' 在字符串规则下可能成立，结果会偏得很离谱。

要按数值查，至少要显式转换：

WHERE (attrs ->> 'amount')::numeric > 100

如果这个条件是高频查询，表达式索引也要跟 SQL 保持一致：

CREATE INDEX CONCURRENTLY idx_app_event_log_amount
ON app_event_log (((attrs ->> 'amount')::numeric));

不过这个索引有一个前提：所有相关记录里的 amount 都能转成 numeric。只要某条数据里出现了 "amount": "unknown"，建索引或查询时就可能报错。

更稳的做法是先约束写入格式，或者把金额从 JSONB 里拆出来。金额、状态、事件类型、业务时间这种字段，本来就经常参与筛选和排序，长期放在 JSONB 里会让查询和索引都变复杂。

函数写法也要和索引一致。

如果索引是：

CREATE INDEX CONCURRENTLY idx_app_event_log_channel
ON app_event_log ((attrs ->> 'channel'));

SQL 写成下面这样才能直接匹配：

WHERE attrs ->> 'channel' = 'huawei'

如果查询里写了 lower：

WHERE lower(attrs ->> 'channel') = 'huawei'

前面的索引就不匹配了。要么查询不要包函数，要么索引也按同样表达式建：

CREATE INDEX CONCURRENTLY idx_app_event_log_channel_lower
ON app_event_log (lower(attrs ->> 'channel'));

表达式索引的原则很简单：SQL 里怎么写，索引里就尽量怎么写。差一个函数、差一次类型转换，都可能让执行计划走到另一条路上。

什么时候把 JSONB 字段拆出来

JSONB 很方便，尤其适合日志、扩展属性、埋点参数。问题是它太方便了，很多字段一开始都被塞进去，后面查询频率上来后才发现索引不好做。

我现在会按访问频率把字段分成三类。

低频、临时、只用于排查的字段，可以继续留在 JSONB 里。比如设备型号、页面来源、调试标签。偶尔查一次，用 GIN 或直接扫一小段时间范围就行。

中频、多条件组合查询的字段，可以考虑 GIN。比如运营临时筛选某类事件，查询条件经常变化，字段组合不稳定。这个时候建很多表达式索引不划算，GIN 更灵活。

高频、稳定参与筛选或排序的字段，最好拆成普通列，或者至少用生成列承接。

比如事件名和设备系统长期都要查，可以改成：

ALTER TABLE app_event_log
ADD COLUMN event_name TEXT
GENERATED ALWAYS AS (attrs ->> 'event') STORED;

ALTER TABLE app_event_log
ADD COLUMN device_os TEXT
GENERATED ALWAYS AS (attrs #>> '{device,os}') STORED;

然后建普通组合索引：

CREATE INDEX CONCURRENTLY idx_app_event_log_event_list
ON app_event_log (
    tenant_id,
    event_name,
    device_os,
    occurred_at DESC,
    id DESC
);

查询也会更清楚：

SELECT
    id,
    user_id,
    occurred_at,
    attrs
FROM app_event_log
WHERE tenant_id = 42
  AND event_name = 'pay_success'
  AND device_os = 'HarmonyOS'
  AND occurred_at >= '2026-01-01 00:00:00+08'
ORDER BY occurred_at DESC, id DESC
LIMIT 50;

生成列的好处是业务写入仍然只写 attrs，查询侧可以像普通列一样使用。缺点也要提前考虑：大表新增 STORED 生成列会有计算和写入成本，线上变更要安排低峰期，或者通过新增普通列、分批回填、再切查询的方式慢慢迁移。

这次我没有马上改表结构，先用了表达式索引解决接口慢查询。后面事件日志字段稳定下来后，再把 event_name、channel、device_os 这几个字段拆出来。这样写入逻辑和查询逻辑都更容易维护。

总结

JSONB 查询慢，不一定是 JSONB 本身的问题。更常见的情况是 SQL 写法、索引类型和查询目标没有对齐。

attrs @> ... 这类包含查询更适合 GIN。字段组合灵活、临时分析多，可以优先考虑它。attrs ->> 'key' = ... 这种文本表达式比较，想要稳定走索引，就要建表达式索引。列表查询里还要把租户、筛选字段、排序字段放到同一个 B-tree 索引里，否则排序和回表过滤仍然会拖慢接口。

如果某个 JSONB 字段长期参与核心查询，就别一直把它当扩展属性看。该拆普通列就拆普通列，该用生成列就用生成列。JSONB 适合承载变化，不适合把所有高频查询字段都藏在里面。