Postgresql 中文操作指南

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.

某些基本事实可以通过 CREATE FUNCTION 命令中提供的声明性注释提供。其中最重要的注释是函数 volatility category （ IMMUTABLE ， STABLE 或 VOLATILE ）；在定义函数时，应始终小心地正确指定此注释。如果希望在并行查询中使用该函数，还必须指定并行安全性属性（ PARALLEL UNSAFE ， PARALLEL RESTRICTED 或 PARALLEL SAFE ）。指定函数的估计执行成本，和/或由返回集合函数返回的行数的估计值，也非常有用。然而，声明性指定这两个事实的方法只能指定常量值，这常常是不够的。

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.

也可以将 planner support function 附加到 SQL 可调用函数（称为其 target function），并由此提供关于目标函数的知识，而这些知识太复杂，无法通过声明性方式表示。规划程序支持函数必须用 C 编写（尽管其目标函数不必用 C 编写），所以这是一个高级特性，相对较少的人会使用。

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.

规划程序支持函数必须具有 SQL 签名

A planner support function must have the SQL signature

supportfn(internal) returns internal

它通过在创建目标函数时指定 SUPPORT 子句来附加到其目标函数。

It is attached to its target function by specifying the SUPPORT clause when creating the target function.

可以在 PostgreSQL 源代码中的 src/include/nodes/supportnodes.h 文件中找到规划程序支持函数 API 的详细信息。此处我们仅提供规划程序支持函数可以执行哪些操作的概述。向支持函数发出的请求集是可扩展的，因此在未来的版本中可能会有更多操作。

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.

基于函数特有的属性，可以在规划过程中简化一些函数调用。例如，int4mul(n, 1) 可以简化为 n。通过让规划程序支持函数实现 SupportRequestSimplify 请求类型，可以执行此类型的转换。将针对在查询解析树中找到的每个目标函数实例调用支持函数。如果它发现特定调用可以简化为其他形式，则可以构建并返回表示该表达式的解析树。这也会自动适用于基于该函数的操作符——在刚给出的示例中，n * 1 也将简化为 n。（但请注意，这仅仅是一个示例；这种特定的优化实际上并未由标准 PostgreSQL 执行。）我们不保证 PostgreSQL 在支持函数可以简化的案例中绝不会调用目标函数。确保简化表达式和目标函数的实际执行之间有严格的等价性。

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.

对于返回 boolean 的目标函数，通常很有用的是估计将使用该函数通过 WHERE 子句选择的行数的比例。这可以通过实现 SupportRequestSelectivity 请求类型的支持函数来完成。

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.

如果目标函数的运行时高度依赖于其输入，则为其提供非常量成本估计可能很有用。这可以通过实现 SupportRequestCost 请求类型的支持函数来完成。

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.

对于返回集合的目标函数，通常很有用的是提供对将返回的行数的非常量估计。这可以通过实现 SupportRequestRows 请求类型的支持函数来完成。

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.

对于返回 boolean 的目标函数，可以将显示在 WHERE 中的函数调用转换成一个或多个可索引操作符子句。转换后的子句可能与函数的条件完全等价，或者在一定程度上较弱（即，它们可能接受函数条件所不接受的一些值）。在后一种情况下，索引条件被称为 lossy；它仍然可用于扫描索引，但必须针对索引返回的每一行执行函数调用，以查看它是否真的通过了 WHERE 条件。为了创建这样的条件，支持函数必须实现 SupportRequestIndexCondition 请求类型。

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.