PostgreSQL GIN / GiST 索引在 JSONB 上的选择与性能

gin索引是jsONB查询的默认首选方案，专为存在性及路径查询优化；GiST在jsonB上几乎无实用场景，仅有限支持子集判断且功能残缺。

GIN 索引是 JSONB 查询的默认且首选方案

对 jsonb 字段做存在性（@>、?、?|、?&）或键路径查询（#>、#>> 配合 jsonb_path_exists 等），GIN 是唯一被原生深度支持的索引类型。postgresql 的 jsonb_ops 默认操作符族就是为 GIN 优化的，它把 JSONB 值展开为键名、键值、路径三元组并建立倒排映射。

常见错误是误以为 GiST 可以替代 GIN 处理标准 JSONB 查询——实际上 GiST 不支持 ? 或 @> 这类操作符的索引加速，强行创建会报错：operator is not supported for indexing。

• 用 CREATE INDEX idx ON tbl using GIN (data) 覆盖整个 JSONB 字段，适合任意路径查询
• 若只查固定路径（如 data->'user'->>'id'），优先建表达式索引：CREATE INDEX idx_user_id ON tbl USING GIN ((data->'user'->>'id'))，更省空间且更快
• 避免对高频更新的 JSONB 字段建全字段 GIN 索引——每次更新需重建大量倒排项，写放大明显

GiST 在 JSONB 上几乎无实用场景

GiST 索引在 JSONB 上仅支持极有限的语义：只有 （是否为子集）和 @>（是否包含）两个操作符能走索引，且仅当使用 jsonb_path_ops 操作符族时才生效。而 jsonb_path_ops 会丢弃键名信息，只保留键值与结构层级，导致无法按 key 查询（例如 data ? 'status' 无法命中）。

实际中几乎没人用它，除非你有特殊需求：比如只存扁平化事件日志，且只做「整个文档是否属于某模板集合」这类子集判断，同时能接受无法按字段名检索。

• jsonb_path_ops 索引体积比 jsonb_ops 小约 1/3，但功能残缺，别为了省空间牺牲查询能力
• CREATE INDEX ... USING GIST 在 JSONB 列上不报错，但后续查询大概率走不了索引——检查 EXPLaiN 输出里有没有 Index Scan using ...，而不是只看有没有 Index 字样

GIN 索引性能关键：选对操作符族和表达式粒度

GIN 索引性能差异主要来自两个选择：是否用 jsonb_path_ops，以及索引建在整列还是具体路径表达式上。前者影响能支持哪些查询，后者决定索引大小和命中精度。

典型陷阱是建了全字段 GIN (data) 却频繁执行 WHERE data->>'name' = 'Alice'——这会触发全索引扫描（因为 GIN 不直接支持字符串等值比较），远不如单独建 GIN ((data->>'name')) 高效。

• data->>'name' 返回 text，可走 B-tree；若坚持用 GIN，必须配合 text_pattern_ops 或 pg_trgm 扩展做模糊匹配
• 嵌套深的路径（如 data#>>'{meta, tags, 0}'）建议提取为生成列再建索引，避免每次查询都解析路径
• GIN 索引的 VACUUM 开销比 B-tree 高，高并发写入表需调大 maintenance_work_mem，否则索引清理可能拖慢 autovacuum

JSONB 索引不是万能解药：先确认查询模式再建

很多性能问题根本不在索引类型，而在查询写法本身。例如用 jsonb_array_elements() 展开数组后 JOIN，或在 WHERE 里写 data @> '{"tags": ["vip"]}'::jsonb 却没建对应 GIN 索引——后者连索引都用不上。

真正卡顿往往发生在：查询条件含函数调用（如 lower(data->>'name')）、跨多层嵌套做 OR 组合、或用 jsonb_path_query 做复杂路径遍历。这些场景索引基本无效，得靠重构数据模型（如拆出关系表）或预计算字段。

• 用 EXPLAIN (ANALYZE, BUFFERS) 看实际是否用了索引，注意 Rows Removed by Index Recheck 过高说明索引选择率差，可能需要更精确的表达式索引
• GIN 索引对 jsonb_typeof()、jsonb_length() 这类标量函数完全无加速能力，别浪费空间去建