Documenting inlining SQL functions

Hi

po 7. 7. 2025 v 5:12 odesílatel Paul Jungwirth <pj@illuminatedcomputing.com>
napsal:

Hi Hackers,

Here are some documentation patches about inlining SQL-language functions.
Postgres has been able to
inline both scalar and set-returning functions since the 9.x days (as long
as they are LANGUAGE SQL
and meet a bunch of other conditions). But this was never documented
outside of a wiki page[1]. This
is a very significant optimization, especially for SRFs with qual
pushdown, but it is largely
unknown. I recently checked five books on advanced Postgres usage, some
specifically on query
optimization, and none of them mentioned this feature. I think we should
talk about it.

Putting this in our docs also gives us a useful reference point for some
support requests that do
similar inlining, but with more user control. We already have a support
request to let users inline
single-result SQL functions[2], and I have a patch to do something similar
for set-returning SQL
functions[3]. I gave a talk at Postgres Extensions Day in Montreal that
ties all this together,[4]
and our docs could do something similar.

The first patch just adds <sect2> elements to break up the Function
Optimization section into two
sub-sections: one covering declarative annotations and another covering
support functions. No
rewriting is needed.

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and
set-returning. Since this happens automatically, it makes a nice
progression with the (easy)
declarative annotations and the (hard) support functions.

I wonder if we should have some tests about this behavior also? I'm happy
to add those, either as
part of this commitfest entry or something separate.

[1] https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
[2]

https://github.com/postgres/postgres/blob/bd3f59fdb71721921bb0aca7e16d483f72e95779/src/include/nodes/supportnodes.h#L64
[3] https://commitfest.postgresql.org/patch/5083/
[4] https://github.com/pjungwir/inlining-postgres-functions

I am checking this patch. The new content is very necessary.

I think the related page is well readable after patching.

This patch can be enhanced by some examples, and with a description how to
detect if a function was inlined or not.

Reards

Pavel

+1, I think this is very nice to have. I knew about inlining, but not
the details of the mechanism, so it's nice to see when I should expect
it to happen.

I have some minor wording feedback:

+ And if an extension has hooked function entry/exit,
+ then inlining must be skipped.

Maybe "And if any extension has hooked function entry/exit, then
inlining will be skipped." for clarity?

+ It must return a type that matches the function declaration.

I think this could also be clearer: maybe "It must return a type that
matches the function declaration exactly (without an implicit cast)"?
Maybe that's overkill, but when reading the original version, it
wasn't clear to me why a function would return a type that does not
match the declaration: wouldn't that be an error? Then I figured out
you're probably talking about implicit casts.

+ references to tables or table-like objects, <literal>DISTINCT</literal>,

Maybe we should say "...to tables, views, and other table-like
objects" or otherwise expand on this. In ddl.sgml, we have "Allows
<command>SELECT</command> from any column, or specific column(s), of a
table, view, materialized view, or other table-like object."

+ The hypothetical inlined expression must be no more volatile than
the original function
+ (so an <literal>IMMUTABLE</literal> function must inline to an
<literal>IMMUTABLE</literal>
+ expression, and a <literal>STABLE</literal> function must inline to
<literal>STABLE</literal> or <literal>IMMUTABLE</literal>).

This makes sense, but I don't know how to reason about the volatility
of an inlined expression. I don't think most users will be able to
either. Is there a way to clarify that?

Otherwise, this looks great.

Thanks,
Maciek

On Friday, July 18, 2025, Maciek Sakrejda <maciek@pganalyze.com> wrote:

+1, I think this is very nice to have. I knew about inlining, but not
the details of the mechanism, so it's nice to see when I should expect
it to happen.

+1 as well.

I have some minor wording feedback:

+ And if an extension has hooked function entry/exit,
+ then inlining must be skipped.

Maybe "And if any extension has hooked function entry/exit, then
inlining will be skipped." for clarity?

Would like to avoid passive voice and not a huge fan of the location
either. I’d be inclined to add it as a note/caution at the end of the
sect2. Though I’m unsure exactly how these hooks work or what is
possible. I’m guessing something like:

Caution: PostgreSQL provides query-level hooks at function entry and exit
that extension code can tap into. When tapped, the planner will not inline
functions while planning queries.

+ It must return a type that matches the function declaration.

I think this could also be clearer: maybe "It must return a type that
matches the function declaration exactly (without an implicit cast)"?
Maybe that's overkill, but when reading the original version, it
wasn't clear to me why a function would return a type that does not
match the declaration: wouldn't that be an error? Then I figured out
you're probably talking about implicit casts.

Doesn’t this requirement preclude “record”, as well as polymorphic
functions?

As for the somewhat lengthy list itself:

If we reject “group by” then rejecting having and aggregate functions is
redundant. Maybe focus less on keywords and just say that concepts are not
allowed. Union/intersect/except already violate “select expression” but
could at least be summed up a SQL SET operators (with a link) if we want
some mention of the feature.

I would suggest two sub-parts for each of the restrictions/requirements:

A function, to qualify for inlining must be defined…e.g., non-strict
Additionally, in the query using the function, (these
limitations/requirements apply)…e.g., values for arguments cannot be
volatile functions

Might be good to put a leading paragraph declaring everything in common to
both (divided into definition and caller), then just note the differences
in the single/multi result specific paragraphs. Should be easier to inject
and recall that two long and only slightly different lists of rules.

