Optimizer Method Configuration

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

Value range: Boolean

• on: used.
• off: not used.

Default value: on

This parameter does not take effect in the current version.

enable_bitmapscan

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

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

Default value: on

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: After this function is disabled, the bitmap scan operator is disabled globally, which may cause performance deterioration in some scenarios. Exercise caution when modifying the parameter to avoid risks caused by misoperations.

force_bitmapand

Parameter description: Specifies whether the optimizer forcibly uses BitmapAnd plan types.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

Default value: on

enable_mergejoin

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

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

Default value: off

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

Default value: off

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

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

Value range: Boolean

• on: used.
• off: not used.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

Default value: on

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

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

Value range: Boolean

• on: used.
• off: not used.

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

Value range: Boolean

• on: used.
• off: not used.

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

enforce_a_behavior

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

This is a USERSET parameter. Set it based on instructions provided 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

max_recursive_times

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

Parameter type: integer.

Unit: none

Value range: 0 to INT_MAX

Default value: 200

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

enable_change_hjcost

Parameter description: Specifies whether the optimizer excludes inner 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: used.
• off: not used.

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

Parameter type: string.

Unit: none

Value range:

• 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 rule-based inlist2join query rewriting is forcibly enabled.
• A positive integer (1 to 2147483647) 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

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

skew_option

Parameter description: Specifies whether an optimization policy is used.

This is a USERSET parameter. Set it based on instructions provided 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 non-negative value, the value is used as the estimated number of limit rows. If this parameter is set to a negative value, the value is converted to a percentage and used as default estimated value; for example, -5 indicates 5%.

Parameter type: floating point

Unit: none

Value range: -100 to DBL_MAX

Default value: –10

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: none

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

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

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

Default value: 1

enable_default_index_deduplication

Parameter description: Specifies whether to deduplicate and compress tuples with duplicate key values for a B-tree index by default. The deduplication and compression functions do not take effect for primary key indexes and unique indexes. When there are a large number of indexes with duplicate key values, the deduplication and compression function can effectively reduce the space occupied by indexes. In scenarios where non-unique indexes are used and index key values are seldom repeated or unique, the deduplication and compression function slightly deteriorates the index insertion performance. If the WITH (deduplication set to on/off) syntax is used during index creation, the deduplication parameter is preferentially used to determine whether to use deduplication and compression for the index.

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

Value range: Boolean

• off: indicates that the deduplication and compression function is disabled for B-tree indexes. This is the default value.
• on: indicates that the deduplication and compression function is enabled for B-tree indexes.

Default value: off

enable_expr_fusion

Parameter description: Specifies whether to enable the SRF, expression flattening, centralized Seq Scan projection cancellation, transition status of shared aggregate functions, and step number optimization features.

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

Value range: Boolean

• off: indicates that this function is disabled. This is the default value.
• on: indicates that the SRF, expression flattening, centralized Seq Scan projection cancellation, transition status of shared aggregate functions, and step number optimization features are enabled.

Default value: off

The SRF supports only the scenario where query_dop is set to 1.

enable_opfusion_reuse

Parameter description: Specifies whether to reuse the opfusion obj memory.

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

Value range: Boolean

• on indicates that the opfusion obj memory is reused when conditions are met.
• off indicates that this function is disabled.

Default value: on

enable_iud_fusion

Parameter description: Specifies whether to optimize the IUD statements.

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

Value range: Boolean

• on indicates that the input is optimized during type conversion.
• off indicates that this function is disabled.

Default value: off