public inbox for [email protected]
help / color / mirror / Atom feedInteresting case of IMMUTABLE significantly hurting performance
11+ messages / 8 participants
[nested] [flat]
* Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 16:12 Olleg Samoylov <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Olleg Samoylov @ 2025-04-09 16:12 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 20:50 Laurenz Albe <[email protected]>
parent: Olleg Samoylov <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Laurenz Albe @ 2025-04-09 20:50 UTC (permalink / raw)
To: Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:06 David G. Johnston <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2025-04-09 21:06 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe <[email protected]>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:21 Nico Williams <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Nico Williams @ 2025-04-09 21:21 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:43 Adrian Klaver <[email protected]>
parent: Nico Williams <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2025-04-09 21:43 UTC (permalink / raw)
To: Nico Williams <[email protected]>; Laurenz Albe <[email protected]>; +Cc: Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 22:08 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2025-04-09 22:08 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe <[email protected]>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 15:48 Nico Williams <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: Nico Williams @ 2025-04-10 15:48 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 15:59 David G. Johnston <[email protected]>
parent: Nico Williams <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2025-04-10 15:59 UTC (permalink / raw)
To: Nico Williams <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Thu, Apr 10, 2025 at 8:49 AM Nico Williams <[email protected]> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 16:00 Adrian Klaver <[email protected]>
parent: Nico Williams <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Adrian Klaver @ 2025-04-10 16:00 UTC (permalink / raw)
To: Nico Williams <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 03:18 Merlin Moncure <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Merlin Moncure @ 2025-04-11 03:18 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Nico Williams <[email protected]>; Adrian Klaver <[email protected]>; Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Thu, Apr 10, 2025 at 10:59 AM David G. Johnston <
[email protected]> wrote:
> On Thu, Apr 10, 2025 at 8:49 AM Nico Williams <[email protected]>
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-14 10:18 Wolfgang Walther <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Wolfgang Walther @ 2025-04-14 10:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Olleg Samoylov <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-04-14 10:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-09 16:12 Interesting case of IMMUTABLE significantly hurting performance Olleg Samoylov <[email protected]>
2025-04-09 20:50 ` Laurenz Albe <[email protected]>
2025-04-09 21:06 ` David G. Johnston <[email protected]>
2025-04-09 22:08 ` Tom Lane <[email protected]>
2025-04-14 10:18 ` Wolfgang Walther <[email protected]>
2025-04-09 21:21 ` Nico Williams <[email protected]>
2025-04-09 21:43 ` Adrian Klaver <[email protected]>
2025-04-10 15:48 ` Nico Williams <[email protected]>
2025-04-10 15:59 ` David G. Johnston <[email protected]>
2025-04-11 03:18 ` Merlin Moncure <[email protected]>
2025-04-10 16:00 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox