On this page

Show all

Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-2.x)/ Configuring GUC Parameters/ GUC Parameters/ Query Planning/ Optimizer Method Configuration

Optimizer Method Configuration

Updated on 2025-02-27 GMT+08:00

View PDF

These configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan. Better ways include adjusting the optimizer cost constants, manually running ANALYZE, increasing the value of the default_statistics_target parameter, or increasing the amount of the statistics collected in specific columns using ALTER TABLE SET STATISTICS.

enable_bitmapscan

Parameter description: Controls the query optimizer's use of bitmap-scan plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

force_bitmapand

Parameter description: Controls the query optimizer's use of BitmapAnd plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: off

enable_hashagg

Parameter description: Controls the query optimizer's use of Hash aggregation plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_hashjoin

Parameter description: Controls the query optimizer's use of Hash-join plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_indexscan

Parameter description: Controls the query optimizer's use of index-scan plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_indexonlyscan

Parameter description: Controls the query optimizer's use of index-only-scan plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_material

Parameter description: Specifies the query optimizer's use of materialization.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_mergejoin

Parameter description: Controls the query optimizer's use of merge-join plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: off

enable_nestloop

Parameter description: Controls whether the query optimizer uses the nested-loop join plan type to fully scan internal tables. If this variable is disabled, the optimizer preferentially selects another method when another method exists.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: off

enable_index_nestloop

Parameter description: Controls the query optimizer's use of the index nested-loop join plan types to scan the parameterized indexes of internal tables.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_seqscan

Parameter description: Controls the query optimizer's use of sequential scan plan types. If this variable is disabled, the optimizer preferentially selects another method when another method exists.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_sort

Parameter description: Specifies the query optimizer's choice of sort methods. If this variable is disabled, the optimizer preferentially selects another method when another method exists.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_tidscan

Parameter description: Controls the query optimizer's use of Tuple ID (TID) scan plan types.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_kill_query

Parameter description: In CASCADE mode, when a user is deleted, all the objects belonging to the user are deleted. This parameter specifies whether the queries of the objects belonging to the user can be unlocked when the user is deleted.

This is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

on indicates that the unlocking is allowed.
off indicates that the unlocking is not allowed.

Default value: off

enable_stream_concurrent_update

Parameter description: Controls the use of stream in concurrent updates. This parameter is restricted by the enable_stream_operator parameter.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that the optimizer can generate stream plans for the UPDATE statement.
off indicates that the optimizer can generate only non-stream plans for the UPDATE statement.

Default value: on

enable_stream_operator

Parameter description: Controls the query optimizer's use of stream. When this parameter is set to off, a large number of logs indicating that the stream plans cannot be pushed down are recorded. If you do not need these logs, you are advised to set both enable_unshipping_log and enable_stream_operator to off.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value:

Independent deployment: off
Finance edition (standard): on
Enterprise edition: on
Finance edition (data computing): on

enable_stream_recursive

Parameter description: Specifies whether to push WITH RECURSIVE join queries to DNs for processing.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that WITH RECURSIVE join queries will be pushed down to DNs.
off indicates that WITH RECURSIVE join queries will not be pushed down.

Default value: on

max_recursive_times

Parameter description: Specifies the maximum number of WITH RECURSIVE iterations.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 0 to 2147483647

Default value: 200

enable_broadcast

Parameter description: Controls the query optimizer's use of broadcast distribution method when it evaluates the cost of stream.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: on

enable_change_hjcost

Parameter description: Specifies whether the optimizer excludes internal table running costs when selecting the Hash Join cost path. If it is set to on, tables with a few records and high running costs are more possible to be selected.

This is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

on: enabled.
off: disabled.

Default value: off

best_agg_plan

Parameter description: The query optimizer generates three plans for the aggregate operation under the stream:

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

This parameter is used to control the type of hashagg plans generated by the query optimizer.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: an integer ranging from 0 to 3

1 indicates that the first plan is forcibly generated.
2 indicates that the second plan is forcibly generated if the group by column can be redistributed. Otherwise, the first plan is generated.
3 indicates that the third plan is forcibly generated if the group by column can be redistributed. Otherwise, the first plan is generated.
0 indicates that the optimizer chooses an optimal plan based on the estimated costs of the three plans above.

Default value: 0

agg_redistribute_enhancement

Parameter description: When the aggregate operation is performed, which contains multiple group by columns and none of the columns is the distribution key, a group by column will be selected for redistribution. This parameter specifies the policy of selecting a redistribution key.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that the column that can be redistributed and evaluates the most distinct value is selected for redistribution.
off indicates that the first column that can be redistributed is selected for redistribution.

Default value: off

enable_absolute_tablespace

Parameter description: Controls whether the tablespace can use an absolute path.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that an absolute path can be used.
off indicates that an absolute path cannot be used.

Default value: on

enable_valuepartition_pruning

Parameter description: Specifies whether the DFS partitioned table is dynamically or statically optimized.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that the DFS partitioned table is dynamically or statically optimized.
off indicates that the DFS partitioned table is not dynamically or statically optimized.

Default value: on

expected_computing_nodegroup

Parameter description: Specifies a compute node group or the way to choose such a group. The node group mechanism is now for internal use only. You do not need to set it.

During join or aggregation operations, a node group can be selected in four modes. In each mode, the specified candidate computing node groups are listed for the optimizer to select the most appropriate one for the current operator.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: a string

optimal: The list of candidate compute node groups consists of the node groups where the operator's operation objects are located and the node group that combines all DNs in the node groups on which the current user has the COMPUTE permission.
query: The list of candidate compute node groups consists of the node groups where the operator's operation objects are located and the node group that combines all DNs in the node groups where base tables involved in the query are located.
Node group name (when enable_nodegroup_debug is set to off): The list of candidate compute node groups consists of the node groups where the operator's operation objects are located and the specified node group.
Node group name (when enable_nodegroup_debug is set to on): A specific node group is used as the compute node group.

Default value: query

enable_nodegroup_debug

Parameter description: Specifies whether the optimizer assigns computing workloads to a specific node group when multiple node groups exist in an environment. The Node Group mechanism is now for internal use only. You do not need to set it.

This parameter takes effect only when expected_computing_nodegroup is set to a specific node group.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that computing workloads are assigned to the node group specified by expected_computing_nodegroup.
off indicates no node group is specified for computing.

Default value: off

stream_multiple

Parameter description: Specifies the weight used by the optimizer to calculate the final cost of stream operators.

The base stream cost is multiplied by this weight to obtain the final cost.

This is a USERSET parameter. Set it based on instructions in Table 1.

NOTICE:

This parameter is applicable only to Redistribute and Broadcast streams.

Value range: a floating-point number ranging from 0 to DBL_MAX

Default value: 1

qrw_inlist2join_optmode

Parameter description: Specifies whether to enable inlist-to-join (inlist2join) query rewriting.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: a string

disable indicates that the inlist2join query rewriting is disabled.
cost_base indicates that the cost-based inlist2join query rewriting is enabled.
rule_base indicates that the forcible rule-based inlist2join query rewriting is enabled.
A positive integer indicates the threshold of inlist2join query rewriting. If the number of list elements in the IN clause is greater than the threshold, the rewriting is performed.

Default value: cost_base

skew_option

Parameter description: Specifies whether an optimization policy is used.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: enumerated values

off indicates that the policy is disabled.
normal indicates that a radical policy is used. All possible skews are optimized.
lazy indicates that a conservative policy is used. Uncertain skews are ignored.

Default value: normal

cost_weight_index

Parameter description: Specifies the cost weight of index_scan.

If this parameter is set to 1, no adjustment is performed.
If this parameter is set to a value less than 1, the cost of index_scan is reduced and index_scan is more likely to be selected by the optimizer.
If this parameter is set to a value greater than 1, the cost of index_scan increases.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: a floating-point number ranging from 1e-10 to 1e+10.

Default value: 1

default_limit_rows

Parameter description: Specifies the default estimated number of limit rows for generating a generic plan. If this parameter is set to a positive value, the positive value is used as the estimated number of limit rows. If the positive value is a decimal, the value is rounded up automatically. If this parameter is set to a negative value, the negative value is converted to a percentage and used as default estimated value, that is, -5 indicates 5%.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: a floating-point number ranging from –100 to DBL_MAX

Default value: –10

enforce_a_behavior

Parameter description: Controls the rule matching modes of regular expressions.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that the A matching rule is used.
off indicates that the POSIX matching rule is used.

Default value: on

check_implicit_conversions

Parameter description: Specifies whether to check that candidate index paths are generated for index columns with implicit type conversion in a query. For details about the application scenarios of this parameter, see "SQL Optimization > Checking the Implicit Conversion Performance" in Developer Guide.

This is a USERSET parameter. Set it based on instructions in Table 1.

Value range: Boolean

on indicates that the system checks whether candidate index paths are generated for index columns with implicit type conversion in a query.
off indicates that a check will not be performed.

Default value: off

NOTICE:

When this parameter is set to on, you need to set enable_fast_query_shipping to off so that the mechanism for identifying implicit data type conversion of index columns can take effect.

Parent topic: Query Planning

Previous topic: Query Planning

Next topic: Optimizer Cost Constants