From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Nico Williams <nico@cryptonector.com>,
        Adrian Klaver <adrian.klaver@aklaver.com>,
        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: <CAHyXU0ypfKJ6LmEWJHKF63m5UEcZwB5gny3DWGQygvVeePM8mw@mail.gmail.com>
References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at> <Z/bk5E6pqIBWTF3j@ubby> <c857b827-3a17-4600-aa98-d0710216ccfe@aklaver.com> <Z/foWNmbdDOWH6/M@ubby> <CAKFQuwa-H6ZzST_dFNhviqQ8b4fPfLT+o3btfygrVOxQJhgWNQ@mail.gmail.com> <CAHyXU0ypfKJ6LmEWJHKF63m5UEcZwB5gny3DWGQygvVeePM8mw@mail.gmail.com>
Comments: In-reply-to Merlin Moncure <mmoncure@gmail.com>
	message dated "Thu, 10 Apr 2025 22:18:42 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <752811.1744343513.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 10 Apr 2025 23:51:53 -0400
Message-ID: <752812.1744343513@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/752812.1744343513%40sss.pgh.pa.us>
Precedence: bulk

Merlin Moncure <mmoncure@gmail.com> writes:
> I guess the real problems here are lack of feedback on a number of front=
s:
> *) the server knows the function is not immutable but lets you create it
> anyway, even though it can have negative downstream consequences

That's debatable I think.  If you know what you're doing, you're going
to be annoyed by warnings telling you that you don't.

> *) there is no way to discern inline vs non-inlined execution in explain

That's simply false.  Using the examples in this thread:

regression=3D# explain (verbose,analyze)
select formatted_num_immutable(i) from generate_series(1,1000000) i;
                                                                     QUERY=
 PLAN                                                                     =

--------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series i  (cost=3D0.00..262500.00 ro=
ws=3D1000000 width=3D32) (actual time=3D65.535..2444.956 rows=3D1000000.00=
 loops=3D1)
   Output: formatted_num_immutable((i)::bigint)
   Function Call: generate_series(1, 1000000)
   Buffers: temp read=3D1709 written=3D1709
 Planning Time: 0.086 ms
 Execution Time: 2481.218 ms
(6 rows)

regression=3D# explain (verbose,analyze)
select formatted_num_stable(i) from generate_series(1,1000000) i;
                                                                    QUERY =
PLAN                                                                    =

--------------------------------------------------------------------------=
------------------------------------------------------------------------
 Function Scan on pg_catalog.generate_series i  (cost=3D0.00..17500.00 row=
s=3D1000000 width=3D32) (actual time=3D65.615..478.780 rows=3D1000000.00 l=
oops=3D1)
   Output: ltrim(to_char((i)::bigint, '999 999 999 999 999 999 999 999'::t=
ext))
   Function Call: generate_series(1, 1000000)
   Buffers: temp read=3D1709 written=3D1709
 Planning Time: 0.091 ms
 Execution Time: 501.412 ms
(6 rows)

You can easily see that the second case was inlined, because you don't
see the SQL function anymore, rather its body.

> *) the planner is clearly not modelling function scan overhead give the
> relative costing discrepancies

That's also false; note the 15x difference in estimated cost above,
which is actually more than the real difference in runtime.  (I hasten
to add that I don't have a lot of faith in our function cost
estimates.  But the planner is quite well aware that a non-inlined SQL
function is likely to be expensive.)

			regards, tom lane