public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Olleg Samoylov <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
Date: Wed, 09 Apr 2025 22:50:00 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[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
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox