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
