Subject: Interesting case of IMMUTABLE significantly hurting performance

From: Olleg Samoylov <[email-masked]>
Date: Wed, 9 Apr 2025 19:12:18 +0300

PostgreSQL 17.4

CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summa bigint)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE STRICT
RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999'));

CREATE OR REPLACE FUNCTION formatted_num_stable(p_summa bigint)
  RETURNS text
  LANGUAGE sql
  STABLE STRICT
RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999'));

explain analyze select formatted_num_immutable(i) from 
generate_series(1,1000000) as i(i);
                                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
  Function Scan on generate_series i  (cost=0.00..262500.00 rows=1000000 
width=32) (actual time=56.892..1548.656 rows=1000000 loops=1)
  Planning Time: 0.039 ms
  JIT:
    Functions: 4
    Options: Inlining false, Optimization false, Expressions true, 
Deforming true
    Timing: Generation 0.184 ms (Deform 0.040 ms), Inlining 0.000 ms, 
Optimization 0.115 ms, Emission 1.570 ms, Total 1.869 ms
  Execution Time: 1587.741 ms
(7 rows)

explain analyze select formatted_num_stable(i) from 
generate_series(1,1000000) as i(i);
                                                              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
  Function Scan on generate_series i  (cost=0.00..17500.00 rows=1000000 
width=32) (actual time=54.993..573.333 rows=1000000 loops=1)
  Planning Time: 0.056 ms
  Execution Time: 598.190 ms
(3 rows)

First interesting thing is immutable variant has cost in 15 time more, 
then stable. That's why jit compilation is tuned on. Second, immutable 
function is working much longer (3 times). And jit is not the reason.

=> set jit=off;
SET
=> explain analyze select formatted_num_immutable(i) from 
generate_series(1,1000000) as i(i);
                                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
  Function Scan on generate_series i  (cost=0.00..262500.00 rows=1000000 
width=32) (actual time=54.888..1537.602 rows=1000000 loops=1)
  Planning Time: 0.052 ms
  Execution Time: 1575.985 ms
(3 rows)

-- 
Olleg

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 09 Apr 2025 22:50:00 +0200

> generate_series(1,1000000) as i(i);

If you use EXPLAIN (VERBOSE), you will see that the function gets inlined in the fast case.

That saves the overhead of a function call.

The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE.

Yours,
Laurenz Albe

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 9 Apr 2025 14:06:12 -0700

> The IMMUTABLE function cannot be inlined because to_char() is not

So, the punishment for lying about the volatility of one's function is to
prohibit it from being inlined even in a case where had you been truthful
about the volatility it would have been inlined.

David J.

---

From: Nico Williams <[email-masked]>
Date: Wed, 9 Apr 2025 16:21:40 -0500

> The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE.

Q: Why would to_char() not be IMMUTABLE?

A: Because it makes use of locales, and I guess the guc-timezone GUC,
   which could change if the expression is ultimately used in a PlPgSQL
   fragment, or if it's in a prepared statement.  (I think.)

That to_char is not immutable is not documented though.  Though it's
clear when looking at the docs for the `jsonb_.*_tz()` functions.

Nico
--

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 9 Apr 2025 14:43:11 -0700

> clear when looking at the docs for the `jsonb_.*_tz()` functions.

 From here:

https://www.postgresql.org/docs/current/catalog-pg-proc.html

select proname, provolatile, prosrc  from pg_proc where proname='to_char';
  proname | provolatile |       prosrc
---------+-------------+---------------------
  to_char | s           | timestamptz_to_char
  to_char | s           | numeric_to_char
  to_char | s           | int4_to_char
  to_char | s           | int8_to_char
  to_char | s           | float4_to_char
  to_char | s           | float8_to_char
  to_char | s           | interval_to_char
  to_char | s           | timestamp_to_char

Where 's' is:

"It is s for “stable” functions, whose results (for fixed inputs) do not 
change within a scan."

-- 
Adrian Klaver
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Apr 2025 18:08:08 -0400

> about the volatility it would have been inlined.

Yeah.  The assumption is that you had a reason for marking the
function IMMUTABLE and you want the planner to treat it that way
even if it isn't really.  (There are valid use-cases for that, for
instance if you want calls to the function to be constant-folded.)

So we don't inline the function --- if we did, the merely-stable
contained expression would be exposed and then treated as STABLE.
But that comes at a pretty substantial cost, since the
SQL-language-function executor isn't exactly free.

If you err in the other direction, you don't get slapped on the
wrist that way.  We're willing to inline VOLATILE functions,
for instance, whether or not the contained expression is volatile.
Similarly for STRICT, and I think parallel safety as well.
So my own habit when writing a SQL function that I wish to be
inlined is to leave off all those markings.  They won't matter
if the function is successfully inlined, and they might get in
the way of that happening.

			regards, tom lane

---

From: Nico Williams <[email-masked]>
Date: Thu, 10 Apr 2025 10:48:40 -0500

On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
> [...]

I'm surprised to see that counted as docs, but good to know.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 10 Apr 2025 08:59:05 -0700

> I'm surprised to see that counted as docs, but good to know.
Consulting pg_proc constitutes, IMO, going outside the documentation to
retrieve information.  It is just not high up on anyone's annoyance list to
try and get this piece of information incorporated into the documentation.
Partly because \df+ does show this information as well, so at least one
doesn't have to go write the catalog query themself.

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 10 Apr 2025 09:00:16 -0700

> I'm surprised to see that counted as docs, but good to know.

When in doubt consult the system catalogs.

My guess is the absence of specific volatility information in the data 
formatting functions is due to that information originating from a time 
before provolatile existed. Whereas the jsonb_.*_tz() functions are 
relatively new and where documented with knowledge of provolatile.

---

From: Merlin Moncure <[email-masked]>
Date: Thu, 10 Apr 2025 22:18:42 -0500

> doesn't have to go write the catalog query themself.

Facts.  This is black magic.   This has come up over and over.

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
*) there is no way to discern inline vs non-inlined execution in explain
*) the planner is clearly not modelling function scan overhead give the
relative costing discrepancies

I think the first point is the most serious. A warning on function
creation, 'WARNING: this function is not provably immutable and therefore
is not eligible for inlining" with some appropriate hints might do the
trick, in the very specific situation where the function is otherwise
eligible.

merlin

---

From: Wolfgang Walther <[email-masked]>
Date: Mon, 14 Apr 2025 12:18:44 +0200

Tom Lane:
> inlined is to leave off all those markings.

According to [1], this only applies to inlining of scalar functions, but 
not to table functions, which *need* to be either STABLE or IMMUTABLE.

Just mentioning this for anyone taking this advice blindly and leaving 
all marks off, which might not always work as expected in the general case.

Best,

Wolfgang

[1]: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions