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
