PostgreSQL中带OR的jsonB GIN索引查询未命中问题求助

针对你遇到的情况，以下几种方法可以让带OR的查询命中GIN索引：

方法一：用UNION ALL替代OR #

PostgreSQL对UNION ALL的查询规划会分别处理每个子查询，各自命中索引后合并结果，非常适合你的场景（尤其是加了LIMIT 1的情况）。改写后的查询如下：

SELECT id FROM activity WHERE sources @> '{"sfEventId":"exampleSfEventId"}'
UNION ALL
SELECT id FROM activity WHERE sources @> '{"eventId":"exampleEventId"}'
LIMIT 1;

如果担心出现重复id（同一行满足两个条件），可以把UNION ALL换成UNION，但UNION会额外做去重操作，性能略低，而你的查询加了LIMIT 1，其实UNION ALL足够高效。

方法二：更新表统计信息 #

如果PostgreSQL的统计信息过时，优化器可能错误判断索引扫描的成本，从而选择全表扫描。执行以下命令更新统计信息：

ANALYZE activity;

更新后重新执行查询，优化器会基于最新的统计数据重新评估执行计划，大概率会选择索引扫描。

方法三：临时强制使用索引（应急方案） #

如果以上方法都无效，可以临时关闭顺序扫描，强制优化器使用索引：

-- 临时关闭顺序扫描
SET enable_seqscan = off;
-- 执行查询
SELECT id FROM activity WHERE ((sources @> '{"sfEventId":"exampleSfEventId"}') OR (sources @> '{"eventId":"exampleEventId"}')) LIMIT 1;
-- 恢复顺序扫描设置
SET enable_seqscan = on;

也可以使用查询提示（PostgreSQL 12及以上版本支持），仅对当前查询生效：

SELECT id FROM activity 
/*+ IndexScan(activity activitysources) */
WHERE ((sources @> '{"sfEventId":"exampleSfEventId"}') OR (sources @> '{"eventId":"exampleEventId"}')) 
LIMIT 1;

注意：强制索引是应急手段，优先让优化器自主选择执行计划，只有在统计信息正常但优化器仍判断错误时再使用。

补充说明 #

为什么单独条件能命中索引，OR时不行？这是因为PostgreSQL优化器会评估执行成本：当两个OR条件匹配的行数较多时，优化器可能认为合并两个索引扫描的结果成本高于全表扫描；或者统计信息过时，导致成本计算偏差。用UNION ALL拆分查询可以绕开这个问题，让每个子查询单独使用索引。

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