Re: Documenting inlining SQL functions

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