Message-ID: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru>
Date: Wed, 9 Apr 2025 19:12:18 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Language: en-US
From: Olleg Samoylov <splarv@ya.ru>
Subject: Interesting case of IMMUTABLE significantly hurting performance
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/662101e2-5ca5-4bf5-9307-2519efde8690%40ya.ru>
Precedence: bulk

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