Optimizer Method Configuration

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.

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.

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.

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

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

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 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_gsiscan

Parameter description: Specifies whether the query optimizer uses global secondary-index-only 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: In scenarios where global secondary indexes are available, if this parameter is disabled, only global secondary index scan plans cannot be generated, which may affect the query performance.

enable_gsitablescan

Parameter description: Specifies whether the query optimizer uses the global secondary index table 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: In scenarios where global secondary indexes are available, if this parameter is disabled, only global secondary index scan plans cannot be generated, which may affect the query performance.

gsi_fastpath_level

Parameter type: enumerated type.

Unit: none

Value range:

• strict: The equivalent condition contains all GSI index keys.
• partial: The equivalent condition contains partial index keys (leftmost match) of GSI. The performance may not be optimal.
• none: disabled.

Default value: strict

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: Improper settings may affect plan generation in some GSI scenarios, affecting query performance.

If you want to use partial:

gaussdb=# CREATE TABLE t1(a INT, b INT, c INT);
gaussdb=# CREATE GLOBAL UNIQUE INDEX t1_gsi ON t1(b,c);
-- Q1
gaussdb=# SELECT * FROM t1 WHERE b=1;
-- Q2
gaussdb=# SELECT * FROM t1 WHERE b=1 LIMIT 1;

In the preceding scenario, the GSI is created on the b and c columns. When gsi_fastpath_level is set to partial, the GSI non-stream return plan can be generated for query statements Q1 and Q2. The uniqueness of an index is ensured by the b and c columns. For query statement Q1, multiple results may be returned. The performance of the GSI non-stream return plan may not be optimal. For query statement Q2, the limit 1 condition is included, the performance of the GSI non-stream return plan is still optimal.

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.

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

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.

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.

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.

Risks and impacts of improper settings: If this parameter is enabled, the optimizer generates a nested-loop 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_index_nestloop

Parameter description: Specifies whether the optimizer uses the nested-loop join plan type to scan the parameterized indexes of inner tables.

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: 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_remote_param

Parameter description: Specifies whether to use the XC parameterized path feature. If this parameter is enabled, an XC parameterized path plan can be generated.

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: In related sublink and nested loop scenarios, if the outer table is small and the inner table is large, and the inner table has the leftmost matching index in the join condition, set this parameter to on to improve performance.

Risks and impacts of improper settings: In related sublink and nested loop scenarios, if the outer table size is large and the inner table size is small, setting this parameter to on may cause performance deterioration.

• Only XC plans are supported. Stream and FQS plans are not supported.
• A plan with the parameter rescan is generated for the base table only when the index parameterized path is generated for the base table.
• Only the parameterized path of the base table is supported. The parameterized path of the join table is not supported.
• Stream and XC plans cannot be adaptively selected.
• The nested loop parameterized path supports only the scenario where the inner table is RemoteQuery.
• The predpush hint is not supported.
• RemoteQuery with the PBE parameter cannot be pushed down.
• In some scenarios, there is no obvious advantage in the execution performance of parameterized paths. Manual optimization is required.
• In M-compatible database, the DN plan cannot be displayed when the value of max_datanode_for_plan is greater than 0.

enable_remote_pbe_reuse

Parameter description: Specifies whether to reuse the PBE packets of DNs delivered by the CN as planned by the XC. After this function is enabled, the packets sent by the CN to the DN in the XC plan are changed from unnamed packets to packets with the _disqry_sys_cached prefix. The packets are reused during execution, improving query performance.

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: If this parameter is disabled, the XC plan query performance will be affected in some scenarios.

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.

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

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

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

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

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.

enable_stream_concurrent_update

Parameter description: Specifies the optimizer's use of streams in concurrent update scenarios. This parameter is restricted by the enable_stream_operator parameter. If concurrent update conflicts occur, an error may be reported. This parameter can be used to control the conflict.

Parameter type: Boolean.

Unit: none

Value range:

• 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

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 generates only non-stream plans for update statements. Therefore, you are advised to enable this parameter.

enable_stream_operator

