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 configuration parameter, and 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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on indicates that the query optimizer's use of bitmap-scan plan types is enabled.
• off indicates that the query optimizer's use of bitmap-scan plan types is disabled.

Default value: on

force_bitmapand

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

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

Value range: Boolean

• on indicates that the query optimizer's use of BitmapAnd plan types is enabled.
• off indicates that the query optimizer's use of BitmapAnd plan types is disabled.

Default value: off

enable_hashagg

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

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

Value range: Boolean

• on indicates that the query optimizer's use of Hash aggregation plan types is enabled.
• off indicates that the query optimizer's use of Hash aggregation plan types is disabled.

Default value: on

enable_hashjoin

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

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

Value range: Boolean

• on indicates that the query optimizer's use of Hash-join plan types is enabled.
• off indicates that the query optimizer's use of Hash-join plan types is disabled.

Default value: on

enable_indexscan

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

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

Value range: Boolean

• on indicates that the query optimizer's use of index-scan plan types is enabled.
• off indicates that the query optimizer's use of index-scan plan types is disabled.

Default value: on

enable_indexonlyscan

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

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

Value range: Boolean

• on indicates that the query optimizer's use of index-only-scan plan types is enabled.
• off indicates that the query optimizer's use of index-only-scan plan types is disabled.

Default value: on

enable_material

Parameter description: Controls the query optimizer's use of materialization. It is impossible to suppress materialization entirely, but setting this variable to off prevents the optimizer from inserting materialized nodes.

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

Value range: Boolean

• on indicates that the query optimizer's use of materialization is enabled.
• off indicates that the query optimizer's use of materialization is disabled.

Default value: on

enable_mergejoin

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

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

Value range: Boolean

• on indicates that the query optimizer's use of merge-join plan types is enabled.
• off indicates that the query optimizer's use of merge-join plan types is disabled.

Default value: on

enable_nestloop

Parameter description: Controls the query optimizer's use of nested-loop join plan types to fully scan internal tables. It is impossible to suppress nested-loop joins entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

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

Value range: Boolean

• on indicates that the query optimizer's use of nested-loop join plan types is enabled.
• off indicates that the query optimizer's use of nested-loop join plan types is disabled.

Default value: on

enable_index_nestloop

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

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

Value range: Boolean

• on indicates that the query optimizer's use of nested-loop join plan types is enabled.
• off indicates that the query optimizer's use of nested-loop join plan types is disabled.

Default value: on

enable_seqscan

Parameter description: Controls the query optimizer's use of sequential scan plan types. It is impossible to suppress sequential scans entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

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

Value range: Boolean

• on indicates that the query optimizer's use of sequential scan plan types is enabled.
• off indicates that the query optimizer's use of sequential scan plan types is disabled.

Default value: on

enable_sort

Parameter description: Controls the query optimizer's use of sort methods. It is impossible to suppress explicit sorts entirely, but setting this variable to off encourages the optimizer to choose other methods if available.

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

Value range: Boolean

• on indicates that the query optimizer's use of sort methods is enabled.
• off indicates that the query optimizer's use of sort methods is disabled.

Default value: on

enable_tidscan

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

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

Value range: Boolean

• on indicates that the query optimizer's use of TID scan plan types is enabled.
• off indicates that the query optimizer's use of TID scan plan types is 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 parameter is a SUSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

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

Default value: off

enforce_a_behavior

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

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

Value range: Boolean

• on indicates that the O matching rule is used.
• off indicates that the POSIX matching rule is used.

Default value: on

max_recursive_times

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

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

Value range: an integer ranging from 0 to INT_MAX

Default value: 200

enable_vector_engine

Parameter description: Controls the query optimizer's use of vectorized executor.

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

Value range: Boolean

• on indicates the query optimizer's use of vectorized executor is enabled.
• off indicates the query optimizer's use of vectorized executor is 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 parameter is a SUSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: Boolean

• on indicates that the internal table running costs will be excluded.
• off indicates that the internal table running costs will not be excluded.

Default value: off

enable_absolute_tablespace

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

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

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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

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 computing 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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

Value range: a string

• optimal: The list of candidate computing Node Groups consists of the Node Groups where the operator's operation objects are located and the Node Group that combines all the Node Groups on which the current user has the COMPUTE permission.
• query: The list of candidate computing Node Groups consists of the Node Groups where the operator's operation objects are located and the Node Group that combines all 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 computing Node Groups consists of the Node Group 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 computing 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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

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 to compute.

Default value: off

qrw_inlist2join_optmode

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

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

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 elements in the list 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 parameter is a USERSET parameter. Set it based on instructions provided in Table 1 GUC parameters.

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 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%.

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

Default value: -10

check_implicit_conversions

Parameter description: Specifies whether to check candidate index paths generated for index columns that have implicit type conversions in a query.

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

Value range: Boolean

• on indicates that a check will be performed for candidate index paths generated for index columns that have 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.

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

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

Default value: 1

try_vector_engine_strategy

Parameter description: Specifies the policy for processing row-store tables using the vectorized executor. By setting this parameter, queries containing row-store tables can be converted to vectorized execution plans for calculation, improving the execution performance of complex queries in AP-like scenarios.

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

Valid value: enumerated values

• off is the default value, which indicates that this function is disabled. That is, row-store tables will not be converted into vectorized execution plans for execution.
• force: Queries are forcibly converted to vectorized execution plans for execution no matter whether the base table to be queried is a row-store table, column-store table, or hybrid row-column store table, unless the query type or expression is not supporte by the vectorized executor. In this case, the performance may deteriorate in different query scenarios.
• optimal: On the basis of force, the optimizer determines whether to convert a query statement into a vectorized execution plan based on the query complexity to avoid performance deterioration after the conversion.

Default value: off