38.11. Function Optimization Information
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 theCREATE FUNCTION command. Most important of these is the function'svolatility category (IMMUTABLE,STABLE, orVOLATILE); one should always be careful to specify this correctly when defining a function. The parallel safety property (PARALLEL UNSAFE,PARALLEL RESTRICTED, orPARALLEL 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 aplanner support function to an SQL-callable function (called itstarget 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 theSUPPORT clause when creating the target function.
The details of the API for planner support functions can be found in filesrc/include/nodes/supportnodes.h in thePostgreSQL 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 justn. This type of transformation can be performed by a planner support function, by having it implement theSupportRequestSimplify 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 ton. (But note that this is just an example; this particular optimization is not actually performed by standardPostgreSQL.) We make no guarantee thatPostgreSQL 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 returnboolean, it is often useful to estimate the fraction of rows that will be selected by aWHERE clause using that function. This can be done by a support function that implements theSupportRequestSelectivity 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 theSupportRequestCost 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 theSupportRequestRows request type.
For target functions that returnboolean, it may be possible to convert a function call appearing inWHERE 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 belossy; 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 theWHERE condition or not. To create such conditions, the support function must implement theSupportRequestIndexCondition request type.