Query Tuning / Planner Method Configuration v10

This section describes the configuration parameters used for planner method configuration.

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.

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