1. 首页
  2. 问题
  3. 扩展复合结果时，避免在同一函数上多次调用
admin

扩展复合结果时，避免在同一函数上多次调用

sql

我有一个SQL函数重新调整复合结果。

CREATE TYPE result_t AS (a int, b int, c int, d numeric);

CREATE OR REPLACE FUNCTION slow_function(int)
RETURNS result_t
AS $$
    -- just some placeholder code to make it slow
    SELECT 0, 0, 0, (
        SELECT sum(ln(i::numeric))
        FROM generate_series(1, $1) i
    )
$$ LANGUAGE sql IMMUTABLE;

调用函数时，我希望将复合类型的各个部分扩展为几列。当我打电话时，这很好用：

SELECT (slow_function(i)).*
FROM generate_series(0, 200) i

a     b     c     d                    
----  ----  ----  -------------------- 
0     0     0     (null)               
0     0     0     0                    
0     0     0     0.6931471805599453   
0     0     0     1.791759469228055
...
Total runtime: 6196.754 ms

不幸的是，这将导致 每个结果列 仅调用 一次
该函数，这会不必要地降低速度。可以通过将运行时间与查询进行比较来进行测试，查询可以直接返回复合结果并以四倍的速度运行：

SELECT slow_function(i)
FROM generate_series(0, 200) i
...
Total runtime: 1561.476 ms

示例代码也位于http://sqlfiddle.com/#!15/703ba/7

如何在不浪费CPU能力的情况下获得多列结果？


阅读 161

收藏
2021-07-01

共1个答案

admin

甚至不需要CTE。一个 普通的子查询 也可以完成这项工作（已在9.3版中进行了测试）：

SELECT i, (f).*                     -- decompose here
FROM  (
   SELECT i, (slow_func(i)) AS f    -- do not decompose here
   FROM   generate_series(1, 3) i
   ) sub;

确保不要在子查询中分解函数的复合结果。将其保留给 外部查询 。
当然需要一个众所周知的类型。无法使用匿名记录。

或者说，什么@Richard写道，一个 LATERAL JOIN 作品了。语法可以更简单：

SELECT * FROM generate_series(1, 3) i, slow_func(i) f
  • LATERAL 在Postgres 9.3或更高版本中隐式应用。
  • 函数可以在FROM子句中独立存在，而不必包装在其他子选择中。试想一下在其位置上有一张桌子。

SQL Fiddle 具有EXPLAIN VERBOSE所有变体的输出。如果发生该功能，您可以 看到 对该功能的多次评估。

COST 环境

通常，（对于此特定查询无关紧要），请确保对您的函数应用高成本设置，因此计划者会知道避免进行更多必要的评估。喜欢：

CREATE OR REPLACE FUNCTION slow_function(int)
  RETURNS result_t AS
$func$
    -- expensive body
$func$ LANGUAGE sql IMMUTABLE **COST 100000** ;

每个文档：

较大的值会使计划人员尝试避免不必要地评估函数。

2021-07-01