Optimizer Method Configuration

These configuration parameters provide a basic way to influence the query optimizer's choice of execution plans. If the default plan chosen by the optimizer for a specific query is not optimal, these configuration parameters can be used to force the optimizer to select a different plan as a temporary solution. Better methods include tuning the optimizer cost constants, manually running ANALYZE, and increasing the value of the default_statistics_target parameter.

enable_hashjoin

Definition: Controls whether the query optimizer uses the Bitmap Scan plan type.

Range: Boolean

on: enabled.
off: disabled.

Default Value: on

enable_nestloop

Definition: Controls whether the query optimizer uses the nested-loop join plan type to fully scan inner tables. It is impossible to eliminate nested loop joins. However, disabling this variable will make the optimizer prefer other methods when they exist.

Range: Boolean

on: enabled.
off: disabled.

Default Value: off

best_agg_plan

Definition: For Agg operations under stream, the optimizer generates three plans:

hashagg+gather(redistribute)+hashagg
redistribute+hashagg(+gather)
hashagg+redistribute+hashagg(+gather)

This parameter controls which hashagg plan the optimizer generates.

Range: 0, 1, 2, and 3.

The value 1 forces the generation of the first plan.
The value 2 forces the generation of the second plan if the group by columns can be redistributed. Otherwise, it generates the first plan.
The value 3 forces the generation of the third plan if the group by columns can be redistributed. Otherwise, it generates the first plan.
The value 0 allows the optimizer to select the best plan based on the estimated costs of the above three plans.

Default Value: 0

turbo_engine_version

Definition: For tables created with the turbo storage format (with the enable_turbo_store parameter set to on in the table attributes), and when the query does not involve merge join or sort agg operators, the executor can utilize the turbo execution engine, significantly improving performance.

Range: 0, 1, 2, and 3.

The value 0 disables the turbo execution engine.
The value 1 enables the turbo execution engine only for single-table aggregate queries.
The value 2 enables the turbo execution engine only for single-table aggregate or multi-table join queries.
The value 3 accelerates most common operators using the turbo execution engine, excluding operators like merge join and sort agg. When the data volume is large and turbo_engine_version is set to 3, the occurrence of merge join and sort agg operators is relatively rare, so turbo execution engine acceleration can be achieved for almost SQL statements.

Default Value: 3

You are advised not to enable the turbo execution engine in cross-VW scenarios.

agg_redistribute_enhancement

Definition: During the Agg operation, if multiple group by columns are contained and none of them is a distribution column, a group by column is selected for redistribution. This parameter controls the policy for selecting a redistribution column.

Range: Boolean

on indicates that the redistribution column with the largest estimated distinct value is selected as the redistribution column.
off indicates that the first column that can be redistributed is selected as the redistribution column.

Default Value: off

skew_option

Definition: Controls whether to use the optimization policy.

Range: String

off: disables the policy.
normal: uses the aggressive policy. For scenarios where skew occurrence is uncertain, assumes skew exists and performs corresponding optimizations.
lazy: uses the conservative policy. For scenarios where skew occurrence is uncertain, assumes no skew exists and performs no optimizations.

Default Value: normal

rewrite_rule

Definition: Identifies the optional query rewriting rules to be enabled. In specific scenarios, this GUC parameter can be used to configure the query rewriting rules to optimize query performance.

This parameter controls the combination of query rewriting rules. For example, with multiple rewriting rules: rule1, rule2, rule3, and rule4, you can set:

set rewrite_rule='on(rule1)';          -- Enable query rewriting rule rule1.
set rewrite_rule='off(rule1)';          -- Disable query rewriting rule rule1.
set rewrite_rule='rule2,rule3';    -- Enable query rewriting rules rule2 and rule3.
set rewrite_rule=none;           -- Disable all optional query rewriting rules.

Range: String

**Table 1** Value descriptions
Value	Description
none	Does not use any optional query rewriting rules.
lazyagg	Uses the Lazy Agg query rewriting rules (removes aggregation operations in subqueries).
magicset	Uses the Magic Set query rewriting rules (pushes conditions from the main query to the subquery).
uniquecheck	Uses the Unique Check rewriting rule (applicable when the target column does not include expression sublinks of aggregate functions. Requires the target column values to be unique after aggregating the sublinks based on related columns. Recommended for professional tuning experts).
disablerep	Uses the rule prohibiting sublink promotion for replicated tables (prohibits sublink promotion for replicated tables).
notinopt	Uses the Not In query rewriting rule (converts Not In to more efficient Hash Join).
or_conversion	Uses OR query rewriting rules (splits or rewrites complex predicates containing OR).
projection_pushdown	Uses Projection Pushdown query rewriting rules.
plain_lazyagg	Uses simplified Lazy Agg query rewriting rules (applied to individual subqueries and distinct operations).
eager_magicset	Uses the Eager Magic Set query rewriting rule (allows pushing down qual conditions in subqueries with group by or partition by).
outer_join_quality_imply	Uses outer join query rewriting rules (enables transferring attributes from outer to inner tables in outer joins).
inlist_merge	Uses the Inlist Merge query rewriting rule (merges multiple IN clauses on the same column into a single IN(List)).
casewhen_simplification	Uses CASE WHEN query rewriting optimizations (performs constant folding on case when...then...else...end constructs, removes unreachable branches, and simplifies calculations).
subquery_qual_pull_up	Uses subquery pull-up query rewriting optimizations (moves filtering conditions from subqueries up to the outer queries).
join_elimination	Uses Join Elimination query rewriting rules (removes joins that do not impact the final results).
not_distinct_from_opt	Uses the Not Distinct From query rewriting rule (transforms "is not distinct from" into = format to support hashjoin).
foreign_agg_opt	Uses partial aggregate function query rewriting optimization rules (retrieves result sets via file footer for count(1), max, and min functions).
selfjoin_elimination	Uses self-join query optimization rewriting rules (reduces self-joins between two tables in EXISTS and ANY sublinks to single-table aggregations).

Default Value: magicset, or_conversion, projection_pushdown, plain_lazyagg, subquery_qual_pull_up, not_distinct_from_opt, and foreign_agg_opt.