Parameter description: Specifies whether the optimizer uses stream. When the enable_stream_operator parameter is disabled, 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 disable enable_unshipping_log when disabling enable_stream_operator.

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

enable_stream_recursive

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

Parameter type: Boolean.

Unit: none

Value range:

• 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

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 query performance may deteriorate.

max_recursive_times

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

Parameter type: integer.

Unit: none

Value range: 0 to 2147483647

Default value: 200

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.

Parameter type: Boolean.

Unit: none

Value range:

• on: The A matching rule is used.
• off: The POSIX matching rule is used.

Default value: on

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

Setting suggestion: Set this parameter based on the actual service scenario.

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.

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

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: The accuracy of stream plan generation and the query performance are affected.

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.

Parameter type: Boolean.

Unit: none

Value range:

• on: used.
• off: not used.

Default value: off

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.

best_agg_plan

Parameter type: integer.

Unit: none

Value range: 0 to 3.

• 0: The optimizer generates the optimal plan based on the estimated costs of the three plans.

• 1: The first plan is forcibly generated.
• 2: The second plan is forcibly generated if the group by column can be redistributed. Otherwise, the first plan is generated.
• 3: The third plan is forcibly generated if the group by column can be redistributed. Otherwise, the first plan is generated.

Default value: 0

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 affects the hashagg generation mode. You are advised to use the default value. If this parameter is set to another value, the corresponding hashagg generation mode is preferred. If this parameter is set to an improper value, the query performance in some scenarios may be affected.

agg_redistribute_enhancement

Parameter description: When the aggregate operation is performed, which contains multiple group by columns and all of the columns are not in the distribution key, you need to select one group by column for redistribution. This parameter specifies the policy of selecting a redistribution key.

Parameter type: Boolean.

Unit: none

Value range:

• on: The column that can be redistributed with the most distinct value evaluated is selected for redistribution.
• off: The first column that can be redistributed is selected for redistribution.

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.

Risks and impacts of improper settings: If this parameter is enabled, the plan generation time increases, but a more reasonable redistribution key may be selected.

enable_absolute_tablespace

Parameter description: Specifies whether the tablespace can use the absolute path.

Parameter type: Boolean.

Unit: none

Value range:

• on: An absolute path can be used.
• off: No absolute path can be 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: 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.

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

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.

expected_computing_nodegroup

Parameter description: Specifies a compute node group or the way to choose such a group. The node group mechanism is internal. 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 compute node groups are listed for the optimizer to select the most appropriate one for the current operator.

Parameter type: string.

Unit: none

Value range:

• 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

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 optimizer may fail to select a better compute node group, affecting query performance.

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 internal. You do not need to set it. This parameter takes effect only when expected_computing_nodegroup is set to a specific node group.

Parameter type: Boolean.

Unit: none

Value range:

• on: Computing workloads are assigned to the node group specified by expected_computing_nodegroup.
• off: No node group is specified for computing.

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.

Risks and impacts of improper settings: The query performance may be affected.

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.

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

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.

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 parameter is applicable only to Redistribute and Broadcast streams.

Parameter type: floating point.

Unit: none

Value range: 0 to DBL_MAX

Default value: 1

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

Setting suggestion: Retain the default value and adjust the value based on the deployment environment.

Risks and impacts of improper settings: The optimizer may incorrectly estimate the overhead of the stream operator, affecting the query performance.

qrw_inlist2join_optmode

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

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

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.

Parameter 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

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.

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

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.

cost_weight_index

Parameter description: Specifies the cost weight of index_scan.

Parameter type: floating point.

Unit: none

Value range: 1e-6 to 1e+10

Default value: 1

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.

Parameter 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

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.

Parameter type: Boolean.

Unit: none

Value range:

• on indicates that the opfusion obj memory is reused when conditions are met.
• 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, plans may be regenerated and the query performance may deteriorate.

enable_iud_fusion

Parameter description: Specifies whether to optimize the IUD statements.

Parameter type: Boolean.

Unit: none

Value range:

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

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

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

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.

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

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.