在PostgreSQL函数中高效将数组传入多列WHERE IN的方法

问题场景 #

现有PostgreSQL表定义及数据：

create table test
(
  id varchar(20),
  text varchar(20)
);
    
insert into test values(1, 'a')
insert into test values(2, 'b')
insert into test values(3, 'c')

原本的多列匹配查询写法：

SELECT * FROM test 
WHERE (id, text) = ANY (VALUES('1', 'a')
                            , ('2', 'b')
                            , ('3', 'c'));

现有一个接收两个数组参数的函数，需要用输入的p_ids和p_texts高效替换上述查询中的ANY (VALUES(...))部分，要求从性能角度做最优实现。初始函数定义如下：

create or replace function test_function
(
    p_ids     in varchar[],
    p_texts   in varchar[]
)
returns table (p_id   varchar,
               p_text varchar) as $$
begin
    return query 
    SELECT id, text 
    FROM test 
    WHERE (id, text) = ANY (VALUES('1', 'a')
                                , ('2', 'b')
                                , ('3', 'c'));
end;
$$ LANGUAGE plpgsql;

性能最优的实现方案 #

方法1：用unnest关联数组生成匹配行（推荐） #

通过unnest同时展开两个数组，确保对应位置的元素配对生成临时匹配行集，再用JOIN关联原表。这种方式能让PostgreSQL更好地利用复合索引，避免隐式转换，是性能最优的写法。

修改后的函数：

create or replace function test_function
(
    p_ids     in varchar[],
    p_texts   in varchar[]
)
returns table (p_id   varchar,
               p_text varchar) as $$
begin
    -- 先校验数组长度一致，避免数据不匹配
    IF array_length(p_ids, 1) != array_length(p_texts, 1) THEN
        RAISE EXCEPTION '输入的两个数组长度必须一致';
    END IF;

    return query 
    SELECT t.id, t.text 
    FROM test t
    JOIN unnest(p_ids, p_texts) AS matches(m_id, m_text)
      ON t.id = m_id AND t.text = m_text;
end;
$$ LANGUAGE plpgsql;

性能优势：

• unnest(p_ids, p_texts)会按位置配对数组元素，生成结构化临时关系，优化器能直接选择高效执行计划（如嵌套循环、哈希连接）。
• 如果test表存在(id, text)复合索引，查询会直接走索引扫描，效率大幅提升。
• 无动态SQL的解析开销，也避免了SQL注入风险。

方法2：构造行数组直接匹配 #

这种写法更简洁，但性能略逊于JOIN方式，因为行数组构造会引入额外类型转换开销，适合数组元素较少的场景。

示例：

create or replace function test_function
(
    p_ids     in varchar[],
    p_texts   in varchar[]
)
returns table (p_id   varchar,
               p_text varchar) as $$
begin
    IF array_length(p_ids, 1) != array_length(p_texts, 1) THEN
        RAISE EXCEPTION '输入的两个数组长度必须一致';
    END IF;

    return query 
    SELECT id, text 
    FROM test 
    WHERE (id, text) = ANY (
        array(SELECT row(p_ids[i], p_texts[i]) FROM generate_subscripts(p_ids, 1) AS i)
    );
end;
$$ LANGUAGE plpgsql;

额外性能优化建议 #

1. 给test表创建(id, text)复合索引，最大化查询效率：

CREATE INDEX idx_test_id_text ON test(id, text);

2. 确保函数参数类型与表字段类型完全一致（此处均为varchar，已匹配），避免隐式类型转换损耗性能。
3. 若数组元素量极大，可保持现有写法，PostgreSQL对unnest的批量处理效率已足够优秀，无需额外拆分。

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