postgresql中的select function()对function()的调用过多 [英] select function() in postgresql makes too much calls to function()

问题描述

假设我们具有此功能:

create or replace function foo(a integer)
returns table (b integer, c integer)
language plpgsql
as $$
begin
    raise notice 'foo()';
    return query select a*2, a*4;
    return query select a*6, a*8;
    return query select a*10, a*12;
end;
$$;

募集通知'foo()'"部分将用于知道函数被调用了多少次.

The "raise notice 'foo()'" part will be used to know how many time the function is called.

如果我这样调用函数:

postgres=# SELECT i, foo(i) as bla FROM generate_series(1,3) as i;
NOTICE:  foo()
NOTICE:  foo()
NOTICE:  foo()
 i |   bla   
---+---------
 1 | (2,4)
 1 | (6,8)
 1 | (10,12)
 2 | (4,8)
 2 | (12,16)
 2 | (20,24)
 3 | (6,12)
 3 | (18,24)
 3 | (30,36)
(9 rows)

我们可以看到，正如预期的那样，foo()被调用了3次.

We can see that, as expected, foo() is called 3 times.

但是，如果我以这种方式调用函数(因此我实际上在不同的列中获取foo()结果):

But if i call the function this way (so i actually gets foo() result in different columns):

postgres=# SELECT i, (foo(i)).* FROM generate_series(1,3) as i;
NOTICE:  foo()
NOTICE:  foo()
NOTICE:  foo()
NOTICE:  foo()
NOTICE:  foo()
NOTICE:  foo()
 i | b  | c  
---+----+----
 1 |  2 |  4
 1 |  6 |  8
 1 | 10 | 12
 2 |  4 |  8
 2 | 12 | 16
 2 | 20 | 24
 3 |  6 | 12
 3 | 18 | 24
 3 | 30 | 36
(9 rows)

我们可以看到foo()被调用了6次.并且如果foo()返回3列，则它将被调用9次.显然，对每个i及其返回的每一列都调用foo().

We can see that foo() is called 6 times. And if foo() was returning 3 columns, it would have been called 9 times. It's pretty clear that foo() is called for every i and every column it returns.

我不明白为什么postgres在这里没有进行优化.这对我来说是个问题，因为我的(实际)foo()可能占用大量CPU.有什么主意吗?

I don't understand why postgres does not make an optimisation here. And this is a problem for me as my (real) foo() may be CPU intensive. Any idea ?

使用不可变"函数或不返回多行的函数会产生相同的行为:

Using an "immutable" function or a function that does not return multiple rows gives the same behaviour:

create or replace function foo(a integer)
returns table (b integer, c integer, d integer)
language plpgsql
immutable
as $$
begin
raise notice 'foo';
return query select a*2, a*3, a*4;
end;
$$;

postgres=# select i, (foo(i)).* from generate_series(1,2) as i;
NOTICE:  foo
NOTICE:  foo
NOTICE:  foo
NOTICE:  foo
NOTICE:  foo
NOTICE:  foo
 i | b | c | d 
---+---+---+---
 1 | 2 | 3 | 4
 2 | 4 | 6 | 8
(2 rows)

推荐答案

这是一个已知问题.

SELECT (f(x)).*

在解析时被宏扩展为

SELECT (f(x)).a, (f(x)).b, ...

并且PostgreSQL不会合并对同一函数的多次调用，直到一次调用.

and PostgreSQL doesn't coalesce multiple calls to the same function down to a single call.

为避免此问题，您可以将其包装在子查询的另一层中，以便在对函数结果(而不是函数调用)的简单引用上进行宏扩展:

To avoid the issue you can wrap it in another layer of subquery so that the macro-expansion occurs on a simple reference to the function's result rather than the function invocation:

select i, (f).* 
FROM (
    SELECT i, foo(i) f from generate_series(1,2) as i
) x(i, f)

或在FROM子句中使用横向调用，这是较新版本的首选:

or use a lateral call in the FROM clause, which is preferred for newer versions:

select i, f.*
from generate_series(1,2) as i
    CROSS JOIN LATERAL foo(i) f;

使用传统的逗号联接和隐式横向联接，可以省略CROSS JOIN LATERAL，但是我发现将其包括在内相当明显，尤其是在混合其他联接类型时.

The CROSS JOIN LATERAL may be omitted, using legacy comma joins and an implicit lateral join, but I find it considerably clear to include it, especially when you're mixing other join types.

这篇关于postgresql中的select function()对function()的调用过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！