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]https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions. 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]https://github.com/postgres/postgres/blob/bd3f59fdb71721921bb0aca7e16d483f72e95779/src/include/nodes/supportnodes.h#L64, and I have a patch to do something similar for set-returning SQL
functions[3]https://commitfest.postgresql.org/patch/5083/. I gave a talk at Postgres Extensions Day in Montreal that ties all this together,[4]https://github.com/pjungwir/inlining-postgres-functions
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
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
Attachments:
v1-0001-Split-Function-Optimization-section-into-parts.patchtext/x-patch; charset=UTF-8; name=v1-0001-Split-Function-Optimization-section-into-parts.patchDownload
From c32a09320a6320bcc8a681829c03690556760ffe Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 1 Jul 2025 20:10:18 -0700
Subject: [PATCH v1 1/2] Split Function Optimization section into parts
We already separate declarative function annotations from dynamic
information provided by support functions. We can make these
sub-sections for better clarity and linkability. And this structure will
help keep things accessible when we introduce a new section about
function inlining.
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
doc/src/sgml/xfunc.sgml | 200 ++++++++++++++++++++++------------------
1 file changed, 108 insertions(+), 92 deletions(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 2d81afce8cb..74740b4e345 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -4097,107 +4097,123 @@ extern PgStat_Kind pgstat_register_kind(PgStat_Kind kind,
knowledge that helps the planner optimize function calls.
</para>
- <para>
- Some basic facts can be supplied by declarative annotations provided in
- the <link linkend="sql-createfunction"><command>CREATE FUNCTION</command></link> command. Most important of
- these is the function's <link linkend="xfunc-volatility">volatility
- category</link> (<literal>IMMUTABLE</literal>, <literal>STABLE</literal>,
- or <literal>VOLATILE</literal>); one should always be careful to
- specify this correctly when defining a function.
- The parallel safety property (<literal>PARALLEL
- UNSAFE</literal>, <literal>PARALLEL RESTRICTED</literal>, or
- <literal>PARALLEL SAFE</literal>) must also be specified if you hope
- to use the function in parallelized queries.
- It can also be useful to specify the function's estimated execution
- cost, and/or the number of rows a set-returning function is estimated
- to return. However, the declarative way of specifying those two
- facts only allows specifying a constant value, which is often
- inadequate.
- </para>
+ <sect2 id="xfunc-annotations">
+ <title>Function Annotations</title>
+ <indexterm>
+ <primary>function</primary>
+ <secondary>annotations</secondary>
+ </indexterm>
- <para>
- It is also possible to attach a <firstterm>planner support
- function</firstterm> to an SQL-callable function (called
- its <firstterm>target function</firstterm>), and thereby provide
- knowledge about the target function that is too complex to be
- represented declaratively. Planner support functions have to be
- written in C (although their target functions might not be), so this is
- an advanced feature that relatively few people will use.
- </para>
+ <para>
+ Some basic facts can be supplied by declarative annotations provided in
+ the <link linkend="sql-createfunction"><command>CREATE FUNCTION</command></link> command. Most important of
+ these is the function's <link linkend="xfunc-volatility">volatility
+ category</link> (<literal>IMMUTABLE</literal>, <literal>STABLE</literal>,
+ or <literal>VOLATILE</literal>); one should always be careful to
+ specify this correctly when defining a function.
+ The parallel safety property (<literal>PARALLEL
+ UNSAFE</literal>, <literal>PARALLEL RESTRICTED</literal>, or
+ <literal>PARALLEL SAFE</literal>) must also be specified if you hope
+ to use the function in parallelized queries.
+ It can also be useful to specify the function's estimated execution
+ cost, and/or the number of rows a set-returning function is estimated
+ to return. However, the declarative way of specifying those two
+ facts only allows specifying a constant value, which is often
+ inadequate.
+ </para>
+ </sect2>
- <para>
- A planner support function must have the SQL signature
+ <sect2 id="xfunc-support-functions">
+ <title>Function Support Functions</title>
+ <indexterm>
+ <primary>function</primary>
+ <secondary>support functions</secondary>
+ </indexterm>
+
+ <para>
+ It is also possible to attach a <firstterm>planner support
+ function</firstterm> to an SQL-callable function (called
+ its <firstterm>target function</firstterm>), and thereby provide
+ knowledge about the target function that is too complex to be
+ represented declaratively. Planner support functions have to be
+ written in C (although their target functions might not be), so this is
+ an advanced feature that relatively few people will use.
+ </para>
+
+ <para>
+ A planner support function must have the SQL signature
<programlisting>
supportfn(internal) returns internal
</programlisting>
- It is attached to its target function by specifying
- the <literal>SUPPORT</literal> clause when creating the target function.
- </para>
+ It is attached to its target function by specifying
+ the <literal>SUPPORT</literal> clause when creating the target function.
+ </para>
- <para>
- The details of the API for planner support functions can be found in
- file <filename>src/include/nodes/supportnodes.h</filename> in the
- <productname>PostgreSQL</productname> source code. Here we provide
- just an overview of what planner support functions can do.
- The set of possible requests to a support function is extensible,
- so more things might be possible in future versions.
- </para>
+ <para>
+ The details of the API for planner support functions can be found in
+ file <filename>src/include/nodes/supportnodes.h</filename> in the
+ <productname>PostgreSQL</productname> source code. Here we provide
+ just an overview of what planner support functions can do.
+ The set of possible requests to a support function is extensible,
+ so more things might be possible in future versions.
+ </para>
- <para>
- Some function calls can be simplified during planning based on
- properties specific to the function. For example,
- <literal>int4mul(n, 1)</literal> could be simplified to
- just <literal>n</literal>. This type of transformation can be
- performed by a planner support function, by having it implement
- the <literal>SupportRequestSimplify</literal> request type.
- The support function will be called for each instance of its target
- function found in a query parse tree. If it finds that the particular
- call can be simplified into some other form, it can build and return a
- parse tree representing that expression. This will automatically work
- for operators based on the function, too — in the example just
- given, <literal>n * 1</literal> would also be simplified to
- <literal>n</literal>.
- (But note that this is just an example; this particular
- optimization is not actually performed by
- standard <productname>PostgreSQL</productname>.)
- We make no guarantee that <productname>PostgreSQL</productname> will
- never call the target function in cases that the support function could
- simplify. Ensure rigorous equivalence between the simplified
- expression and an actual execution of the target function.
- </para>
+ <para>
+ Some function calls can be simplified during planning based on
+ properties specific to the function. For example,
+ <literal>int4mul(n, 1)</literal> could be simplified to
+ just <literal>n</literal>. This type of transformation can be
+ performed by a planner support function, by having it implement
+ the <literal>SupportRequestSimplify</literal> request type.
+ The support function will be called for each instance of its target
+ function found in a query parse tree. If it finds that the particular
+ call can be simplified into some other form, it can build and return a
+ parse tree representing that expression. This will automatically work
+ for operators based on the function, too — in the example just
+ given, <literal>n * 1</literal> would also be simplified to
+ <literal>n</literal>.
+ (But note that this is just an example; this particular
+ optimization is not actually performed by
+ standard <productname>PostgreSQL</productname>.)
+ We make no guarantee that <productname>PostgreSQL</productname> will
+ never call the target function in cases that the support function could
+ simplify. Ensure rigorous equivalence between the simplified
+ expression and an actual execution of the target function.
+ </para>
- <para>
- For target functions that return <type>boolean</type>, it is often useful to estimate
- the fraction of rows that will be selected by a <literal>WHERE</literal> clause using that
- function. This can be done by a support function that implements
- the <literal>SupportRequestSelectivity</literal> request type.
- </para>
+ <para>
+ For target functions that return <type>boolean</type>, it is often useful to estimate
+ the fraction of rows that will be selected by a <literal>WHERE</literal> clause using that
+ function. This can be done by a support function that implements
+ the <literal>SupportRequestSelectivity</literal> request type.
+ </para>
- <para>
- If the target function's run time is highly dependent on its inputs,
- it may be useful to provide a non-constant cost estimate for it.
- This can be done by a support function that implements
- the <literal>SupportRequestCost</literal> request type.
- </para>
+ <para>
+ If the target function's run time is highly dependent on its inputs,
+ it may be useful to provide a non-constant cost estimate for it.
+ This can be done by a support function that implements
+ the <literal>SupportRequestCost</literal> request type.
+ </para>
- <para>
- For target functions that return sets, it is often useful to provide
- a non-constant estimate for the number of rows that will be returned.
- This can be done by a support function that implements
- the <literal>SupportRequestRows</literal> request type.
- </para>
+ <para>
+ For target functions that return sets, it is often useful to provide
+ a non-constant estimate for the number of rows that will be returned.
+ This can be done by a support function that implements
+ the <literal>SupportRequestRows</literal> request type.
+ </para>
- <para>
- For target functions that return <type>boolean</type>, it may be possible to
- convert a function call appearing in <literal>WHERE</literal> into an indexable operator
- clause or clauses. The converted clauses might be exactly equivalent
- to the function's condition, or they could be somewhat weaker (that is,
- they might accept some values that the function condition does not).
- In the latter case the index condition is said to
- be <firstterm>lossy</firstterm>; it can still be used to scan an index,
- but the function call will have to be executed for each row returned by
- the index to see if it really passes the <literal>WHERE</literal> condition or not.
- To create such conditions, the support function must implement
- the <literal>SupportRequestIndexCondition</literal> request type.
- </para>
+ <para>
+ For target functions that return <type>boolean</type>, it may be possible to
+ convert a function call appearing in <literal>WHERE</literal> into an indexable operator
+ clause or clauses. The converted clauses might be exactly equivalent
+ to the function's condition, or they could be somewhat weaker (that is,
+ they might accept some values that the function condition does not).
+ In the latter case the index condition is said to
+ be <firstterm>lossy</firstterm>; it can still be used to scan an index,
+ but the function call will have to be executed for each row returned by
+ the index to see if it really passes the <literal>WHERE</literal> condition or not.
+ To create such conditions, the support function must implement
+ the <literal>SupportRequestIndexCondition</literal> request type.
+ </para>
+ </sect2>
</sect1>
--
2.45.0
v1-0002-Document-inlining-SQL-language-functions.patchtext/x-patch; charset=UTF-8; name=v1-0002-Document-inlining-SQL-language-functions.patchDownload
From 79d95ce36d9712110c7c84d2e964e1a7b3360d64 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 1 Jul 2025 20:15:55 -0700
Subject: [PATCH v1 2/2] Document inlining SQL-language functions
Both single-result functions and set-returning functions can be inlined
(since the 9.x days), but this has never been documented outside of a
wiki page: https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
This useful optimization seems largely unknown, even to many books about
Postgres query optimization, so we should include it in our
documentation.
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
doc/src/sgml/xfunc.sgml | 52 ++++++++++++++++++++++++++++++++++++++++-
1 file changed, 51 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
index 74740b4e345..14a632b72d7 100644
--- a/doc/src/sgml/xfunc.sgml
+++ b/doc/src/sgml/xfunc.sgml
@@ -4097,6 +4097,56 @@ extern PgStat_Kind pgstat_register_kind(PgStat_Kind kind,
knowledge that helps the planner optimize function calls.
</para>
+ <sect2 id="xfunc-inlining">
+ <title>Function Inlining</title>
+ <indexterm>
+ <primary>function</primary>
+ <secondary>inlining</secondary>
+ </indexterm>
+
+ <para>
+ Even with no extra information, the planner may be able to inline the function
+ into the calling query. The rules vary depending on whether the function returns
+ a single result or is a <link linkend="queries-tablefunctions">set-returning function</link>.
+ But in all cases the function must be implemented in SQL (not PL/pgSQL).
+ It must not be <literal>SECURITY DEFINER</literal>.
+ And if an extension has hooked function entry/exit,
+ then inlining must be skipped.
+ </para>
+
+ <para>
+ For single-result functions, the function body must be a single
+ <literal>SELECT <replaceable>expression</replaceable></literal> statement
+ returning a single column.
+ It must not return a <literal>RECORD</literal>.
+ It must return a type that matches the function declaration.
+ It cannot recurse. It must not include CTEs, a <literal>FROM</literal> clause,
+ references to tables or table-like objects, <literal>DISTINCT</literal>,
+ <literal>GROUP BY</literal>, <literal>HAVING</literal>,
+ aggregate functions, window functions,
+ <literal>ORDER BY</literal>, <literal>LIMIT</literal>, <literal>OFFSET</literal>,
+ <literal>UNION</literal>, <literal>INTERSECT</literal>, or <literal>EXCEPT</literal>.
+ Its arguments, if used more than once in its body, cannot include <literal>VOLATILE</literal> functions.
+ 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>).
+ If the original function was <literal>STRICT</literal>, then any called functions must be <literal>STRICT</literal>.
+ For more control, see <link linkend="xfunc-support-request-simplify"><literal>SupportRequestSimplify</literal></link>.
+ </para>
+
+ <para>
+ For set-returning functions, inlining lets the planner merge the query into the
+ outer query, enabling optimizations like qual pushdown, constant folding, etc.
+ The function body must be a single <literal>SELECT</literal> statement.
+ It must be declared <literal>STABLE</literal> or <literal>IMMUTABLE</literal>.
+ It must not be <literal>STRICT</literal>.
+ In addition its arguments may not include volatile function calls or
+ sub-queries. The function must be called from the <literal>FROM</literal> clause,
+ not the <literal>SELECT</literal> clause, nor with <literal>ORDINALITY</literal> or
+ <literal>ROWS FROM</literal>.
+ </para>
+ </sect2>
+
<sect2 id="xfunc-annotations">
<title>Function Annotations</title>
<indexterm>
@@ -4158,7 +4208,7 @@ supportfn(internal) returns internal
so more things might be possible in future versions.
</para>
- <para>
+ <para id="xfunc-support-request-simplify">
Some function calls can be simplified during planning based on
properties specific to the function. For example,
<literal>int4mul(n, 1)</literal> could be simplified to
--
2.45.0
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
Show quoted text
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
+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
Show quoted text
regards, tom lane
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