Thread: Why is my function inlined only when STABLE?

Hi all,
I have a function that isn't being inlined, and I would appreciate help to understand why that's the case.

I'm using PG 11.15. I know that if I declare my function IMMUTABLE and it calls a non-IMMUTABLE function, Postgres
won'tinline my function. But even when my function calls only substring() (which I understand to be IMMUTABLE based on
'\df+substring'), I still can't get Postgres to inline it. If I re-declare my function as STABLE, then Postgres inlines
it.According to the rules I understand
(https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#Inlining_conditions_for_scalar_functions),the IMMUTABLE
versionof my function should be inlined too. What am I missing? 

Here's a log of a CLI session showing that the IMMUTABLE version is not inlined, but the STABLE one is.


show track_functions
+-------------------+
| track_functions   |
|-------------------|
| all               |
+-------------------+
SHOW
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid   | schemaname   | funcname   | calls   | total_time   | self_time   |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.021s
me@/tmp:wylan#
CREATE OR REPLACE FUNCTION f(foo text)
 RETURNS text
 AS $$
     SELECT substring(foo FROM 1 FOR 2)
 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
CREATE FUNCTION
Time: 0.003s
me@/tmp:wylan#
select f('4242')
+-----+
| f   |
|-----|
| 42  |
+-----+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid   | schemaname   | funcname   | calls   | total_time   | self_time   |
|----------+--------------+------------+---------+--------------+-------------|
| 14472085 | public       | f          | 1       | 0.05         | 0.05        |
+----------+--------------+------------+---------+--------------+-------------+
SELECT 1
Time: 0.022s
me@/tmp:wylan#
DROP FUNCTION f(text)
DROP FUNCTION
Time: 0.001s
me@/tmp:wylan#
CREATE OR REPLACE FUNCTION f(foo text)
 RETURNS text
 AS $$
     SELECT substring(foo FROM 1 FOR 2)
 $$ LANGUAGE sql STABLE PARALLEL SAFE;
CREATE FUNCTION
Time: 0.003s
me@/tmp:wylan#
select pg_stat_reset()
+-----------------+
| pg_stat_reset   |
|-----------------|
|                 |
+-----------------+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid   | schemaname   | funcname   | calls   | total_time   | self_time   |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.022s
me@/tmp:wylan#
select f('4242')
+-----+
| f   |
|-----|
| 42  |
+-----+
SELECT 1
Time: 0.008s
me@/tmp:wylan#
SELECT * FROM pg_stat_user_functions
+----------+--------------+------------+---------+--------------+-------------+
| funcid   | schemaname   | funcname   | calls   | total_time   | self_time   |
|----------+--------------+------------+---------+--------------+-------------|
+----------+--------------+------------+---------+--------------+-------------+
SELECT 0
Time: 0.019s
me@/tmp:wylan#




Thanks
Philip

Philip Semanchuk <philip@americanefficient.com> writes:
> I have a function that isn't being inlined, and I would appreciate help to understand why that's the case.

The example you show *is* inline-able, as you can easily prove with EXPLAIN.

regression=# CREATE OR REPLACE FUNCTION f(foo text)
 RETURNS text
 AS $$
     SELECT substring(foo FROM 1 FOR 2)
 $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
CREATE FUNCTION

regression=# explain verbose select f(f1) from text_tbl;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on public.text_tbl  (cost=0.00..1.02 rows=2 width=32)
   Output: "substring"(f1, 1, 2)
(2 rows)

No f() anywhere there.

I think the test methodology you used is faulty, because it does not
distinguish between "inline-able" and "foldable to a constant".
Given an immutable function applied to constant(s), the planner prefers
to fold to a constant by just executing the function.  The inline-ing
transformation is considered only when that case doesn't apply.

            regards, tom lane

> On Mar 29, 2022, at 2:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Philip Semanchuk <philip@americanefficient.com> writes:
>> I have a function that isn't being inlined, and I would appreciate help to understand why that's the case.
>
> I think the test methodology you used is faulty, because it does not
> distinguish between "inline-able" and "foldable to a constant".
> Given an immutable function applied to constant(s), the planner prefers
> to fold to a constant by just executing the function.  The inline-ing
> transformation is considered only when that case doesn't apply.

Excellent point, thank you. Now I understand. I was trying to write an inlining demo for my colleagues, and I
simplifiedmy example one step too far by using a constant. 

I really appreciate the help!

Cheers
Philip