扩展复合结果时避免对同一函数进行多次调用 [英] Avoid multiple calls on same function when expanding composite result

问题描述

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

I have an SQL function retuning a composite result.

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;

在调用该函数时，我希望将复合类型的部分扩展为几列.当我打电话时，这很好用:

When calling the function, I would like to have the parts of the composite type expanded into several columns. That works fine when I call:

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

不幸的是，这会导致每个结果列调用一次函数，这会导致不必要的缓慢.这可以通过将运行时间与查询进行比较来测试，查询直接返回复合结果并且运行速度提高了四倍:

Unfortunately, this causes the function to be called once per result column, which is unnecessarily slow. This can be tested by comparing the run time with a query, which directly returns the composite result and runs four times as fast:

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

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

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

How can I get the result with multiple columns without wasting CPU power?

推荐答案

甚至不需要 CTE.普通子查询也能完成这项工作(用 pg 9.3 测试):

A CTE is not even necessary. A plain subquery does the job as well (tested with pg 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;

一定不要在子查询中分解函数的复合结果.保留用于外部查询.
当然，需要一个众所周知的类型.不适用于匿名记录.

Be sure not to decompose the composite result of the function in the subquery. Reserve that for the outer query.
Requires a well known type, of course. Would not work with anonymous records.

或者，@Richard 写的，LATERAL JOIN 也能用.语法可以更简单:

Or, what @Richard wrote, a LATERAL JOIN works, too. The syntax can be simpler:

SELECT * FROM generate_series(1, 3) i, slow_func(i) f

• LATERAL 在 Postgres 9.3 或更高版本中隐式应用.
• 一个函数可以在 FROM 子句中独立存在，不必包装在额外的子选择中.想象一下在它的位置放一张桌子.
• LATERAL is applied implicitly in Postgres 9.3 or later.
• A function can stand on its own in the FROM clause, doesn't have to be wrapped in an additional sub-select. Just imagine a table in its place.

SQL Fiddle 与 EXPLAIN VERBOSE 所有变体的输出.如果发生该函数，您可以看到多次评估.

SQL Fiddle with EXPLAIN VERBOSE output for all variants. You can see multiple evaluation of the function if it happens.

通常(对于这个特定查询应该无关紧要)，请确保对您的函数应用高成本设置，以便规划器知道避免更频繁地进行评估.喜欢:

Generally (should not matter for this particular query), make sure to apply a high cost setting to your function, so the planner knows to avoid evaluating more often then necessary. Like:

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

每个文档:

较大的值会导致规划器尝试避免对函数进行不必要的评估.

Larger values cause the planner to try to avoid evaluating the function more often than necessary.

这篇关于扩展复合结果时避免对同一函数进行多次调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！