Query Tuning / Planner Method Configuration v11
This section describes the configuration parameters used for planner method configuration.
edb_custom_plan_tries
Parameter Type: Integer
Default Value: 5
Range: -1 to 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.
Setting edb_custom_plan_tries to -1 allows custom plan generation for an infinite number of tries.
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 or 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.
edb_enable_pruning
Parameter Type: Boolean
Default Value: true
Range: {true | false}
Minimum Scope of Effect: Per session
When Value Changes Take Effect: Immediate
Required Authorization to Activate: Session user
When set to TRUE, edb_enable_pruning allows the query planner to early-prune partitioned tables. Early-pruning means that the query planner can prune (i.e., ignore) partitions that would not be searched in a query before generating query plans. This helps improve performance time as it eliminates the generation of query plans of partitions that would not be searched.
Conversely, late-pruning means that the query planner prunes partitions after generating query plans for each partition. (The constraint_exclusion configuration parameter controls late-pruning.)
The ability to early-prune depends upon the nature of the query in the WHERE clause. Early-pruning can be utilized in only simple queries with constraints of the type WHERE column = literal (e.g., WHERE deptno = 10).
Early-pruning is not used for more complex queries such as WHERE column = expression (e.g., WHERE deptno = 10 + 5).
- On this page
- edb_custom_plan_tries
- edb_enable_pruning