PostgreSQL 16存储过程嵌套调用性能优化咨询

在PostgreSQL 16中，嵌套存储过程的调用开销成为性能瓶颈：空逻辑的三层嵌套存储过程循环调用50万次耗时约1.787秒；实际业务中多计算类存储过程嵌套时，整体执行效率远低于Oracle PL/SQL（Oracle需4分钟，PostgreSQL需1小时）。改用函数后性能有一定提升，但未达预期，以下是针对PL/pgSQL引擎的优化方案：

测试代码与执行结果 #

测试代码 #

CREATE OR REPLACE PROCEDURE perf_test_3() AS
$body$
DECLARE
begin
end;
$body$ LANGUAGE PLPGSQL;


CREATE OR REPLACE PROCEDURE perf_test_2() AS
$body$
DECLARE
begin
  CALL perf_test_3();
end;
$body$ LANGUAGE PLPGSQL;


CREATE OR REPLACE PROCEDURE perf_test_1() AS
$body$
DECLARE
begin
  CALL perf_test_2();
end;
$body$ LANGUAGE PLPGSQL;

do $$ 
DECLARE 
    v_ii int;
    v_start            timestamp(3);
    v_end              timestamp(3);
BEGIN
    v_start := clock_timestamp();
    for v_ii in 1..500000 LOOP
        call perf_test_1();
    END LOOP;
    v_end := clock_timestamp();
    RAISE NOTICE 'start time %', v_start;
    RAISE NOTICE 'end   time %', v_end;
    RAISE NOTICE 'run   time %', v_end-v_start;
END; 
$$;

执行结果 #

NOTICE:  start time 2024-02-20 16:37:15.76
NOTICE:  end   time 2024-02-20 16:37:17.547
NOTICE:  run   time 00:00:01.787

优化方案 #

• 扁平化嵌套逻辑，减少调用次数
  PL/pgSQL的过程/函数调用存在上下文切换、栈帧创建的固定开销，多层嵌套会持续放大该开销。如果业务逻辑允许，直接将多层调用的逻辑合并到同一个过程/函数中，彻底消除嵌套调用的额外消耗。比如将perf_test_1、perf_test_2、perf_test_3的逻辑合并，避免三层调用的叠加开销。

• 优先使用SQL函数替代PL/pgSQL函数（逻辑允许时）
  SQL函数基于纯SQL实现，无需经过PL/pgSQL解释器的初始化和执行环节，调用开销远低于PL/pgSQL函数。如果你的计算逻辑可以通过纯SQL表达（无需复杂分支、循环控制），建议改用SQL函数。示例：

  CREATE OR REPLACE FUNCTION perf_test_3() RETURNS void AS
  $body$
  SELECT;
  $body$ LANGUAGE SQL;
  

• 启用PL/pgSQL编译优化
  PostgreSQL 11+支持将PL/pgSQL函数编译为字节码，减少解释执行的开销。可以通过以下配置或设置优化：

  • 确保plpgsql.compile = on（默认开启，可通过SHOW plpgsql.compile;确认）
  • 关闭断言检查：SET plpgsql.check_asserts = off;（如果业务不需要断言验证）
  • 正确标记函数稳定性：创建函数时使用VOLATILE/STABLE/IMMUTABLE，帮助优化器生成更高效的执行计划

• 用批量处理替代循环调用
  针对大量数据处理场景，尽可能利用PostgreSQL的集合运算能力，用批量SQL操作替代逐行循环调用过程/函数。比如将50万次的单条调用改为一次性处理整个数据集，从根源减少调用次数，避免重复的调用开销。

• 核心逻辑改用C语言自定义函数（极端场景）
  如果上述方案仍无法满足性能要求，对于核心计算逻辑，可以编写C语言自定义函数并编译为PostgreSQL扩展模块。C函数的调用开销和执行效率接近原生代码，远优于PL/pgSQL，但需要具备C语言开发能力，同时要考虑扩展的长期维护成本。

• 调整PostgreSQL配置参数
  优化影响PL/pgSQL执行的全局配置：

  • 增大shared_buffers：确保足够的共享内存，减少磁盘IO对性能的影响
  • 调整work_mem：为排序、哈希等操作分配足够的工作内存，避免临时文件生成
  • 启用JIT编译：设置jit = on、jit_optimize = on、jit_inline = on，让复杂逻辑编译为机器码执行，提升运行效率

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