From 79d95ce36d9712110c7c84d2e964e1a7b3360d64 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" 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 --- 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. + + Function Inlining + + function + inlining + + + + 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 set-returning function. + But in all cases the function must be implemented in SQL (not PL/pgSQL). + It must not be SECURITY DEFINER. + And if an extension has hooked function entry/exit, + then inlining must be skipped. + + + + For single-result functions, the function body must be a single + SELECT expression statement + returning a single column. + It must not return a RECORD. + It must return a type that matches the function declaration. + It cannot recurse. It must not include CTEs, a FROM clause, + references to tables or table-like objects, DISTINCT, + GROUP BY, HAVING, + aggregate functions, window functions, + ORDER BY, LIMIT, OFFSET, + UNION, INTERSECT, or EXCEPT. + Its arguments, if used more than once in its body, cannot include VOLATILE functions. + The hypothetical inlined expression must be no more volatile than the original function + (so an IMMUTABLE function must inline to an IMMUTABLE + expression, and a STABLE function must inline to STABLE or IMMUTABLE). + If the original function was STRICT, then any called functions must be STRICT. + For more control, see SupportRequestSimplify. + + + + 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 SELECT statement. + It must be declared STABLE or IMMUTABLE. + It must not be STRICT. + In addition its arguments may not include volatile function calls or + sub-queries. The function must be called from the FROM clause, + not the SELECT clause, nor with ORDINALITY or + ROWS FROM. + + + Function Annotations @@ -4158,7 +4208,7 @@ supportfn(internal) returns internal so more things might be possible in future versions. - + Some function calls can be simplified during planning based on properties specific to the function. For example, int4mul(n, 1) could be simplified to -- 2.45.0