Re: Interesting case of IMMUTABLE significantly hurting performance

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