Postgres中数组与表列求交集及匹配度统计的方案咨询

问题根因说明 #

• ERROR: operator does not exist: bigint[] & bigint[]报错原因：原生PostgreSQL未提供数组交集的&运算符，该运算符是intarray扩展的专属能力，且仅支持integer[]类型，不兼容bigint[]。
• 全量聚合参考列为单个数组比对效率极低的原因：每次数组比对都需要遍历17万元素的超大数组，数千万行遍历的时间复杂度为O(千万 * 17万)，必然耗时数小时。

最优实现方案（无需聚合参考列为大数组） #

核心思路：利用参考表的索引做单值匹配，仅对大表每行的短数组（1-10个元素）做展开统计，时间复杂度可降低到O(千万 * 10 * log(17万))，性能提升几个数量级。

步骤1：给参考临时表加唯一索引 #

首先给17万行的参考临时表的匹配字段加唯一索引，加速单值查找：

CREATE UNIQUE INDEX idx_temp_reference_col ON temp_reference_table(reference_column);

步骤2：逐行统计数组匹配情况 #

通过unnest将大表每行的bigint_array拆分为单个元素，左关联参考表统计匹配数量，再按规则分类：

SELECT 
    t.id,
    t.bigint_array,
    -- 按匹配数量分类
    CASE
        WHEN count(r.reference_column) = cardinality(t.bigint_array) THEN '全匹配'
        WHEN count(r.reference_column) > 1 THEN '多个匹配项'
        WHEN count(r.reference_column) = 1 THEN '仅1个匹配项'
        ELSE '无匹配'
    END AS match_type
FROM big_table t
-- 展开数组元素
LEFT JOIN LATERAL unnest(t.bigint_array) arr_elem(elem) ON true
-- 关联参考表走索引匹配
LEFT JOIN temp_reference_table r ON r.reference_column = arr_elem.elem
GROUP BY t.id, t.bigint_array;

可选优化（进一步提速） #

如果大表中存在大量完全无匹配的行，可以提前过滤减少处理量：

1. 先将参考表聚合为单个数组存入变量：

SELECT array_agg(reference_column) INTO ref_arr FROM temp_reference_table;

2. 给大表的bigint_array字段加GIN索引：

CREATE INDEX idx_bigtable_arr ON big_table USING GIN (bigint_array);

3. 在查询中新增过滤条件，提前过滤完全无匹配的行：

WHERE t.bigint_array && ref_arr

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