MIME-Version: 1.0
In-Reply-To: <379a1e13-c775-4fb5-a444-b41308996cc4@ya.ru>
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at>
 <CAKFQuwY51Us6LeBxtdL7MbHpJkxYpWtMOHJAhUoXXN2p5f1JfQ@mail.gmail.com>
 <373227.1744236488@sss.pgh.pa.us> <379a1e13-c775-4fb5-a444-b41308996cc4@ya.ru>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 10 Apr 2025 07:01:18 -0700
Message-ID: <CAKFQuwZ_9nEHR7bfnhK8XqCiN3WkX5O0i7Rbqn1w68kFC+f67Q@mail.gmail.com>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
To: Olleg Samoylov <splarv@ya.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001e1d6d06326d0535"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZ_9nEHR7bfnhK8XqCiN3WkX5O0i7Rbqn1w68kFC%2Bf67Q%40mail.gmail.com>
Precedence: bulk

--0000000000001e1d6d06326d0535
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, April 9, 2025, Olleg Samoylov <splarv@ya.ru> wrote:
>
> On 10.04.2025 01:08, Tom Lane wrote:
>
>> Yeah.  The assumption is that you had a reason for marking the
>> function IMMUTABLE and you want the planner to treat it that way
>> even if it isn't really.  (There are valid use-cases for that, for
>> instance if you want calls to the function to be constant-folded.)
>>                         regards, tom lane
>>
>
> Well, to_char(bigint, text) indeed not immutable, because in some pattern
> it uses get information from locale. For instance,'SLDG' patterns. But in
> case of
>
> 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'));
>
> to_char do not use locale information in this pattern. So it is correct
> conclude that to_char is immutable with this pattern and
> formatted_num_immutable too. I did not lie to the planner.
>
> So this is looked "strange", immutable function marked as immutable
> function can not be inlined, but exactly the same function marked as
> volatile do.
>
>
Yeah, the inlining is an optimization, and while it seems like it could
perform more tests or maybe make slightly different/more adjustments, it
really isn=E2=80=99t worth the development effort or runtime cost to do so.=
  Make
your function volatility match the most volatile function you internally
call - constant input arguments don=E2=80=99t change this.

There is no reason to perform number formatting immutably - function call
results involving table data are not memoized.

David J.

--0000000000001e1d6d06326d0535
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, April 9, 2025, Olleg Samoylov &lt;<a href=3D"mailto:splarv@ya=
.ru">splarv@ya.ru</a>&gt; wrote:<blockquote class=3D"gmail_quote" style=3D"=
margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
On 10.04.2025 01:08, Tom Lane wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
Yeah.=C2=A0 The assumption is that you had a reason for marking the<br>
function IMMUTABLE and you want the planner to treat it that way<br>
even if it isn&#39;t really.=C2=A0 (There are valid use-cases for that, for=
<br>
instance if you want calls to the function to be constant-folded.)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote>
<br>
Well, to_char(bigint, text) indeed not immutable, because in some pattern i=
t uses get information from locale. For instance,&#39;SLDG&#39; patterns. B=
ut in case of<br>
<br>
CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summ<wbr>a bigint)<br>
=C2=A0RETURNS text<br>
=C2=A0LANGUAGE sql<br>
=C2=A0IMMUTABLE STRICT<br>
RETURN ltrim(to_char(p_summa, &#39;999 999 999 999 999 999 999 999&#39;));<=
br>
<br>
to_char do not use locale information in this pattern. So it is correct con=
clude that to_char is immutable with this pattern and formatted_num_immutab=
le too. I did not lie to the planner.<br>
<br>
So this is looked &quot;strange&quot;, immutable function marked as immutab=
le function can not be inlined, but exactly the same function marked as vol=
atile do.<br><br>
</blockquote><div><br></div><div>Yeah, the inlining is an optimization, and=
 while it seems like it could perform more tests or maybe make slightly dif=
ferent/more adjustments, it really isn=E2=80=99t worth the development effo=
rt or runtime cost to do so.=C2=A0 Make your function volatility match the =
most volatile function you internally call - constant input arguments don=
=E2=80=99t change this.</div><div><br></div><div>There is no reason to perf=
orm number formatting immutably - function call results involving table dat=
a are not memoized.</div><div><br></div><div>David J.</div><div><br></div>

--0000000000001e1d6d06326d0535--