Help Center/ DataArts Fabric/ Developer Guide/ GUC Parameter/ Query Planning/ Optimizer Method Configuration
Updated on 2025-08-25 GMT+08:00
View PDF
Share

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:
  1. hashagg+gather(redistribute)+hashagg
  2. redistribute+hashagg(+gather)
  3. 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

Parent topic: Query Planning