From c32a09320a6320bcc8a681829c03690556760ffe Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" 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 --- 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. - - Some basic facts can be supplied by declarative annotations provided in - the CREATE FUNCTION command. Most important of - these is the function's volatility - category (IMMUTABLE, STABLE, - or VOLATILE); one should always be careful to - specify this correctly when defining a function. - The parallel safety property (PARALLEL - UNSAFE, PARALLEL RESTRICTED, or - PARALLEL SAFE) 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. - + + Function Annotations + + function + annotations + - - It is also possible to attach a planner support - function to an SQL-callable function (called - its target function), 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. - + + Some basic facts can be supplied by declarative annotations provided in + the CREATE FUNCTION command. Most important of + these is the function's volatility + category (IMMUTABLE, STABLE, + or VOLATILE); one should always be careful to + specify this correctly when defining a function. + The parallel safety property (PARALLEL + UNSAFE, PARALLEL RESTRICTED, or + PARALLEL SAFE) 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. + + - - A planner support function must have the SQL signature + + Function Support Functions + + function + support functions + + + + It is also possible to attach a planner support + function to an SQL-callable function (called + its target function), 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. + + + + A planner support function must have the SQL signature supportfn(internal) returns internal - It is attached to its target function by specifying - the SUPPORT clause when creating the target function. - + It is attached to its target function by specifying + the SUPPORT clause when creating the target function. + - - The details of the API for planner support functions can be found in - file src/include/nodes/supportnodes.h in the - PostgreSQL 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. - + + The details of the API for planner support functions can be found in + file src/include/nodes/supportnodes.h in the + PostgreSQL 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. + - - Some function calls can be simplified during planning based on - properties specific to the function. For example, - int4mul(n, 1) could be simplified to - just n. This type of transformation can be - performed by a planner support function, by having it implement - the SupportRequestSimplify 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, n * 1 would also be simplified to - n. - (But note that this is just an example; this particular - optimization is not actually performed by - standard PostgreSQL.) - We make no guarantee that PostgreSQL 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. - + + Some function calls can be simplified during planning based on + properties specific to the function. For example, + int4mul(n, 1) could be simplified to + just n. This type of transformation can be + performed by a planner support function, by having it implement + the SupportRequestSimplify 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, n * 1 would also be simplified to + n. + (But note that this is just an example; this particular + optimization is not actually performed by + standard PostgreSQL.) + We make no guarantee that PostgreSQL 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. + - - For target functions that return boolean, it is often useful to estimate - the fraction of rows that will be selected by a WHERE clause using that - function. This can be done by a support function that implements - the SupportRequestSelectivity request type. - + + For target functions that return boolean, it is often useful to estimate + the fraction of rows that will be selected by a WHERE clause using that + function. This can be done by a support function that implements + the SupportRequestSelectivity request type. + - - 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 SupportRequestCost request type. - + + 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 SupportRequestCost request type. + - - 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 SupportRequestRows request type. - + + 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 SupportRequestRows request type. + - - For target functions that return boolean, it may be possible to - convert a function call appearing in WHERE 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 lossy; 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 WHERE condition or not. - To create such conditions, the support function must implement - the SupportRequestIndexCondition request type. - + + For target functions that return boolean, it may be possible to + convert a function call appearing in WHERE 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 lossy; 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 WHERE condition or not. + To create such conditions, the support function must implement + the SupportRequestIndexCondition request type. + + -- 2.45.0