时间戳

首页 > 解决方案 > 函数调用另一个函数时的Postgers问题

postgresql - 函数调用另一个函数时的Postgers问题

问题描述


我正在使用 postgres 并且我有 plpgsql 功能。在这个函数中，我正在调用我编写的另一个 plpgsql 函数。
但是当我执行该函数时，它需要很长时间（大约 30 秒）。
当我尝试将外部函数内容插入第一个函数时，它得到了改进性能显着降低到 1 秒或更低。
有谁知道为什么会这样？
在这种情况下，问题解决了，但我有一个与外部函数类似的情况，我无法将其内容插入第一个函数，因为这是一个更长的函数。

代码示例：
耗时 30 秒的实现：
CREATE OR REPLACE FUNCTION public.f1(arr integer[])
    RETURNS bytea
    LANGUAGE 'plpgsql'
    
AS $BODY$
declare
res bytea;
Begin
WITH mvtgeom AS (
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), table2.geom) AS geom, public.f2(t.id ,arr) AS func_res
FROM table1 t, table2
 WHERE ST_Intersects(t.geom, ST_Transform(table2.geom, 4326))
)
    
    SELECT ST_AsMVT(mvtgeom, 'public. f1')
    FROM mvtgeom
    INTO res
    RETURN res;
end;
$BODY$;



CREATE OR REPLACE FUNCTION public.f2(idd integer ,arr integer[])
RETURNS integer
LANGUAGE 'plpgsql'
AS $BODY$
declare
result integer;

BEGIN
    IF ARRAY[idd] <@ arr THEN
        result :=0;
    ELSE
        SELECT col1 FRIM table1 t WHERE t.id = idd INTO result;
    END IF;

    RETURN result;
END;
$BODY$;

更快的实施：
CREATE OR REPLACE FUNCTION public.f1(arr integer[])
    RETURNS bytea
    LANGUAGE 'plpgsql'
    
AS $BODY$
declare
res bytea;
Begin
WITH mvtgeom AS (
      SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), table2.geom) AS geom, CASE WHEN ARRAY[t.id] <@ arr THEN 0 ELSE t.col1 END AS func_res
FROM table1 t, table2
 WHERE ST_Intersects(t.geom, ST_Transform(table2.geom, 4326))
)
    
    SELECT ST_AsMVT(mvtgeom, 'public. f1')
    FROM mvtgeom
    INTO res
    RETURN res;
end;
$BODY$;

标签： postgresqlfunctionquery-optimizationpostgisplpgsql

解决方案

将简单的 SQL 查询包装到函数中通常不是一个好主意。函数是用于优化的黑盒，当您在某些复杂查询中使用函数内部 SQL 时，优化器会优化 2 个独立查询而不是一个。

您可以使用 SQL 语言创建函数。这些函数（在某些情况下）是内联的，然后优化器可以对一个查询进行优化。

如果您需要用 PL/pgSQL 语言编写函数，那么最好使用函数的正确标志。当结果函数依赖于参数时，函数应该是immutable，当函数只从数据库中读取时，函数应该是stable。在其他情况下，该功能应标记为volatile。这是默认设置。你的功能f2应该是stable：

CREATE OR REPLACE FUNCTION public.f2(idd integer ,arr integer[])
RETURNS integer
LANGUAGE 'plpgsql'
AS $BODY$
...
$BODY$ STABLE;

推荐阅读