Re: Documenting inlining SQL functions

Hi

po 7. 7. 2025 v 5:12 odesílatel Paul Jungwirth <pj(at)illuminatedcomputing(dot)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(at)illuminatedcomputing(dot)com
>