MIME-Version: 1.0
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at>
In-Reply-To: <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 9 Apr 2025 14:06:12 -0700
Message-ID: <CAKFQuwY51Us6LeBxtdL7MbHpJkxYpWtMOHJAhUoXXN2p5f1JfQ@mail.gmail.com>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Olleg Samoylov <splarv@ya.ru>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5668306325ed839"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwY51Us6LeBxtdL7MbHpJkxYpWtMOHJAhUoXXN2p5f1JfQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Apr 9, 2025 at 1:50=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.=
at>
wrote:

> 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.
>

So, the punishment for lying about the volatility of one's function is to
prohibit it from being inlined even in a case where had you been truthful
about the volatility it would have been inlined.

David J.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Wed, Apr 9, 2025 at 1:50=E2=80=AFPM Laurenz Albe &lt;<a=
 href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybertec.at</a>&gt; =
wrote:</span></div></div><div class=3D"gmail_quote gmail_quote_container"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">If you use EXPLAIN (VERBOS=
E), you will see that the function gets inlined in the fast case.<br>
<br>
That saves the overhead of a function call.<br>
<br>
The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE=
.<br>
</blockquote><div><br></div><div class=3D"gmail_default" style=3D"font-fami=
ly:arial,helvetica,sans-serif">So, the punishment for lying about the volat=
ility of one&#39;s function is to prohibit it from being inlined even in a =
case where had you been truthful about the volatility it would have been in=
lined.</div><div class=3D"gmail_default" style=3D"font-family:arial,helveti=
ca,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-family:=
arial,helvetica,sans-serif">David J.</div><div class=3D"gmail_default" styl=
e=3D"font-family:arial,helvetica,sans-serif"><br></div></div></div>

--000000000000f5668306325ed839--