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, and increasing the amount of the statistics collected in specific columns using ALTER TABLE SET STATISTICS.
enable_bitmapscan
Parameter description: Specifies 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: Retain the default value.
Risks and impacts of improper settings: 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 query optimizer forcibly uses BitmapAnd plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this parameter is enabled, the optimizer forcibly performs bitmap and index scan when the conditions for using the bitmap and index scan operator are met. In some scenarios, the query performance may be affected. Exercise caution when modifying the parameter to avoid risks caused by misoperations.
enable_hashagg
Parameter description: Specifies the query optimizer's use of Hash aggregation plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the Hash aggregation operator cannot be used in some scenarios, affecting the query rewriting logic and deteriorating the query performance. Exercise caution when modifying the parameter to avoid risks caused by misoperations.
enable_hashjoin
Parameter description: Specifies whether the optimizer uses hash join 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: Retain the default value.
Risks and impacts of improper settings: After this function is disabled, the Hash Join operator cannot be used globally and other Join plans are preferentially generated. As a result, the performance may deteriorate in some scenarios. Exercise caution when modifying the parameter to avoid risks caused by misoperations.
enable_indexscan
Parameter description: Specifies whether the optimizer uses index-scan plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this function is disabled, the index 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.
enable_indexonlyscan
Parameter description: Specifies whether the optimizer uses index-only-scan plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this function is disabled, the index-only-scan operator cannot be used globally. Some plans that require only index scan but do not require table return cannot be generated. As a result, the performance may deteriorate in some scenarios. Exercise caution when modifying the parameter to avoid risks caused by misoperations.
enable_material
Parameter description: Specifies whether the query optimizer uses materialization. It is impossible to suppress materialization entirely, but setting this parameter to off prevents the optimizer from inserting materialized nodes. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this function is disabled, the optimizer is prohibited from inserting entity nodes. As a result, the Material operator cannot be generated in some scenarios, and the performance may deteriorate in some scenarios.
enable_mergejoin
Parameter description: Specifies whether the optimizer uses merge-join plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is enabled, the optimizer generates a merge join path for joins that meet the conditions. As a result, the selected join operator in this scenario may not be the optimal one.
enable_nestloop
Parameter description: Specifies whether the query optimizer uses nested-loop join plan types to fully scan internal tables. It is impossible to completely eliminate nested-loop joins. However, if this parameter is disabled, the optimizer prefers other methods when other methods exist. This parameter can be set at the PDB level.
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. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Risks and impacts of improper settings: If this parameter is enabled, the optimizer generates a NestloopJoin path for joins that meet the conditions. As a result, the selected join operator in this scenario may not be the optimal one.
enable_index_nestloop
Parameter description: Specifies whether the optimizer uses the nested-loop join plan type to scan the parameterized indexes of inner tables. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, parameterized paths cannot be generated in some scenarios. Generally, parameterized paths can improve query performance. Therefore, you are advised to enable this parameter.
enable_seqscan
Parameter description: Specifies whether the query optimizer uses sequential scan plan types. It is impossible to completely eliminate sequential scan. However, if this parameter is disabled, the optimizer prefers other methods when other methods exist. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the optimizer tends to select other scan methods and lowers the priority of sequential scan methods. In some scenarios, the query performance may be affected.
enable_sort
Parameter description: Specifies whether the optimizer uses sort methods. It is impossible to completely eliminate explicit sorting. However, if this parameter is disabled, the optimizer prefers other methods when other methods exist. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the optimizer prefers other methods and lowers the priority of the sorting method. Therefore, you are advised to enable this parameter.
enable_tidscan
Parameter description: Specifies whether the optimizer uses Tuple ID (TID) scan plan types. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the optimizer prefers other methods and lowers the priority of the TID scan method. Therefore, you are advised to enable this parameter.
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 can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the unlocking is allowed.
- off indicates that the unlocking is not allowed.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this parameter is enabled, when a user is deleted, the query of the user may be exposed, affecting system consistency and security.
max_recursive_times
Parameter description: Specifies the maximum number of WITH RECURSIVE iterations. This parameter can be set at the PDB level.
Parameter type: integer.
Unit: none
Value range: 0 to 2147483647
Default value: 200. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on service requirements.
Risks and impacts of improper settings: This parameter is used to prevent an infinite loop of the WITH RECURSIVE statement by using nocycle in hierarchical queries to exit the loop. If you want to adjust the value, make sure you understand the parameter meaning and adjust the value with caution to avoid risks caused by misoperations.
enforce_a_behavior
Parameter description: Specifies the rule matching modes of regular expressions. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The A-compatible matching rule is used.
- off: The POSIX-compatible matching rule is used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Set this parameter based on service requirements.
Risks and impacts of improper settings: The regular expression in the query statement cannot be matched as expected.
enable_vector_engine
Parameter description: Specifies whether the optimizer uses vectorized executor. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the optimizer may fail to generate vectorized query plans for specific scenarios, affecting query performance.
enable_broadcast
Parameter description: Specifies whether the query optimizer uses broadcast distribution method when it evaluates the cost of stream. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The accuracy of stream plan generation and the query performance are affected.

