PostgreSQL JSONB数组索引优化：关联请求与选择表查询

问题根源 #

你原来的查询用jsonb_to_recordset展开selection.settings数组，这种方式会触发全表扫描——数据库需要先把所有selection记录的数组都拆出来，再做关联过滤，完全没法用到已创建的ix_selection_stage_setting GIN索引（该索引仅支持针对JSONB值的路径匹配操作）。

修改后的查询语句 #

直接利用JSONB的@>包含操作符，让数据库可以通过GIN索引快速定位符合条件的selection记录：

SELECT r.id, r.selection_id, r.current_stage_id
FROM request r
JOIN selection s 
  ON r.selection_id = s.id
WHERE s.settings @> jsonb_build_array(jsonb_build_object('id', r.current_stage_id, 'stage_type', 1));

索引生效原理 #

• jsonb_build_array(jsonb_build_object('id', r.current_stage_id, 'stage_type', 1))会动态构造出和settings数组元素结构一致的JSONB数组（比如当current_stage_id=1时，构造出[{"id":1,"stage_type":1}]）
• @>操作符用于判断s.settings是否包含这个构造出来的数组元素，而你创建的gin (settings jsonb_path_ops)索引正是为@>这类包含操作优化的，数据库会直接走索引筛选符合条件的selection记录，避免全表扫描。

额外优化建议 #

为了进一步提升关联效率，可以给request表添加联合索引：

CREATE INDEX ix_request_selection_current ON request (selection_id, current_stage_id);

这个索引能让数据库快速定位到和筛选出的selection记录匹配的request数据，减少关联阶段的开销。

验证索引是否生效 #

执行以下命令查看执行计划，确认是否出现Index Scan using ix_selection_stage_setting on selection：

EXPLAIN ANALYZE
SELECT r.id, r.selection_id, r.current_stage_id
FROM request r
JOIN selection s 
  ON r.selection_id = s.id
WHERE s.settings @> jsonb_build_array(jsonb_build_object('id', r.current_stage_id, 'stage_type', 1));

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