PostgreSQL函数内查询比单独执行慢数十倍问题求助

我有一条PostgreSQL查询语句，从总记录数约1600万的表中查询最多1万行数据，单独执行仅需约3秒。但将完全相同的查询放入函数后，执行速度大幅变慢，耗时约1分20秒。

环境概述 #

（实际环境更复杂，以下为简化版）
使用PostgreSQL存储数值网格的单元格和节点数据，一个网格包含多个单元格，每个单元格由一个或多个节点组成，节点本质是空间中的点。涉及三张表：GridCells、GridNodes和GridCellNodeLinks，表结构如下：

CREATE TABLE public."GridNodes" (
    "id" serial NOT NULL,
    PRIMARY KEY ("id")
);
CREATE TABLE public."GridCells" (
    "id" serial NOT NULL,
    "grid" integer NOT NULL,
    PRIMARY KEY ("id")
);
CREATE TABLE public."GridCellNodeLinks" (
    "gridCellId" int NOT NULL,
    "gridNodeId" int NOT NULL,
    FOREIGN KEY ("gridCellId") REFERENCES public."GridCells"("id"),
    FOREIGN KEY ("gridNodeId") REFERENCES public."GridNodes"("id")
);

关联表查询性能异常 #

需要分页查询指定网格下的所有节点，每页1万条，为提升性能，使用WHERE id >= firstIdOnPage替代OFFSET pageNumber * 10000。

针对包含1,038,240个节点的网格1，查询语句如下：

SELECT
    N."id"
FROM public."GridCells" G
LEFT JOIN public."GridCellNodeLinks" L
    ON G."id" = L."gridCellId"
LEFT JOIN public."GridNodes" N
    ON N."id" = L."gridNodeId"
WHERE "grid" = 1
and N."id" >= 1030001
ORDER BY "id" ASC
LIMIT 10000;

该语句单独执行仅需数秒，但放入函数后，调用耗时几乎是单独执行的30倍。仅当查询到的节点数小于LIMIT时才会出现此问题：若使用更小的起始ID（如N."id" >= 1020001）或缩小LIMIT值（如LIMIT 8240），函数仍能在数秒内完成，且该现象仅在函数中出现，单独查询无此问题。

函数代码如下（实际场景会传入参数，此处为硬编码简化）：

CREATE OR REPLACE FUNCTION GetNodes()
RETURNS TABLE(
    "id" INT
)
AS
    $BODY$
    BEGIN
        RETURN QUERY 
            SELECT
                N."id"
            FROM public."GridCells" G
            LEFT JOIN public."GridCellNodeLinks" L
                ON G."id" = L."gridCellId"
            LEFT JOIN public."GridNodes" N
                ON N."id" = L."gridNodeId"
            WHERE "grid" = 1
            and N."id" >= 1030001
            ORDER BY "id" ASC
            LIMIT 10000;
    END;
    $BODY$
LANGUAGE plpgsql
PARALLEL SAFE;

已尝试的方案 #

• 查询仅执行索引扫描，本应无性能问题；对函数执行EXPLAIN ANALYZE仅得到无参考价值的“function scan”，因无超级权限无法使用auto_explain。
• 优化查询语句（如使用子查询）可将单独执行时间缩短至500ms，但对函数执行速度无影响，此优化不在本次问题范围内。
• 添加PARALLEL SAFE无任何效果。
• 改为language sql也无效果。

核心原因：执行计划生成差异 #

单独执行SQL时，PostgreSQL能根据具体常量值（如N."id" >= 1030001、LIMIT 10000）精准估算返回行数，选择最优执行计划——比如先过滤GridNodes中符合条件的记录，再关联其他表，快速找到8240条目标节点后停止扫描。

但在函数中，PostgreSQL的执行计划生成逻辑存在差异：

1. plpgsql函数预编译计划：默认会在函数编译阶段生成通用执行计划，无法感知硬编码或传入参数的实际数据分布。当计划假设N."id" >= ?会返回大量数据时，会选择先扫描GridCells中grid=1的所有单元格，再关联所有对应GridCellNodeLinks，最后逐一匹配GridNodes，直到凑够10000条或遍历完所有数据。这种计划在实际只需8240条时，会做大量无用关联，导致耗时剧增。
2. SQL函数的统计信息偏差：即使改用language sql，若没有明确指定稳定性修饰符，或PostgreSQL统计信息未覆盖id接近最大值的边界场景，也可能生成低效计划。

针对性解决方法 #

1. 使用动态SQL强制生成最优计划 #

在plpgsql函数中用EXECUTE执行查询，让PostgreSQL每次调用时根据实际参数生成适配的执行计划：

CREATE OR REPLACE FUNCTION GetNodes()
RETURNS TABLE("id" INT)
AS $BODY$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT N."id"
         FROM public."GridCells" G
         LEFT JOIN public."GridCellNodeLinks" L ON G."id" = L."gridCellId"
         LEFT JOIN public."GridNodes" N ON N."id" = L."gridNodeId"
         WHERE "grid" = 1
           AND N."id" >= %L
         ORDER BY "id" ASC
         LIMIT 10000',
        1030001
    );
END;
$BODY$ LANGUAGE plpgsql PARALLEL SAFE;

若需传入参数，用USING子句避免SQL注入：

CREATE OR REPLACE FUNCTION GetNodes(p_grid INT, p_min_id INT, p_limit INT)
RETURNS TABLE("id" INT)
AS $BODY$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT N."id"
         FROM public."GridCells" G
         LEFT JOIN public."GridCellNodeLinks" L ON G."id" = L."gridCellId"
         LEFT JOIN public."GridNodes" N ON N."id" = L."gridNodeId"
         WHERE "grid" = $1
           AND N."id" >= $2
         ORDER BY "id" ASC
         LIMIT $3'
    ) USING p_grid, p_min_id, p_limit;
END;
$BODY$ LANGUAGE plpgsql PARALLEL SAFE;

2. 优化SQL函数的执行计划稳定性 #

若坚持用language sql，添加STABLE修饰符，并更新表统计信息：

CREATE OR REPLACE FUNCTION GetNodes()
RETURNS TABLE("id" INT)
AS $BODY$
    SELECT N."id"
    FROM public."GridCells" G
    LEFT JOIN public."GridCellNodeLinks" L ON G."id" = L."gridCellId"
    LEFT JOIN public."GridNodes" N ON N."id" = L."gridNodeId"
    WHERE "grid" = 1
      AND N."id" >= 1030001
    ORDER BY "id" ASC
    LIMIT 10000;
$BODY$ LANGUAGE sql STABLE PARALLEL SAFE;

更新统计信息：

ANALYZE public."GridNodes";
ANALYZE public."GridCellNodeLinks";

3. 调整查询逻辑减少关联开销 #

直接以GridNodes为查询起点，通过EXISTS验证节点归属，能利用主键索引快速过滤：

SELECT N."id"
FROM public."GridNodes" N
WHERE N."id" >= 1030001
  AND EXISTS (
      SELECT 1
      FROM public."GridCellNodeLinks" L
      JOIN public."GridCells" G ON G."id" = L."gridCellId"
      WHERE L."gridNodeId" = N."id" AND G."grid" = 1
  )
ORDER BY N."id" ASC
LIMIT 10000;

建议给GridCellNodeLinks创建联合索引(gridNodeId, gridCellId)，进一步提升关联验证的速度。

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