This parameter does not take effect in the centralized version.
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 parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: used.
- off: not used.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a SUSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The hash join cost may be incorrectly estimated, affecting the accuracy of generating hash join plans.
enable_absolute_tablespace
Parameter description: Specifies whether the tablespace can use the absolute path. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: An absolute path can be used.
- off: No absolute path can be used.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, absolute paths cannot be used when tablespaces are created. You are advised to set this parameter based on service requirements. No other risk exists.
enable_valuepartition_pruning
Parameter description: Specifies whether the DFS partitioned table is dynamically or statically optimized. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The DFS partitioned table is dynamically or statically optimized.
- off: The DFS partitioned table is not dynamically or statically optimized.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this parameter is disabled, the query performance of DFS partitioned tables may deteriorate.
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. 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.
Parameter type: Boolean.
Unit: none
Value range:
- on: The deduplication and compression function is enabled for B-tree indexes.
- off: The deduplication and compression function is disabled for B-tree indexes.
Default value: off
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. You can enable this parameter for non-unique indexes, high index key value repetition, and large index space usage.
Risks and impacts of improper settings: 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.
enable_expr_fusion
Parameter description: Specifies whether to enable the SRF, expression flattening, transition status of shared aggregate functions, and step number optimization features. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The SRF, expression flattening, transition status of shared aggregate functions, and step number optimization features are enabled.
- off: This function is disabled.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is enabled, the optimizer running cost increases.

