PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) 自定义custom plan次数

背景

PostgreSQL 数据库与商用数据库Oracle一样，支持绑定变量，绑定变量的目的是降低SQL硬解析的性能开销。

使用绑定变量后，执行变成这样的流程，一次prepare，后面都只需要bind和execute：

prepare      
      
bind      
      
execute      

同时，prepare statement也降低了优化器的plan开销，流程这样：

前5次执行时，采样 custom plan(每次都需要根据SQL输入的变量，规划最佳plan tree)，并增加custom plan次数计数，平均COST计数。

第六次开始，就会生成generic plan，也就是说后面不再主动重新规划最佳plan tree，而是使用plan cache，将变量代入plan cache，直接通过plan cache产生这次bind 变量计算得到的COST，这个COST与custom plan的平均COST对比，如果COST小于或等于custom plan COST的1.1倍，则使用这个plan tree执行。否则才会主动进行custom plan，并继续累加custom plan次数计数，平均COST计数。

原理详见：

[《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare

execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》](../201212/20121224_01.md)

但是到底执行几次custom plan更好，这个不同的数据或和用户前几次调用有关，浙江直接导致后面的generic plan的选择和效率。

默认PostgreSQL是硬编码，5次，PostgreSQL 商用版本EPAS(阿里云ppas)允许用户自定义custom plan的次数。

PostgreSQL 商用版本EPAS(阿里云ppas)，用户自定义custom plan的次数

edb_custom_plan_tries

你可以在会话层面设置edb_custom_plan_tries 参数，也可以在用户、库、实例 层面设置。

Parameter Type: Numeric

Default Value: 5

Range: {0

100}

Minimum Scope of Effect: Per session

When Value Changes Take Effect: Immediate

Required Authorization to Activate: Session User

This configuration parameter controls the number of custom execution plans considered by the planner before the planner settles on a generic execution plan.

When a client application repeatedly executes a prepared statement, the server may decide to evaluate several execution plans before deciding to choose a custom plan or a generic plan.

• A custom plan is a plan built for a specific set of parameter values.

• A generic plan is a plan that will work with any set of parameter values supplied by the client application.

By default, the optimizer will generate five custom plans before evaluating a generic plan. That means that if you execute a prepared statement six times, the optimizer will generate five custom plans, then one generic plan, and then decide whether to stick with the generic plan.

In certain workloads, this extra planning can have a negative impact on performance. You can adjust the edb_custom_plan_tries configuration parameter to decrease the number of custom plans considered before evaluating a generic plan. Setting edb_custom_plan_tries to 0 will effectively disable custom plan generation.

Consider the following query:

PREPARE custQuery AS SELECT * FROM customer WHERE salesman >= $1

The $1 token in this query is a parameter marker - the client application must provide a value for each parameter marker each time the statement executes.

If an index has been defined on customer.salesman, the optimizer may choose to execute this query using a sequential scan, or using an index scan. In some cases, an index is faster than a sequential scan; in other cases, the sequential scan will win. The optimal plan will depend on the distribution of salesman values in the table and on the search value (the value provided for the $1 parameter).

When the client application repeatedly executes the custQuery prepared statement, the optimizer will generate some number of parameter-value-specific execution plans (custom plans), followed by a generic plan (a plan that ignores the parameter values), and then decide whether to stick with the generic plan or to continue to generate custom plans for each execution. The decision process takes into account not only the cost of executing the plans, but the cost of generating custom plans as well.

参考

《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

《PostgreSQL 函数调试、诊断、优化 & auto_explain》

https://www.enterprisedb.com/docs/en/10.0/EPAS_Guide_v10/EDB_Postgres_Advanced_Server_Guide.1.27.html#pID0E0GNF0HA

digoal’s 大量PostgreSQL文章入口