为 JSONB 属性建立包含查询索引

概述

jsonb 列允许不同行携带不同属性，适合事件与配置类数据，但查询代价会在运行时显现：attributes ->> 'plan' = 'pro' 需要逐行提取并比较，普通索引帮不上忙。几百万行时就是一次全表扫描加逐文档解析，上线时还很快的接口会逐渐变成系统里最慢的一个。

步骤

1. 用 jsonb_path_ops 建立更小的 GIN 索引，它只索引路径哈希，适合包含查询：

CREATE INDEX events_attrs_gin
  ON events USING gin (attributes jsonb_path_ops);

2. 把多键过滤改写为包含运算：

SELECT id, occurred_at FROM events
WHERE attributes @> '{"plan": "pro", "region": "eu"}'::jsonb;

3. 若经常按单个键做等值过滤，改用 btree 表达式索引，体积更小且支持排序：

CREATE INDEX events_plan_idx ON events ((attributes ->> 'plan'));

4. 用 EXPLAIN (ANALYZE, BUFFERS) 确认出现 Bitmap Index Scan，并对比改造前后的 buffer 读取量。

验证

同一条件在改造前应为 Seq Scan，改造后应为 Bitmap Index Scan on events_attrs_gin 并带 Recheck Cond。把查询改成 ->> 形式后索引应不再被使用，这正好说明索引只覆盖包含语义。再观察写入延迟与索引体积，确认收益不是以写入性能为代价。

注意事项

• jsonb_path_ops 只支持 @>、?、?|、?&，其他运算符需要 jsonb_ops。
• GIN 不提供顺序，按属性排序仍需 btree 表达式索引。
• 数值比较（如分数大于 80）需要带正确类型转换的表达式索引或生成列。
• 大文档逐键索引会显著增加写入成本，只索引真正用于过滤的属性。
• GIN 的 pending list 在批量导入后可能很大，读取会暂时变慢，需要关注其大小并适时清理。