The SRF supports only the scenario where query_dop is set to 1.
qrw_inlist2join_optmode
Parameter description: Specifies whether to enable inlist-to-join query rewriting. This parameter can be set at the PDB level.
Parameter type: string.
Unit: none
Value range:
- disable: The inlist-to-join query rewriting is disabled.
- cost_base: cost-based inlist-to-join query rewriting. That is, whether to use inlist-to-join query rewriting is automatically evaluated based on the cost.
- rule_base: The rule-based inlist-to-join query rewriting is forcibly enabled.
- A positive integer (1 to 2147483647) indicates the threshold of inlist-to-join query rewriting. If the number of elements in the list is greater than the threshold, the rewriting is performed.
Default value: cost_base. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. Use other values only when manual optimization is required in abnormal scenarios or when the query performance of the plan selected by the optimizer is low.
Risks and impacts of improper settings: Whether the optimizer performs inlist-to-join query rewriting is affected. No better plan is generated due to improper settings.
skew_option
Parameter description: Specifies whether an optimization policy is used. This parameter can be set at the PDB level.
Value type: enumerated type.
Unit: none
Value range: off, normal, and lazy
- 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. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Whether the optimizer optimizes skew is affected. The plan generation performance or query execution performance is affected due to improper settings.
default_limit_rows
Parameter description: Specifies the default estimated number of limit rows for generating genericplan. This parameter can be set at the PDB level.
Parameter type: floating point.
Unit: none
Value range: –100 to DBL_MAX
- If this parameter is set to a non-negative number, the value is used as the estimated number of limit rows.
- If this parameter is set to a negative value, the value is used as the default estimated number in percentage. A negative value is converted to the default percentage, that is, –5 indicates 5%.
Default value: –10. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The genericplan query performance of the limit operator may be affected.
check_implicit_conversions
Parameter description: Specifies whether to check candidate index paths generated for index columns that have implicit type conversion in a query. This parameter can be set at the PDB level. For details about the application scenarios of this parameter, see "SQL Optimization > Checking the Performance of Implicit Conversion" in Developer Guide.
Parameter type: Boolean.
Unit: none
Value range:
- on: A check will be performed for candidate index paths generated for index columns that have implicit type conversion in a query.
- off: No check will be performed.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impact of improper settings: If the check is not performed, the regular expression in the query statement may not be matched as expected.
cost_weight_index
Parameter description: Specifies the cost weight of index_scan. This parameter can be set at the PDB level.
Parameter type: floating point.
Unit: none
Value range: 1e-6 to 1e+10
Default value: 1. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: This parameter specifies whether the optimizer uses index_scan. The optimizer may use a non-optimal plan due to improper settings, affecting query performance.
try_vector_engine_strategy
Parameter description: Specifies the policy for processing row-store tables by 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 can be set at the PDB level.
Value type: enumerated type.
Unit: none
Value range:
- off: This function is disabled. That is, row-store tables will not be converted into vectorized execution plans for execution.
- force: The base table to be queried is a row-store table as long as queries do not contain types or expressions that are not supported by the vectorized engine. Queries are forcibly converted to vectorized execution plans for execution. 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. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: Whether the optimizer uses a vectorized execution plan is affected. The query performance may be affected in some scenarios due to improper settings.
enable_opfusion_reuse
Parameter description: Specifies whether to reuse the opfusion obj memory. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the opfusion obj memory is reused when conditions are met.
- off indicates that this function is disabled.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this function is disabled, plans may be regenerated and the query performance may deteriorate.
enable_iud_fusion
Parameter description: Specifies whether to optimize the IUD statements. This parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the input is optimized during type conversion.
- off indicates that this function is disabled.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value. You can enable this function if the IUD statement performance needs to be optimized.
Risks and impacts of improper settings: If this parameter is enabled, the optimizer cost increases without significant risk.
enable_cached_operator
Parameter description: Specifies whether to enable the cache feature for operators in execution state.
Parameter type: Boolean.
Unit: none
Value range:
- on: Operators can be executed in cache mode.
- off: This function is disabled.
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.
Risks and impacts of improper settings: If this parameter is disabled, the operator execution status cannot be cached, affecting the query performance.
enable_smp_partitionwise
Parameter description: Specifies whether to enable the Partition-wise Join capability in the SMP scenario. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on: The Partition-wise Join capability is enabled in the SMP scenario.
- off: The Partition-wise Join capability is disabled in the SMP scenario.
Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The data query performance in the SMP scenario may be affected.
force_smp_partitionwise_scan
Parameter description: Specifies whether to enable the Partition-wise Scan parallel scan mode globally after the Partition-wise Join switch enable_smp_partitionwise is turned on in the SMP scenario. In the multi-tenancy scenario, this parameter can be set at the PDB level.
Parameter type: Boolean.
Unit: none
Value range:
- on:The Partition-wise Scan parallel scan mode is enabled in the SMP scenario.
- off:The Partition-wise Scan parallel scan mode is disabled in the SMP scenario.
Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: The performance of reading partitioned table data in the SMP scenario may be affected.
enable_nestagg
Parameter description: Specifies whether to enable nested aggregate function. Nested aggregation is a double aggregation syntax similar to sum(min(col)).
Parameter type: Boolean.
Unit: none
Value range:
- on: The nested aggregate function is enabled.
- off: The nested aggregate function is disabled.
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.
Risks and impacts of improper settings: The nested aggregate function is affected.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot