Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Make row compares robust during nbtree array scans.

  1. Documenting inlining SQL functions

    Paul A Jungwirth <pj@illuminatedcomputing.com> — 2025-07-07T03:11:55Z

    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

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

  2. Re: Documenting inlining SQL functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-07-18T19:57:12Z

    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




> Yours,
>
> --
> Paul              ~{:-)
> pj@illuminatedcomputing.com
>

  3. Re: Documenting inlining SQL functions

    Maciek Sakrejda <maciek@pganalyze.com> — 2025-07-19T01:09:31Z

    +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

  4. Documenting inlining SQL functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-19T02:44:58Z

    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.

  5. Re: Documenting inlining SQL functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-19T02:51:49Z

    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.

  6. Re: Documenting inlining SQL functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-19T03:08:14Z

    "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

  7. Re: Documenting inlining SQL functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-07-19T03:37:37Z

    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.

  8. Re: Documenting inlining SQL functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-07-19T04:48:01Z

    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



>
>                         regards, tom lane
>
>
>

  9. Re: Documenting inlining SQL functions

    Merlin Moncure <mmoncure@gmail.com> — 2025-07-20T03:19:32Z

    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