Message-ID: <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Olleg Samoylov <splarv@ya.ru>, "pgsql-generallists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Wed, 09 Apr 2025 22:50:00 +0200
In-Reply-To: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru>
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-04-09 at 19:12 +0300, Olleg Samoylov wrote:
> PostgreSQL 17.4
>=20
> 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'));
>=20
> 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'));
>=20
> explain analyze select formatted_num_immutable(i) from=20
> generate_series(1,1000000) as i(i);
>                                                                QUERY PLAN
> -------------------------------------------------------------------------=
-------------------------------------------------------------
>   Function Scan on generate_series i  (cost=3D0.00..262500.00 rows=3D1000=
000=20
> width=3D32) (actual time=3D56.892..1548.656 rows=3D1000000 loops=3D1)
>   Planning Time: 0.039 ms
>   JIT:
>     Functions: 4
>     Options: Inlining false, Optimization false, Expressions true,=20
> Deforming true
>     Timing: Generation 0.184 ms (Deform 0.040 ms), Inlining 0.000 ms,=20
> Optimization 0.115 ms, Emission 1.570 ms, Total 1.869 ms
>   Execution Time: 1587.741 ms
> (7 rows)
>=20
> explain analyze select formatted_num_stable(i) from=20
> generate_series(1,1000000) as i(i);
>                                                               QUERY PLAN
> -------------------------------------------------------------------------=
-----------------------------------------------------------
>   Function Scan on generate_series i  (cost=3D0.00..17500.00 rows=3D10000=
00=20
> width=3D32) (actual time=3D54.993..573.333 rows=3D1000000 loops=3D1)
>   Planning Time: 0.056 ms
>   Execution Time: 598.190 ms
> (3 rows)
>=20
> First interesting thing is immutable variant has cost in 15 time more,=
=20
> then stable. That's why jit compilation is tuned on. Second, immutable=
=20
> function is working much longer (3 times). And jit is not the reason.
>=20
> =3D> set jit=3Doff;
> SET
> =3D> explain analyze select formatted_num_immutable(i) from=20
> generate_series(1,1000000) as i(i);
>                                                                QUERY PLAN
> -------------------------------------------------------------------------=
-------------------------------------------------------------
>   Function Scan on generate_series i  (cost=3D0.00..262500.00 rows=3D1000=
000=20
> width=3D32) (actual time=3D54.888..1537.602 rows=3D1000000 loops=3D1)
>   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 i=
n 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