37.11. Function Optimization Information

37.11. Function Optimization Information
Prev	Up	Chapter 37. Extending SQL	Home	Next

+ By default, a function is just a “black box” that the + database system knows very little about the behavior of. However, + that means that queries using the function may be executed much less + efficiently than they could be. It is possible to supply additional + 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. +

+ It is also possible to attach a planner support + function to a 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. +

+ 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. +

+ 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. +

+ 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. +

Prev	Up	Next
37.10. C-Language Functions	Home	37.12. User-Defined Aggregates