Horton

Documenting inlining SQL functions

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-07-07T03:11:55Z
Lists: pgsql-hackers

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.

Attachments

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