+ The hypothetical inlined expression must be no more volatile than

the original function
+ (so an <literal>IMMUTABLE</literal> function must inline to an
<literal>IMMUTABLE</literal>
+ expression, and a <literal>STABLE</literal> function must inline to
<literal>STABLE</literal> or <literal>IMMUTABLE</literal>).

This makes sense, but I don't know how to reason about the volatility
of an inlined expression. I don't think most users will be able to
either. Is there a way to clarify that?

Kinda feel that such clarification, which is more general than just
inlining, should be explained elsewhere if needed (not sure where).

David J.

On Sunday, July 6, 2025, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and set-returning. Since this happens
automatically, it makes a nice progression with the (easy) declarative
annotations and the (hard) support functions.

The fact that attaching a set clause to the function definition (i.e.,
proconfig) prevents inlining is missing from this description.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, July 6, 2025, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and set-returning. Since this happens
automatically, it makes a nice progression with the (easy) declarative
annotations and the (hard) support functions.

The fact that attaching a set clause to the function definition (i.e.,
proconfig) prevents inlining is missing from this description.

TBH, I think trying to document this behavior at this level of detail
will be a disaster. Our track record for keeping documentation in
sync with code is awful, and what exactly will make this area better
than average? Even if this text is 100% accurate today, I'll bet a
good lunch that it will be wrong in two or three releases.

Our attitude for questions at the level of detail that this is
trying to cover has mostly been "someone who cares can go read
the code". I grant that that's not a great answer, but it's
largely stood the test of time.

I think it's reasonable to document the fact that we can do SQL
function inlining in some cases, but not to try to specify which
cases those are in exhaustive detail.

regards, tom lane

On Friday, July 18, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, July 6, 2025, Paul Jungwirth <pj@illuminatedcomputing.com>

wrote:

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and set-returning. Since this happens
automatically, it makes a nice progression with the (easy) declarative
annotations and the (hard) support functions.

The fact that attaching a set clause to the function definition (i.e.,
proconfig) prevents inlining is missing from this description.

TBH, I think trying to document this behavior at this level of detail
will be a disaster. Our track record for keeping documentation in
sync with code is awful, and what exactly will make this area better
than average? Even if this text is 100% accurate today, I'll bet a
good lunch that it will be wrong in two or three releases.

You would have said the same three releases ago and been wrong. Seems like
you’d have to go back like 10 years when we figured out we messed up “case”
and 5 beyond that when we added the hooks.

Our attitude for questions at the level of detail that this is

trying to cover has mostly been "someone who cares can go read
the code". I grant that that's not a great answer, but it's
largely stood the test of time.

Then maybe we should use this patch to update the code comment (the part
about hooks is 15 years old but missed updating the code comment) Then,
make this patch: see backend/optimizer/util/clauses.c#inline_function for a
description of the algorithm.

But I’d still be inclined to have a user-facing document for this, add a
note to the C code comment that it exists, and at least hopefully either
both C and Docs are updated or neither are.

David J.

Hi

so 19. 7. 2025 v 5:08 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, July 6, 2025, Paul Jungwirth <pj@illuminatedcomputing.com>

wrote:

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and set-returning. Since this happens
automatically, it makes a nice progression with the (easy) declarative
annotations and the (hard) support functions.

The fact that attaching a set clause to the function definition (i.e.,
proconfig) prevents inlining is missing from this description.

TBH, I think trying to document this behavior at this level of detail
will be a disaster. Our track record for keeping documentation in
sync with code is awful, and what exactly will make this area better
than average? Even if this text is 100% accurate today, I'll bet a
good lunch that it will be wrong in two or three releases.

Our attitude for questions at the level of detail that this is
trying to cover has mostly been "someone who cares can go read
the code". I grant that that's not a great answer, but it's
largely stood the test of time.

I think it's reasonable to document the fact that we can do SQL
function inlining in some cases, but not to try to specify which
cases those are in exhaustive detail.

I agree so this can be fragile. But inlining has too high an impact on
performance so I don't think a description somewhere on wiki is enough.

Now, the SQL functions can use plan cache too, so the overhead of execution
of non-inlined SQL functions can be less, but still it is very important
from performance perspective and often a source of performance issue. There
can be notes, so described rules can be changed in the time, but I think
the described behaviour is mostly very stable.

Regards

Pavel

On Fri, Jul 18, 2025 at 9:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sunday, July 6, 2025, Paul Jungwirth <pj@illuminatedcomputing.com>

wrote:

The second patch adds a new <sect2> explaining how we inline SQL
functions: both single-result and set-returning. Since this happens
automatically, it makes a nice progression with the (easy) declarative
annotations and the (hard) support functions.

The fact that attaching a set clause to the function definition (i.e.,
proconfig) prevents inlining is missing from this description.

TBH, I think trying to document this behavior at this level of detail
will be a disaster. Our track record for keeping documentation in
sync with code is awful, and what exactly will make this area better
than average? Even if this text is 100% accurate today, I'll bet a
good lunch that it will be wrong in two or three releases.

Yeah.
I'm still contemplating my goofy answer to the inlining gripe on a recent
thread..oh well :).

Maybe it might be more helpful and future proof to, rather than bullet
point list the mechanics that prevent inlining, provide some lightly sanded
down advice on how certain mechanics can prevent inlining along with some
advice on how to detect if a particular function is inlined based on
explain output.

merlin