pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedInteresting case of IMMUTABLE significantly hurting performance
20+ messages / 9 participants
[nested] [flat]
* Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 16:12 Olleg Samoylov <splarv@ya.ru>
0 siblings, 1 reply; 20+ messages in thread
From: Olleg Samoylov @ 2025-04-09 16:12 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 20:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Olleg Samoylov <splarv@ya.ru>
0 siblings, 2 replies; 20+ messages in thread
From: Laurenz Albe @ 2025-04-09 20:50 UTC (permalink / raw)
To: Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 20+ messages in thread
From: David G. Johnston @ 2025-04-09 21:06 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:21 Nico Williams <nico@cryptonector.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 20+ messages in thread
From: Nico Williams @ 2025-04-09 21:21 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 21:43 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Nico Williams <nico@cryptonector.com>
0 siblings, 1 reply; 20+ messages in thread
From: Adrian Klaver @ 2025-04-09 21:43 UTC (permalink / raw)
To: Nico Williams <nico@cryptonector.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-09 22:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Tom Lane @ 2025-04-09 22:08 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 05:33 Olleg Samoylov <splarv@ya.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 20+ messages in thread
From: Olleg Samoylov @ 2025-04-10 05:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 14:01 David G. Johnston <david.g.johnston@gmail.com>
parent: Olleg Samoylov <splarv@ya.ru>
1 sibling, 0 replies; 20+ messages in thread
From: David G. Johnston @ 2025-04-10 14:01 UTC (permalink / raw)
To: Olleg Samoylov <splarv@ya.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 15:38 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Olleg Samoylov <splarv@ya.ru>
1 sibling, 0 replies; 20+ messages in thread
From: Adrian Klaver @ 2025-04-10 15:38 UTC (permalink / raw)
To: Olleg Samoylov <splarv@ya.ru>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 15:48 Nico Williams <nico@cryptonector.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 20+ messages in thread
From: Nico Williams @ 2025-04-10 15:48 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 15:59 David G. Johnston <david.g.johnston@gmail.com>
parent: Nico Williams <nico@cryptonector.com>
1 sibling, 1 reply; 20+ messages in thread
From: David G. Johnston @ 2025-04-10 15:59 UTC (permalink / raw)
To: Nico Williams <nico@cryptonector.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-10 16:00 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Nico Williams <nico@cryptonector.com>
1 sibling, 0 replies; 20+ messages in thread
From: Adrian Klaver @ 2025-04-10 16:00 UTC (permalink / raw)
To: Nico Williams <nico@cryptonector.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 03:18 Merlin Moncure <mmoncure@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Merlin Moncure @ 2025-04-11 03:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 03:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 3 replies; 20+ messages in thread
From: Tom Lane @ 2025-04-11 03:51 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 04:13 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 20+ messages in thread
From: David G. Johnston @ 2025-04-11 04:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 05:15 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Laurenz Albe @ 2025-04-11 05:15 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 05:30 Nico Williams <nico@cryptonector.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 20+ messages in thread
From: Nico Williams @ 2025-04-11 05:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Merlin Moncure <mmoncure@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 07:59 Dominique Devienne <ddevienne@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 20+ messages in thread
From: Dominique Devienne @ 2025-04-11 07:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-11 23:31 Merlin Moncure <mmoncure@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 20+ messages in thread
From: Merlin Moncure @ 2025-04-11 23:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Nico Williams <nico@cryptonector.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Interesting case of IMMUTABLE significantly hurting performance
@ 2025-04-14 10:18 Wolfgang Walther <walther@technowledgy.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 20+ messages in thread
From: Wolfgang Walther @ 2025-04-14 10:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Olleg Samoylov <splarv@ya.ru>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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] 20+ messages in thread
end of thread, other threads:[~2025-04-14 10:18 UTC | newest]
Thread overview: 20+ 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 <splarv@ya.ru>
2025-04-09 20:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-04-09 21:06 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-09 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-04-10 05:33 ` Olleg Samoylov <splarv@ya.ru>
2025-04-10 14:01 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-10 15:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-14 10:18 ` Wolfgang Walther <walther@technowledgy.de>
2025-04-09 21:21 ` Nico Williams <nico@cryptonector.com>
2025-04-09 21:43 ` Adrian Klaver <adrian.klaver@aklaver.com>
2025-04-10 15:48 ` Nico Williams <nico@cryptonector.com>
2025-04-10 15:59 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-11 03:18 ` Merlin Moncure <mmoncure@gmail.com>
2025-04-11 03:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-04-11 04:13 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-11 07:59 ` Dominique Devienne <ddevienne@gmail.com>
2025-04-11 23:31 ` Merlin Moncure <mmoncure@gmail.com>
2025-04-11 05:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-04-11 05:30 ` Nico Williams <nico@cryptonector.com>
2025-04-10 16:00 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox