Postgresql：过滤函数输出与传递过滤器参数到函数(Postgresql: Filter function output vs passing filter parameter to the f

Postgresql：过滤函数输出与传递过滤器参数到函数(Postgresql: Filter function output vs passing filter parameter to the function)

以下查询之间的性能是否存在差异？

1. foo（）返回一个未经过滤的集合，然后进行过滤：

create function foo() returns setof table1 as $$ select * from table1 $$ language SQL; select * from foo() where name = 'bar'

2. foo（）接受一个参数并返回一个过滤集：

create function foo(varchar) returns setof table1 as $$ select * from table1 where name = $1 $$ language SQL; select * from foo('bar')

我假设DB在计划最终查询之前足够聪明地“内联”该函数，因此它在执行中没有任何区别。 但我不确定。

Is there a difference in performance between the following queries?

1. foo() returns an unfiltered set which is then filtered:

create function foo() returns setof table1 as $$ select * from table1 $$ language SQL; select * from foo() where name = 'bar'

2. foo() accepts a parameter and returns a filtered set:

create function foo(varchar) returns setof table1 as $$ select * from table1 where name = $1 $$ language SQL; select * from foo('bar')

I assume the DB is smart enough to "inline" the function before planning the final query, so that it make no difference in execution. But i'm not sure.

最满意答案

第一个运行函数没有任何参数（可能获得更多数据），然后在字段上过滤数据。 所以可能会花费更多。 第二个运行带参数的函数（可能减少函数运行的数据） 没有身体的功能，这是纯粹的猜测

I have found a wiki page that answers this.

There are multiple conditions that have to be met for the function to be inlined and thus get opmimized as part of the whole query.

A function in question will be inlined if it's declared Immutable or Stable:

create function foo() returns setof table1 as $$ select * from table1 $$ language SQL stable;

Stable is more appropriate because the function does a table lookup.