PostgreSQL中使用ANY运算符执行SQL查询时出现超时异常问题

场景说明 #

我有一张包含550万条记录的relations表，结构如下：

relations
------------
id
parent_id
name

编写了递归函数children_ids用于获取某个节点的所有子ID，该函数能正常运行：

CREATE OR REPLACE FUNCTION children_ids(id INTEGER)
RETURNS INT[]
LANGUAGE plpgsql
AS
$$
DECLARE
    ids INT[];
    r RECORD;
BEGIN
    FOR r IN WITH RECURSIVE t
    AS
    (
        SELECT * 
          FROM relations sa
         WHERE sa.id = id
         UNION ALL
        SELECT next.*
          FROM t prev
          JOIN relations next ON (next.parent_id = prev.id)
    )
    SELECT t.id FROM t
    LOOP
        ids := ids || r.id;
    END LOOP;

    RETURN ids;
END
$$;

需要查询books表，统计该函数返回的子ID对应的记录数，执行语句：

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(children_ids(20)); 

但该查询在生产环境超时失败，本地环境（克隆自生产，PostgreSQL版本一致）却能正常运行。

测试结果 #

SELECT children_ids(20);
-- 正常返回: {20}

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(children_ids(20)); 
-- 超时失败

SELECT COUNT(*) FROM books AS p WHERE p.id = ANY(children_ids(20)); 
-- 超时失败

SELECT * FROM books AS p WHERE p.id = ANY(children_ids(20));
-- 超时失败

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(ARRAY[20]);
-- 正常返回: 0

SELECT COUNT(p.*) FROM books AS p WHERE p.id IN (20);
-- 正常返回: 0

SELECT COUNT(*) FROM books AS p WHERE p.id IN (20);
-- 正常返回: 0

可能原因 #

• 函数被重复调用：PL/pgSQL函数默认是VOLATILE（不稳定），优化器可能认为函数返回值会随调用变化，导致对books表的每一行都执行一次children_ids(20)。哪怕单次调用很快，550万次重复调用也会直接超时，而本地环境数据量小或优化器计划不同未触发该问题。
• 统计信息不一致：虽然数据克隆，但生产环境的表统计信息可能未更新，优化器无法准确判断books.id的索引利用率、数据分布，从而选择全表扫描的低效计划，本地环境统计信息更新及时则能快速执行。
• 数组匹配逻辑差异：ANY(数组)的执行逻辑和IN(列表)不同，即使数组只有一个元素，优化器也可能未将其等价转换为p.id = 20的简单条件，而是采用更复杂的数组匹配逻辑，导致性能下降。

解决方案建议 #

1. 修改函数稳定性为STABLE
   函数输入相同则返回固定结果，标记为STABLE可避免优化器重复调用：

   CREATE OR REPLACE FUNCTION children_ids(id INTEGER)
   RETURNS INT[]
   LANGUAGE plpgsql
   STABLE
   AS
   $$
   DECLARE
       ids INT[];
       r RECORD;
   BEGIN
       FOR r IN WITH RECURSIVE t
       AS
       (
           SELECT * 
             FROM relations sa
            WHERE sa.id = id
            UNION ALL
           SELECT next.*
             FROM t prev
             JOIN relations next ON (next.parent_id = prev.id)
       )
       SELECT t.id FROM t
       LOOP
           ids := ids || r.id;
       END LOOP;
   
       RETURN ids;
   END
   $$;
   

2. 改用CTE直接关联查询
   跳过数组转换，直接用递归CTE关联books表，让优化器生成更优计划：

   SELECT COUNT(p.*)
   FROM books p
   JOIN (
       WITH RECURSIVE t AS (
           SELECT id FROM relations WHERE id = 20
           UNION ALL
           SELECT next.id FROM t prev JOIN relations next ON next.parent_id = prev.id
       )
       SELECT id FROM t
   ) AS child_ids ON p.id = child_ids.id;
   

3. 更新统计信息
   在生产环境执行以下语句，确保优化器有准确数据：

   ANALYZE books;
   ANALYZE relations;
   

4. 优化函数数组生成方式
   用array_agg替代循环拼接数组，提升函数本身效率：

   CREATE OR REPLACE FUNCTION children_ids(id INTEGER)
   RETURNS INT[]
   LANGUAGE plpgsql
   STABLE
   AS
   $$
   BEGIN
       RETURN (
           WITH RECURSIVE t AS (
               SELECT id FROM relations WHERE id = $1
               UNION ALL
               SELECT next.id FROM t prev JOIN relations next ON next.parent_id = prev.id
           )
           SELECT array_agg(id) FROM t
       );
   END
   $$;
   

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