2.1.3.4 Query Tuning

Table of Contents Previous Next



2.1.3.4 Query Tuning
2.1.3.4.1 enable_hints
Range: {true | false}
Optimizer hints embedded in SQL commands are utilized when enable_hints is on. Optimizer hints are ignored when this parameter is off.
Range: {0 | 100}
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.
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.
Range: {true | false}
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).


Table of Contents Previous Next