From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, Olleg Samoylov <splarv@ya.ru>,
        "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
In-reply-to: <CAKFQuwY51Us6LeBxtdL7MbHpJkxYpWtMOHJAhUoXXN2p5f1JfQ@mail.gmail.com>
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at> <CAKFQuwY51Us6LeBxtdL7MbHpJkxYpWtMOHJAhUoXXN2p5f1JfQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 09 Apr 2025 14:06:12 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <373226.1744236488.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 09 Apr 2025 18:08:08 -0400
Message-ID: <373227.1744236488@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/373227.1744236488%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Apr 9, 2025 at 1:50 PM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
>> 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.

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

So we don't inline the function --- if we did, the merely-stable
contained expression would be exposed and then treated as STABLE.
But that comes at a pretty substantial cost, since the
SQL-language-function executor isn't exactly free.

If you err in the other direction, you don't get slapped on the
wrist that way.  We're willing to inline VOLATILE functions,
for instance, whether or not the contained expression is volatile.
Similarly for STRICT, and I think parallel safety as well.
So my own habit when writing a SQL function that I wish to be
inlined is to leave off all those markings.  They won't matter
if the function is successfully inlined, and they might get in
the way of that happening.

			regards, tom lane