postgresql - 嵌套 plpgsql 函数中的计算比直接查询慢？

Question

我有一个包含一些 text 和一些 numeric 列的表格，例如:

dimension_1, dimension_2, counter_1, counter_2

而不是执行查询

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;

我在 Postgres 中使用了以下实现:

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

第二个查询(无功能):

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 执行查询:

无功能:

Seq Scan on table (cost=0.00..91.06 rows=2358 width=26) (actual time=0.016..1.223 rows=2358 loops=1)

带有函数:

Seq Scan on table (cost=0.00..668.76 rows=2358 width=26) (actual time=0.123..3.518 rows=2358 loops=1)

RESULTS 函数 no 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

RESULTS 普通查询

Seq Scan on counters_by_cgi_rat  (cost=0.00..91.06 rows=2358 width=26)    
                                 (actual time=0.017..1.175 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.105 ms
 Execution time: 1.356 ms

RESULTS 语言 sql

 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 语言

肯定比使用语言 plpgsql 快，但比原始查询稍慢(重复运行后)

=========更新=========

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

使用上述函数获得的最佳结果(甚至比普通查询更快)

Answer 1

毒镖是 SECURITY DEFINER。声明为 SECURITY DEFINER 的函数不能被内联 - 如果我没记错的话会强制执行上下文切换。这会使它们变得更加昂贵。示例中确实不需要 SECURITY DEFINER。简单计算不需要不同的权限。 (也许您的实际用例有所不同。)

而且也不需要 PL/pgSQL。 只有 SQL 函数可以内联 - 如果满足一些额外的先决条件。

因为所有使用的函数都是IMMUTABLE，你应该声明函数IMMUTABLE。 (默认函数波动性是 VOLATILE。)您已经相应地更新了问题。这允许表达式索引并且可以帮助防止在某些情况下重复评估。但它从不有助于函数内联。相反:它强加了更多的先决条件(在这种情况下得到满足)。引用 Postgres Wiki on function inlining (撰写本文时的最后更新时间为 2016 年):

if the function is declared IMMUTABLE, then the expression must not invoke any non-immutable function or operator

引用 Tom Lane on pgsql-performance :

The basic point here is that a function marked volatile can be expanded to its contained functions even if they're immutable; but the other way around represents a potential semantic change, so the planner won't do it.

解决方案

尝试不使用 SECURITY DEFINER:

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

应该已经多快了。

或者从根本上简化为一个 SQL 函数:

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

更快，还是？

相关:

• How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

基准

我一开始使用了IF 和CASE 表达式，但是在a_horse_with_no_name's comment 之后我运行了广泛的测试，显示 NULLIF 稍微快一些。所以我相应地简化了原始的 NULLIF 变体。

_{主要还是没有SECURITY DEFINER、SQL和IMMUTABLE。}

数据库<> fiddle here - 第 10 页
数据库<> fiddle here - 第 9.4 页