PostgreSQL中SELECT调用NUMERIC类型函数为何开销更高

性能差异的核心原因如下： #

• PL/pgSQL函数的调用开销
  你测试用的f1是PL/pgSQL语言的函数，每次逐行调用时都需要在SQL执行引擎和PL/pgSQL解释器之间做上下文切换，包含入参校验、函数栈分配、结果返回的额外步骤。500万行的场景下，这部分开销会累积得非常明显。而直接写random + 1是SQL引擎原生支持的运算，全程在SQL执行层完成，没有额外的切换成本。
• 函数属性限制了优化器优化
  PostgreSQL的函数默认volatility属性是VOLATILE，代表函数的返回值不确定，即使入参相同也可能返回不同结果。优化器面对这类函数无法做内联、批量计算、常量折叠等优化操作，只能逐行严格执行。而原生的数值加法属于IMMUTABLE类型的运算，优化器可以针对整表查询做大量执行计划层面的优化，执行效率自然更高。
• 函数逻辑的解释执行开销
  PL/pgSQL属于解释执行的语言，即使是简单的加法运算，也要经过解释器的语法解析、执行步骤调度，远不如原生运算直接调用底层C语言实现的数值运算效率高。

优化建议 #

如果你的封装逻辑是纯计算、没有读写任何表、相同入参一定会返回相同结果，可以做两个调整降低开销：

1. 创建函数时显式指定IMMUTABLE属性
2. 改用SQL函数实现逻辑，示例如下：

create or replace function f1 (i NUMERIC)
returns NUMERIC
LANGUAGE sql IMMUTABLE
AS
$body$
select i+1;
$body$;

调整后再执行查询，性能会和直接写原生运算的差距大幅缩小，部分场景下优化器可以直接把函数内联成原生运算，性能完全一致。

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