PostgreSQL多JSONB列overlaps运算符查询优化方案

首选方案：使用JSONB原生重叠操作符（性能最优，支持索引） #

你不需要通过JSONB_ARRAY_ELEMENTS_TEXT拆分数组做逐行判断，PostgreSQL 原生提供的?|操作符专门用于判断JSONB字符串数组是否与传入的文本数组存在重叠（即包含至少一个相同元素），空数组场景下会自动返回false，不会出现行被意外过滤的问题。
查询可直接改写为：

SELECT *
FROM your_table t
WHERE t.data1 ?| ARRAY['some string','some other string']
   OR t.data2 ?| ARRAY['random string', 'another random string'];

性能优化：为两个JSONB列创建GIN索引后，该查询可以直接走索引检索，相比原来的EXISTS子查询写法性能可以提升1~2个数量级，建索引语句如下：

CREATE INDEX idx_table_data1 ON your_table USING GIN (data1);
CREATE INDEX idx_table_data2 ON your_table USING GIN (data2);

原写法性能差的核心原因是逐行拆分数组生成临时结果集做判断，属于表级扫描运算，无法利用索引加速，数据量越大性能损耗越明显。

备选方案：需要提取匹配元素时的写法 #

如果业务场景需要同时获取命中的具体数组元素值，不要使用隐式交叉连接（逗号分隔的表连接默认是INNER JOIN，空数组拆分后无记录时会直接过滤主表行），也不需要尝试不支持的LATERAL全外连接，使用两个LEFT JOIN LATERAL分别拆分数组即可，空数组场景下拆分结果为NULL，不会丢弃主表行：

SELECT DISTINCT t.*
FROM your_table t
LEFT JOIN LATERAL JSONB_ARRAY_ELEMENTS_TEXT(t.data1) d1(val) ON TRUE
LEFT JOIN LATERAL JSONB_ARRAY_ELEMENTS_TEXT(t.data2) d2(val) ON TRUE
WHERE d1.val IN ('some string','some other string')
   OR d2.val IN ('random string', 'another random string');

注意：该写法性能弱于原生?|操作符方案，仅在需要提取匹配到的具体元素值时使用。不要同时交叉连接两个拆分数组的结果，否则会产生两数组长度乘积的冗余行，带来不必要的性能损耗。

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