PostgreSQL中PL/pgSQL函数内计算为何比直接查询慢？

最近我在工作中遇到了一个问题：我有一张包含text类型维度列和numeric类型统计列的表，示例字段如下：

dimension_1, dimension_2, counter_1, counter_2

原本我直接写SQL计算两个统计列的比值作为KPI，查询语句是这样的：

SELECT dimension_1, dimension_2, (counter_1 / NULLIF(counter_2, 0)) as kpi 
from table 
order by kpi desc nulls last;

为了复用这个计算逻辑，我想把它封装成自定义函数，这样后续查询可以写成：

SELECT dimension_1, dimension_2, func(counter_1, counter_2) as kpi 
from table 
order by kpi desc nulls last;

踩坑：PL/pgSQL函数的性能瓶颈 #

最开始我用PL/pgSQL实现了这个函数：

CREATE FUNCTION kpi_latency_ext_msec(val1 numeric, val2 numeric) 
RETURNS numeric AS $func$ 
BEGIN 
    RETURN ($1 / NULLIF($2, 0::numeric)); 
END; 
$func$ LANGUAGE PLPGSQL SECURITY DEFINER IMMUTABLE;

结果是对的，但执行速度慢了不少。我用EXPLAIN ANALYZE对比了两种查询的执行计划，差距一目了然：

用函数的查询执行计划 #

Sort (cost=800.85..806.75 rows=2358 width=26) (actual time=5.534..5.710 rows=2358 loops=1)
  Sort Key: (kpi_latency_ext_msec(external_tcp_handshake_latency_sum, external_tcp_handshake_latency_samples))
  Sort Method: quicksort Memory: 281kB
-> Seq Scan on counters_by_cgi_rat (cost=0.00..668.76 rows=2358 width=26) (actual time=0.142..4.233 rows=2358 loops=1)
      Filter: (("timestamp" >= '2018-05-10 00:00:00'::timestamp without time zone) AND ("timestamp" < '2018-05-13 00:00:00'::timestamp without time zone) AND (granularity = '1 day'::interval))
Planning time: 0.221 ms
Execution time: 5.881 ms

直接写SQL的查询执行计划 #

Sort (cost=223.14..229.04 rows=2358 width=26) (actual time=1.933..2.114 rows=2358 loops=1)
  Sort Key: ((external_tcp_handshake_latency_sum / NULLIF(external_tcp_handshake_latency_samples, 0::numeric)))
  Sort Method: quicksort Memory: 281kB
-> Seq Scan on counters_by_cgi_rat (cost=0.00..91.06 rows=2358 width=26) (actual time=0.010..1.190 rows=2358 loops=1)
      Filter: (("timestamp" >= '2018-05-10 00:00:00'::timestamp without time zone) AND ("timestamp" < '2018-05-13 00:00:00'::timestamp without time zone) AND (granularity = '1 day'::interval))
Planning time: 0.139 ms
Execution time: 2.279 ms

就算去掉ORDER BY只看扫描部分，PL/pgSQL函数的成本和实际耗时还是高很多：

• 直接查询的Seq Scan：成本0.00..91.06，实际耗时0.016..1.223 ms
• 用PL/pgSQL函数的Seq Scan：成本0.00..668.76，实际耗时0.123..3.518 ms

我还尝试移除了SECURITY DEFINER属性，但性能几乎没有变化：

Seq Scan on counters_by_cgi_rat (cost=0.00..668.76 rows=2358 width=26) (actual time=0.035..3.718 rows=2358 loops=1)
  Filter: (("timestamp" >= '2018-05-10 00:00:00'::timestamp without time zone) AND ("timestamp" < '2018-05-13 00:00:00'::timestamp without time zone) AND (granularity = '1 day'::interval))
Planning time: 0.086 ms
Execution time: 3.923 ms

解决方案：改用SQL语言实现函数 #

后来我换成了SQL语言来写这个函数，性能立刻上来了，甚至有时候比直接写SQL还快一点：

优化后的SQL函数 #

CREATE FUNCTION kpi_latency_ext_msec(val1 numeric, val2 numeric) 
RETURNS numeric 
LANGUAGE sql STABLE 
AS 'SELECT $1 / NULLIF($2, 0)';

执行计划验证 #

Seq Scan on counters_by_cgi_rat (cost=0.00..91.06 rows=2358 width=26) (actual time=0.011..1.123 rows=2358 loops=1)
  Filter: (("timestamp" >= '2018-05-10 00:00:00'::timestamp without time zone) AND ("timestamp" < '2018-05-13 00:00:00'::timestamp without time zone) AND (granularity = '1 day'::interval))
Planning time: 0.180 ms
Execution time: 1.294 ms

经过多次测试，这个SQL函数的性能远超PL/pgSQL版本，和直接查询的效率几乎一致，完美解决了性能问题。

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