public inbox for [email protected]
help / color / mirror / Atom feedFrom: Olleg Samoylov <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Interesting case of IMMUTABLE significantly hurting performance
Date: Wed, 9 Apr 2025 19:12:18 +0300
Message-ID: <[email protected]> (raw)
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
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]
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