Re: IMMUTABLE break inlining simple SQL functions.

Hello

I though, so simple SQL functions should be inlined everywhere. When I
tested sample for recent discussion, I found so immutable flag breaks
inlining.

Is it bug?

postgres=# create or replace function foo(a int) returns int as $$
select $1+1$$ language sql STRICT IMMUTABLE;
CREATE FUNCTION
Time: 2,723 ms
postgres=# SELECT * FROM pg_stat_user_functions ;
funcid | schemaname | funcname | calls | total_time | self_time
--------+------------+----------+-------+------------+-----------
16450 | public | foo | 3 | 0 | 0
(1 row)

postgres=# SELECT foo(10);
foo
-----
11
(1 row)

funcid | schemaname | funcname | calls | total_time | self_time
--------+------------+----------+-------+------------+-----------
16450 | public | foo | 4 | 0 | 0
(1 row)

postgres=# create or replace function foo(a int) returns int as $$
select $1+1$$ language sql STRICT;
CREATE FUNCTION
Time: 3,716 ms
postgres=# SELECT foo(11);
foo
-----
12
(1 row)

Time: 1,611 ms
postgres=# SELECT * FROM pg_stat_user_functions ; funcid | schemaname
| funcname | calls | total_time | self_time
--------+------------+----------+-------+------------+-----------
16450 | public | foo | 4 | 0 | 0
(1 row)

regards
Pavel Stehule

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> I though, so simple SQL functions should be inlined everywhere. When I
> tested sample for recent discussion, I found so immutable flag breaks
> inlining.

Your example proves nothing of the sort, since you have forgotten to
allow for immutable functions being folded to constants.

Actually, AFAICS both of these cases end up being folded to a constant,
but I think the processing path is different --- one will just be
evaluated on sight, the other gets inlined and is then seen to be a
constant expression:

regression=# create or replace function foo(a int) returns int as $$
regression$# select $1+1$$ language sql STRICT IMMUTABLE;
CREATE FUNCTION
regression=# explain verbose select foo(12);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: 13
(2 rows)

regression=# create or replace function foo(a int) returns int as $$
select $1+1$$ language sql STRICT ;
CREATE FUNCTION
regression=# explain verbose select foo(12);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: 13
(2 rows)

The fact that it gets inlined rather than evaluated per se is probably
why the stats counter isn't affected.

regards, tom lane