SQL JSONB 的 @> 包含查询与 GIN 索引的加速效果实测

jsonb @> 查询慢的主因是未建匹配的gin索引或查询写法不匹配索引；必须显式创建jsonb_path_ops索引，且索引字段与查询路径严格一致，否则仍会全表扫描。

JSONB @> 查询为什么慢得离谱

没建对索引的 @> 查询，哪怕只查几千行，也可能比全表扫描还慢。postgresql 不会自动为 jsonB 字段建 GIN 索引，更不会猜你想按哪个路径查——它默认连字段最外层都懒得索引。

常见错误现象：EXPLAIN 显示 Seq Scan，哪怕 WHERE 里写了 data @> '{"status":"active"}'；或者加了索引但查询仍不走，因为索引定义和查询模式不匹配。

• GIN 索引必须显式创建，且要指定操作符类：using GIN (data jsonb_path_ops) 或 USING GIN (data jsonb_ops)
• jsonb_path_ops 更轻量、索引小、查询快，但只支持 @>、、<code>?、?|、?& 这几种操作；不支持 #> 路径提取或键存在性模糊匹配（如 data ? 'tags'）
• jsonb_ops 功能全，支持所有 JSONB 操作，但索引体积大、构建慢、查询略慢——别一上来就用它
• 如果查的是嵌套结构，比如 data #> '{user,profile}' @> '{"age": 30}'，GIN 索引对这种写法完全无效；必须把路径“扁平化”进索引，或改用表达式索引

GIN 索引怎么建才让 @> 真正生效

索引字段和查询条件必须“对得上”。不是建了 GIN 就万事大吉，PostgreSQL 对 JSONB 索引路径极其严格。

使用场景：高频查询固定结构的子对象，例如订单数据中查 metadata @> '{"source":"web"}'，或用户配置中查 settings @> '{"theme":"dark"}'。

• 最简有效建法：CREATE INDEX idx_orders_metadata_gin ON orders USING GIN (metadata jsonb_path_ops);
• 如果总查某个固定路径，比如 data -> 'flags' @> '{"verified": true}'，建表达式索引更高效：CREATE INDEX idx_data_flags_gin ON t USING GIN ((data -> 'flags') jsonb_path_ops);
• 别在 jsonb_ops 上建索引后，却用 data -> 'items' @> ... —— 路径提取操作会让索引失效；jsonb_path_ops 只认完整字段或 #>/#>> 提取后的值，但不认 -> 或 ->>
• 建完立刻 VACUUM ANALYZE table_name，否则统计信息不准，查询计划器可能继续选错执行路径

EXPLAIN 看不出走索引？先盯紧这几个点

EXPLAIN 显示没走 GIN，不一定是索引建错了，很可能是查询写法或数据分布触发了计划器的“理性放弃”。

常见错误现象：索引明明存在，EXPLAIN 却显示 Bitmap Heap Scan 后跟一堆 Recheck Cond，甚至退化成 Seq Scan；或者 Rows Removed by Index Recheck 高得反常。

• 检查是否用了非 sargable 写法：比如 (data @> '{"x":1}') = true，多一层括号+比较会让索引失效；直接写 data @> '{"x":1}'
• 确认数据选择性：如果 data @> '{"status":"active"}' 匹配 80% 的行，PG 很可能跳过索引——它算出来走索引再回表比直接扫还贵
• 留意隐式类型转换：data @> '{"count": "5"}'（字符串） vs data @> '{"count": 5}'（数字），JSONB 是强类型的，类型不对就无法命中索引
• 用 EXPLAIN (ANALYZE, BUFFERS)，重点看 Buffers shared hit 和 Index Cond 是否出现；没出现 Index Cond 基本等于没用上

嵌套数组匹配（如 tags @> [‘a’,’b’]）的坑

JSONB 数组的包含查询 @> 表面简单，实际极易踩空——尤其当字段是数组、而你想查“包含全部指定元素”时。

使用场景：标签系统查同时带 "backend" 和 "api" 的记录：tags @> '["backend","api"]'。

• 这个查询能走 jsonb_path_ops 索引，但前提是 tags 字段本身就是 JSONB 数组类型（["a","b"]），不是字符串 "["a","b"]" 或对象 {"list":["a","b"]}
• 如果数据存的是对象包装的数组，比如 {"tags": ["a","b"]}，那必须用表达式索引：CREATE INDEX idx_tags_arr ON t USING GIN ((data -> 'tags') jsonb_path_ops);，然后查 data -> 'tags' @> '["a","b"]'
• 注意 @> 是“超集”语义：查 ["a","b"] 会匹配 ["a","b","c"]，但不会匹配 ["a"]；如果想查“至少含其一”，得用 ? 或 ?|，它们也走 GIN，但索引定义一样
• 数组元素顺序无关，但大小写和空格敏感：'["A"]' ≠ '["a"]'，'["x "]' ≠ '["x"]'

真正卡住人的往往不是语法，而是索引策略和查询写法之间那几毫米的错位——少一个 jsonb_path_ops，多一层 ->，换一种数组存法，效果就天差地别。实测时别只信 EXPLAIN 的第一行，往下翻三行看 Index Cond 和 Rows Removed 才算数。