Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ Configuring GUC Parameters/ GUC Parameters/ Query Planning/ Optimizer Method Configuration
Updated on 2025-02-27 GMT+08:00
View PDF
Share

Optimizer Method Configuration

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_broadcast

Parameter description: Controls whether the query optimizer uses the 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

This parameter does not take effect in the current version.

enable_bitmapscan

Parameter description: Specifies 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: Specifies 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: Specifies 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: Specifies 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: Specifies 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: Specifies 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: Specifies 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: Specifies 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: Specifies whether the query optimizer uses the nested-loop join plan type 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: Specifies 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: Specifies 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 in Table 1.

Value range: Boolean

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

Default value: off

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 INT_MAX

Default value: 200

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 in Table 1.

Value range: Boolean

  • on: enabled.
  • off: disabled.

Default value: off

enable_absolute_tablespace

Parameter description: Specifies whether the tablespace can use the 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

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: a string

  • 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

default_limit_rows

Parameter description: Specifies the default estimated number of limit rows for generating genericplan. 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

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.

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

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

Parent topic: Query Planning