Thread: Interesting case of IMMUTABLE significantly hurting performance
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
On Wed, 2025-04-09 at 19:12 +0300, Olleg Samoylov wrote: > 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) 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
On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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.
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.
On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote: > 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 --
On 4/9/25 14:21, Nico Williams wrote: > On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote: >> 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. 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." > > Nico -- Adrian Klaver adrian.klaver@aklaver.com
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> The IMMUTABLE function cannot be inlined because to_char() is not
>> IMMUTABLE.
> 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.
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
On 10.04.2025 01:08, Tom Lane wrote: > 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.) > regards, tom lane Well, to_char(bigint, text) indeed not immutable, because in some pattern it uses get information from locale. For instance,'SLDG' patterns. But in case of 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')); to_char do not use locale information in this pattern. So it is correct conclude that to_char is immutable with this pattern and formatted_num_immutable too. I did not lie to the planner. So this is looked "strange", immutable function marked as immutable function can not be inlined, but exactly the same function marked as volatile do. -- Olleg
On Wednesday, April 9, 2025, Olleg Samoylov <splarv@ya.ru> wrote:
On 10.04.2025 01:08, Tom Lane wrote: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.)
regards, tom lane
Well, to_char(bigint, text) indeed not immutable, because in some pattern it uses get information from locale. For instance,'SLDG' patterns. But in case of
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'));
to_char do not use locale information in this pattern. So it is correct conclude that to_char is immutable with this pattern and formatted_num_immutable too. I did not lie to the planner.
So this is looked "strange", immutable function marked as immutable function can not be inlined, but exactly the same function marked as volatile do.
Yeah, the inlining is an optimization, and while it seems like it could perform more tests or maybe make slightly different/more adjustments, it really isn’t worth the development effort or runtime cost to do so. Make your function volatility match the most volatile function you internally call - constant input arguments don’t change this.
There is no reason to perform number formatting immutably - function call results involving table data are not memoized.
David J.
On 4/9/25 22:33, Olleg Samoylov wrote: > > > On 10.04.2025 01:08, Tom Lane wrote: >> 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.) >> regards, tom lane > > Well, to_char(bigint, text) indeed not immutable, because in some > pattern it uses get information from locale. For instance,'SLDG' > patterns. But in case of > > 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')); > > to_char do not use locale information in this pattern. So it is correct > conclude that to_char is immutable with this pattern and > formatted_num_immutable too. I did not lie to the planner. I doubt the code actually looks at the template, it just does the lookup on pg_proc and sees: proname | provolatile | prosrc ---------+-------------+--------------------- to_char | s | int8_to_char > > So this is looked "strange", immutable function marked as immutable > function can not be inlined, but exactly the same function marked as > volatile do. -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote: > On 4/9/25 14:21, Nico Williams wrote: > > That to_char is not immutable is not documented though. Though it's > > 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'; > [...] I'm surprised to see that counted as docs, but good to know.
On Thu, Apr 10, 2025 at 8:49 AM Nico Williams <nico@cryptonector.com> wrote:
On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
> On 4/9/25 14:21, Nico Williams wrote:
> > That to_char is not immutable is not documented though. Though it's
> > 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';
> [...]
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.
On 4/10/25 08:48, Nico Williams wrote: > On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote: >> On 4/9/25 14:21, Nico Williams wrote: >>> That to_char is not immutable is not documented though. Though it's >>> 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'; >> [...] > > 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. -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Apr 10, 2025 at 10:59 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Apr 10, 2025 at 8:49 AM Nico Williams <nico@cryptonector.com> wrote:On Wed, Apr 09, 2025 at 02:43:11PM -0700, Adrian Klaver wrote:
> On 4/9/25 14:21, Nico Williams wrote:
> > That to_char is not immutable is not documented though. Though it's
> > 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';
> [...]
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.
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
Merlin Moncure <mmoncure@gmail.com> 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.956rows=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.780rows=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
On Thursday, April 10, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Merlin Moncure <mmoncure@gmail.com> 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.
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.
On Thu, 2025-04-10 at 22:18 -0500, Merlin Moncure wrote: > Facts. This is black magic. This has come up over and over. Perhaps it would help to add the excellent information from the Wiki to the documentation: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions Yours, Laurenz Albe
On Fri, Apr 11, 2025 at 07:15:44AM +0200, Laurenz Albe wrote: > On Thu, 2025-04-10 at 22:18 -0500, Merlin Moncure wrote: > > Facts. This is black magic. This has come up over and over. > > Perhaps it would help to add the excellent information from the Wiki to the documentation: > https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions Adding a warning in the functions _docs_ to check their various attributes in the cataclog would suffice.
On Fri, Apr 11, 2025 at 5:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> 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. 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.
On Thu, Apr 10, 2025 at 10:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Merlin Moncure <mmoncure@gmail.com> 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:
ah -- gotcha. misread the original email -- should have known better :)
thanks
merlin
Tom Lane: > 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. 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