PostgreSQL中如何创建包含JSON字段值的多列索引

针对你需要同时匹配type（varchar类型）、slug（JSON类型）内en键值的查询场景，直接创建包含普通列+JSON值提取表达式的联合B树索引即可，这是性能最优的方案，B树索引对等值匹配、IN多值匹配的支持度最好。

索引创建语句 #

直接执行以下SQL创建联合索引：

CREATE INDEX idx_tags_type_slug_en ON tags USING btree (
  type,
  (slug ->> 'en')
);

关键说明 #

• 索引列顺序可根据字段区分度调整：如果type字段的不同值占比更高（过滤性更好）就保留type在前的顺序；如果slug->>'en'的过滤性更强，可以把JSON表达式列调整到第一列，两种顺序都能被你当前的查询命中，彻底替换掉现在的全表顺序扫描。
• 兼容性说明：PostgreSQL的B树索引原生支持将表达式（这里就是JSON字段取值的(slug ->> 'en')）作为索引列，和普通表字段组合不存在兼容问题，不需要额外安装插件。
• 效果验证：索引创建完成后重新执行EXPLAIN查看执行计划，会从原来的Seq Scan on tags（全表扫描）变为Index Scan或Bitmap Index Scan，查询成本会出现量级下降。
• 适用边界：这个索引只针对固定提取slug下en键的查询生效，如果你后续需要动态匹配slug下其他语言的键值，需要调整索引策略。

内容的提问来源于stack exchange，提问作者Connor Leech