Subject: Re: Interesting case of IMMUTABLE significantly hurting performance

From: Tom Lane <[email-masked]>
Date: Thu, 10 Apr 2025 23:51:53 -0400

Merlin Moncure <[email-masked]> writes:
> I guess the real problems here are lack of feedback on a number of fronts:
> *) the server knows the function is not immutable but lets you create it
> anyway, even though it can have negative downstream consequences

That's debatable I think.  If you know what you're doing, you're going
to be annoyed by warnings telling you that you don't.

> *) there is no way to discern inline vs non-inlined execution in explain

That's simply false.  Using the examples in this thread:

regression=# explain (verbose,analyze)
select formatted_num_immutable(i) from generate_series(1,1000000) i;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series i  (cost=0.00..262500.00 rows=1000000 width=32) (actual time=65.535..2444.956 rows=1000000.00 loops=1)
   Output: formatted_num_immutable((i)::bigint)
   Function Call: generate_series(1, 1000000)
   Buffers: temp read=1709 written=1709
 Planning Time: 0.086 ms
 Execution Time: 2481.218 ms
(6 rows)

regression=# explain (verbose,analyze)
select formatted_num_stable(i) from generate_series(1,1000000) i;
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series i  (cost=0.00..17500.00 rows=1000000 width=32) (actual time=65.615..478.780 rows=1000000.00 loops=1)
   Output: ltrim(to_char((i)::bigint, '999 999 999 999 999 999 999 999'::text))
   Function Call: generate_series(1, 1000000)
   Buffers: temp read=1709 written=1709
 Planning Time: 0.091 ms
 Execution Time: 501.412 ms
(6 rows)

You can easily see that the second case was inlined, because you don't
see the SQL function anymore, rather its body.

> *) the planner is clearly not modelling function scan overhead give the
> relative costing discrepancies

That's also false; note the 15x difference in estimated cost above,
which is actually more than the real difference in runtime.  (I hasten
to add that I don't have a lot of faith in our function cost
estimates.  But the planner is quite well aware that a non-inlined SQL
function is likely to be expensive.)

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 10 Apr 2025 21:13:37 -0700

> to be annoyed by warnings telling you that you don't.

So long as you use atomic SQL functions I suspect it is possible to use the
dependency data to get the volatility of the used functions and compare
them to the volatility of the UDF.

David J.

---

From: Dominique Devienne <[email-masked]>
Date: Fri, 11 Apr 2025 09:59:18 +0200

> to be annoyed by warnings telling you that you don't.

True, but that's typically what pragmas in the code are for then,
to explicitly suppress warnings.

Or a new option in the CREATE FUNCTION command. Better safe than sorry,
and I prefer useful "on-by-default" warnings for the non-experts,
which can be disabled, than silence.

PS: And FWIW, I removed all attributes from the functions I'm
writting, based on your aerlier advice Tom.

---

From: Merlin Moncure <[email-masked]>
Date: Fri, 11 Apr 2025 18:31:35 -0500

> That's simply false.  Using the examples in this thread:

ah -- gotcha.  misread the original email --  should have known better :)
thanks

merlin