Other Optimizer Options

cost_model_version

Parameter description: Specifies the version of the optimizer cost model. It can be regarded as a protection parameter to disable the latest optimizer cost model and keep consistent with the plan of the earlier version. Changing the value of this parameter may change many SQL plans. Therefore, exercise caution when changing the value of this parameter.

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

Value range: 0, 1, 2, or 3

• 0 indicates that the latest cost estimation model is used. The current version is equivalent to 3.
• 1 indicates that the original cost estimation model is used.
• 2: indicates that the enhanced COALESCE expression, hash join cost, and semi/anti join cost are used for estimation on the basis of 1.
• 3: indicates that the boundary correction estimator is used to estimate the NDV on the basis of 2. The hint of indexscan can be applied to indexonlyscan.

Default value: 0

enable_fast_query_shipping

Parameter description: Specifies whether to use the distributed framework for a query optimizer.

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

Value range: Boolean

• on indicates that the distributed framework is not used. Execution plans are generated on CNs and DNs separately.
• off indicates that the distributed framework is used. Execution plans are generated on CNs and then sent to DNs for execution.

Default value: on

enable_trigger_shipping

Parameter description: Specifies whether the trigger can be pushed to DNs for execution.

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

Value range: Boolean

• on indicates that the trigger can be pushed to DNs for execution.
• off indicates that the trigger cannot be pushed to DNs. It must be executed on CNs.

Default value: on

enable_remotejoin

Parameter description: Specifies whether JOIN operation plans can be delivered to DNs for execution.

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

Value range: Boolean

• on indicates that JOIN operation plans can be delivered to DNs for execution.
• off indicates that JOIN operation plans cannot be delivered to DNs for execution.

Default value: on

enable_remotegroup

Parameter description: Specifies whether the execution plans of GROUP BY and AGGREGATE can be delivered to DNs for execution.

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

Value range: Boolean

• on indicates that the execution plans of GROUP BY and AGGREGATE can be delivered to DNs for execution.
• off indicates that the execution plans of GROUP BY and AGGREGATE cannot be delivered to DNs for execution.

Default value: on

enable_remotelimit

Parameter description: Specifies whether the execution plan specified in the LIMIT clause can be delivered to DNs for execution.

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

Value range: Boolean

• on indicates that the execution plan specified in the LIMIT clause can be pushed down to DNs for execution.
• off indicates that the execution plan specified in the LIMIT clause cannot be delivered to DNs for execution.

Default value: on

enable_remotesort

Parameter description: Specifies whether the execution plan of the ORDER BY clause can be delivered to DNs for execution.

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

Value range: Boolean

• on indicates that the execution plan of the ORDER BY clause can be delivered to DNs for execution.
• off indicates that the execution plan of the ORDER BY clause cannot be delivered to DNs for execution.

Default value: on

enable_csqual_pushdown

Parameter description: Specifies whether to deliver filter criteria for a rough check during query.

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

Value range: Boolean

• on indicates that a rough check is performed with filter criteria delivered during query.
• off indicates that a rough check is performed without filter criteria delivered during query.

Default value: on

explain_dna_file

Parameter description: Sets explain_perf_mode to run to export object files in CSV format.

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

The value of this parameter must be an absolute path plus a file name with the extension .csv.

Value range: a string

Default value: empty

analysis_options

Parameter description: Specifies whether to enable function options in the corresponding options to use the corresponding location functions, including data verification and performance statistics. For details, see the options in the value range.

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

Value range: a string

• HASH_CONFLICT indicates that the log file in the pg_log directory of the DN process displays the hash table statistics, including the hash table size, hash chain length, and hash conflict information.
• STREAM_DATA_CHECK indicates that a CRC check is performed on data before and after network data transmission.

Default value: ALL,on(),off(HASH_CONFLICT,STREAM_DATA_CHECK), which indicates that no location function is enabled.

explain_perf_mode

Parameter description: Specifies the display format of the explain command.

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

Value range: normal, pretty, summary, and run

• normal indicates that the default printing format is used.
• pretty indicates a new format improved by using GaussDB. The new format contains a plan node ID, directly and effectively analyzing performance.
• summary indicates that the analysis result on this information is printed in addition to the printed information specified by pretty.
• run indicates that the system exports the printed information specified by summary as a CSV file for further analysis.

Default value: pretty

The pretty mode supports only plans that contain stream operators and does not support plans that deliver statements to DNs. Therefore, the display format is affected by the enable_stream_operator parameter. When enable_stream_operator is set to off, the plan containing the stream operator cannot be generated.

cost_param

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

Value range: an integer ranging from 0 to 2147483647

Default value: 0

enable_partitionwise

Parameter description: Specifies whether to select an intelligent algorithm for joining partition tables.

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

Value range: Boolean

• on indicates that an intelligent algorithm is selected.
• off indicates that an intelligent algorithm is not selected.

Default value: off

enable_fast_numeric

Parameter description: Specifies whether to enable optimization for numeric data calculation. Calculation of numeric data is time-consuming. Numeric data is converted into int64- or int128-type data to improve numeric data calculation performance.

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

Value range: Boolean

• on or true indicates that optimization for numeric data calculation is enabled.
• off or false indicates that optimization for numeric data calculation is disabled.

Default value: on

rewrite_rule

Parameter description: Specifies the rewriting rule for enabled optional queries. Some query rewrite rules are optional. Enabling them cannot always improve the query efficiency. In a specific customer scenario, you can set the query rewriting rules through this GUC parameter to achieve optimal query efficiency.

This parameter can control the combination of query rewriting rules, for example, there are multiple rewriting rules: rule1, rule2, rule3, and rule4. You can perform the following settings:

set rewrite_rule=rule1;          -- Enable query rewriting rule rule1
set rewrite_rule=rule2, rule3;     -- Enable the query rewriting rules rule2 and rule3
set rewrite_rule=none;         -- Disable all optional query rewriting rules

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

Value range: a string

• none: Does not use any optional query rewriting rules
• Lazyagg: Uses the Lazy Agg query rewriting rules for eliminating aggregation operations in subqueries
• magicset: The Magic Set query rewriting rules are used to associate subqueries which have aggregation operators with the main query in advance to reduce repeated scanning of sublinks.
• partialpush: Uses the Partial Push query rewriting rules. For statements that cannot be pushed down, push down some subqueries to DNs for execution and the rest to CNs for execution.
• uniquecheck: Uses the Unique Check query rewriting rules. Optimize the subquery statements in target columns without agg and check whether the number of returned rows is 1.
• disablerep: Uses the Disable Replicate query rewriting rules. The performance may deteriorate after a replication table is optimized. Therefore, after this rule is enabled, subqueries cannot be optimized.
• intargetlist: Uses the In Target List query rewriting rules (subquery optimization in the target column).
• predpushnormal: Uses the Predicate Push query rewriting rules. When predicate conditions are pushed down to subqueries, the BROADCAST operator may be added to support distributed execution.
• predpushforce: Uses the Predicate Push query rewriting rules. Push down predicate conditions to subqueries and use indexes as much as possible for acceleration.
• predpush: Selects the optimal plan based on the cost in predpushnormal and predpushforce.
• disable_pullup_expr_sublink: The optimizer is not allowed to pull up sublinks of the expr_sublink type. For details about sublink classification and pull-up principles, see section "SQL Tuning Guide > Typical SQL Tuning Methods > Subquery Tuning" in Developer Guide.
• enable_sublink_pullup_enhanced: Enhanced sublink query rewriting rules are used, including unrelated sublink pull-up of the WHERE and HAVING clauses and WinMagic rewriting optimization.
• disable_pullup_not_in_sublink: The optimizer is not allowed to pull up sublinks related to NOT IN. For details about sublink classification and pull-up principles, see section "SQL Tuning Guide > Typical SQL Tuning Methods > Subquery Tuning" in Developer Guide.

Default value: magicset

enable_pbe_optimization

Parameter description: Specifies whether the optimizer optimizes the query plan for statements executed in Parse Bind Execute (PBE) mode.

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

Value range: Boolean

• on indicates that the optimizer optimizes the query plan for statements executed in PBE mode.
• off indicates that the optimizer does not optimize the execution.

Default value: on

enable_light_proxy

Parameter description: Specifies whether the optimizer optimizes the execution of simple queries on CNs. This parameter does not take effect if the character set of the application side does not match that of the kernel side. You are advised to set the character set to UTF8 when creating a database.

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

Value range: Boolean

• on indicates that the optimizer optimizes the execution of simple queries on CNs.
• off indicates that the optimizer does not optimize the execution.

Default value: on

enable_global_plancache

Parameter description: Specifies whether to share the cache for the execution plans of statements in PBE queries and stored procedures. If this parameter is set to on, the memory usage of the CNs and DNs in high concurrency scenarios can be reduced. In addition, the value of this parameter must be the same on the CN and DN. Otherwise, the packets sent from the CN to the DN do not match and an error is reported.

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

When enable_global_plancache is enabled, the default value of local_syscache_threshold is greater than or equal to 16 MB to ensure that GPC takes effect. If the value of local_syscache_threshold is less than 16 MB, set it to 16 MB. If the value is greater than 16 MB, do not change it.

Value range: Boolean

• on indicates that cache sharing is enabled for the execution plans of statements in PBE queries and stored procedures.
• off indicates no sharing.

Default value: off

gpc_clean_timeout

Parameter description: When enable_global_plancache is set to on, if a plan in the shared plan list is not used within the period specified by gpc_clean_timeout, the plan will be deleted. This parameter is used to control the retention period of a shared plan that is not used.

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

Value range: an integer ranging from 300 to 86400. The unit is s.

Default value: 1800, that is, 30 minutes

enable_gpc_grayrelease_mode

Parameter description: Specifies whether to enable GPC in a distributed cluster. The cluster needs to be restarted to enable GPC. If you want to enable GPC without restarting the cluster, use enable_gpc_grayrelease_mode.

Operations in a distributed cluster:

To enable GPC:

1. Enable enable_gpc_grayrelease_mode on all DNs.

2. Enable enable_gpc_grayrelease_mode on all CNs.

3. Enable the GPC parameter which is a POSTMASTER parameter. You need to reload the parameter and then kill the node in polling mode for GPC on the restarted node to take effect.

To disable GPC:

1. Ensure that enable_gpc_grayrelease_mode is set to on, reload and then disable the GPC parameter, and kill the node in polling mode for GPC on the restarted node to take effect.

2. Disable enable_gpc_grayrelease_mode on all CNs.

3. Disable enable_gpc_grayrelease_mode on all DNs.

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

Value range: Boolean

• on
• off

Default value: off

enable_opfusion

Parameter description: Specifies whether to optimize simple queries.

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

This parameter is used to optimize the query performance of DNs. You can set max_datanode_for_plan to view the execution plan of a query DN. If the execution plan of the DN contains [Bypass], the query can be optimized on the DN.

Value range: Boolean

• on indicates that the performance logs are output.
• off indicates that the performance logs are not output.

Default value: on

enable_partition_opfusion

Parameter description: If this parameter is enabled when the enable_opfusion parameter is enabled, the simple query of the partitioned table can be optimized to improve the SQL execution performance. If enable_global_plancache is set to on, this parameter does not take effect.

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

Value range: Boolean

• on indicates that the performance logs are output.
• off indicates that the performance logs are not output.

Default value: off

sql_beta_feature

Parameter description: Specifies the SQL engine's optional beta features to be enabled, including optimization of row count estimation and query equivalence estimation. These optional features provide optimization for specific scenarios, but performance deterioration may occur in some scenarios for which testing is not performed. In a specific customer scenario, you can set the query rewriting rules through this GUC parameter to achieve optimal query efficiency.

This parameter determines the combination of the SQL engine's beta features, for example, feature1, feature2, feature3, and feature4. You can perform the following settings:

set sql_beta_feature=feature1;          --Enable the beta feature 1 of the SQL engine.
set sql_beta_feature=feature2,feature3;    --Enable the beta features 2 and 3 of the SQL engine.
set sql_beta_feature=none;           --Disable all optional SQL engine beta features.

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

Value range: a string

• none: None of the beta optimizer features are used.
• sel_semi_poisson: Uses poisson distribution to calibrate the equivalent semi-join and anti-join selection rates.
• sel_expr_instr: Uses the matching row count estimation method to provide more accurate estimation for instr(col, 'const') > 0, = 0, = 1.
• param_path_gen: Generates more possible parameterized paths.
• rand_cost_opt: Optimizes the random read cost of tables that have a small amount of data.
• param_path_opt: Uses the bloating ratio of the table to optimize the analysis information of indexes.
• page_est_opt: Optimizes the relpages estimation for the analysis information of table indexes.
• no_unique_index_first: Disables optimization of the primary key index scanning path first.
• join_sel_with_cast_func: Supports type conversion functions when the number of join rows is estimated.
• canonical_pathkey: The regularization pathkey is generated in advance (pathkey: a set of ordered key values of data).
  

  After this parameter is enabled, the semantics of the output data of statements such as ORDER BY may be different from that of the standard ones in the case of outer join. Contact Huawei engineers to determine whether to enable this parameter.

• index_cost_with_leaf_pages_only: Considers index leaf nodes when the index cost is estimated.
• partition_opfusion: Enables partitioned table optimization.
• a_style_coerce: enables the Decode type conversion rule to be compatible with O. For details, see the part related to case processing in ORA compatibility mode in section "SQL Reference > Type Conversion > UNION, CASE, and Related Constructs" in Developer Guide.
• plpgsql_stream_fetchall: enables the function of obtaining all tuple results when the SQL statements which use streams are executed on the for loop or cursor in a stored procedure.
• partition_fdw_on: SQL statements can be created for Postgres foreign tables based on partitioned tables.
• predpush_same_level: Enables the predpush hint to control parameterized paths at the same layer.
• disable_bitmap_cost_with_lossy_pages: disables the computation of the cost of lossy pages in the bitmap path cost.
• enable_upsert_execute_gplan: In the PBE scenario, if the UPDATE clause in the on duplicate key update statement contains parameters, set enable_upsert_execute_gplan to allow execution through gplan.

Default value: "sel_semi_poisson,sel_expr_instr,rand_cost_opt,param_path_opt,page_est_opt"

table_skewness_warning_threshold

Parameter description: Specifies the threshold for triggering a table skew alarm.

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

Value range: a floating point number ranging from 0 to 1

Default value: 1

table_skewness_warning_rows

Parameter description: Specifies the minimum number of rows for triggering a table skew alarm.

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

Value range: an integer ranging from 0 to 2147483647

Default value: 100000

enable_global_stats

Parameter description: Specifies the current statistics collection mode, which can be global statistics collection or single-node statistics collection. By default, the global statistics collection mode is used. If this parameter is disabled, the statistics of the first node in the cluster are collected by default. In this case, the quality of the generated query plan may be affected. However, the information collection performance is optimal. Therefore, exercise caution when disabling this parameter.

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

Value range: Boolean

• on or true indicates the global statistics mode.
• off or false indicates the single-DN statistics mode.

Default value: on

default_statistics_target

Parameter description: Specifies the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS. If this parameter is set to a positive number, it indicates the number of samples of statistics information. If this parameter is set to a negative number, percentage is used to set the statistic target. The negative number converts to its corresponding percentage, for example, -5 means 5%.

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

Value range: an integer ranging from –100 to 10000

• A larger positive number than the default value increases the time required to do ANALYZE, but might improve the quality of the optimizer's estimates.
• Changing settings of this parameter may result in performance deterioration. If query performance deteriorates, you can:
  1. Restore to the default statistics.
  2. Use hints to force the optimizer to use the optimal query plan. For details, see section "SQL Tuning Guide > Tuning Using Plan Hints" in Developer Guide.
• If this GUC parameter is set to a negative value, the number of samples is greater than or equal to 2% of the total data volume, and the number of records in user tables is less than 1.6 million, the time taken by running ANALYZE will be longer than that when this parameter uses its default value.
• If this GUC parameter is set to a negative value, the auto-analyze function is disabled.

Default value: 100

default_gsi_statistics_target

Parameter description: Sets the default statistics target for global secondary indexes. If the parameter is set to a positive number, it indicates the number of samples of statistics information. If the parameter is set to a negative number, it indicates the percentage of statistics collected. The negative number converts to its corresponding percentage, for example, –5 means 5%.

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

Value range: an integer, in the range from –100 to 3000000.

Default value: 0

• The default value 0 indicates that the default sampling rate is used for sampling.
• Changing settings of this parameter may result in performance deterioration. If query performance deteriorates, you can:
  1. Restore to the default statistics.
  2. Use hints to force the optimizer to use the optimal query plan. For details, see section "SQL Tuning Guide > Tuning Using Plan Hints" in Developer Guide.

auto_statistic_ext_columns

Parameter description: Collects statistics about multiple columns based on the first K columns of the composite index in the data table. This GUC parameter indicates K. For example, if a composite index is (a,b,c,d,e) and the GUC parameter is set to 3, statistics about multiple columns are generated on columns (a,b) and (a,b,c). Multi-column statistics can make the optimizer estimate the cardinality more accurate when querying with combined conditions.

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

• The system catalog does not take effect.
• The statistics take effect only when the types of all columns support the comparison functions '=' and '<'.
• System pseudocolumns in indexes, such as tableoid and ctid, are not collected.
• By default, distinct values, MCVs without NULL, and MCVs with NULL are collected. If the AI-based cardinality estimation parameter enable_ai_stats is set to on, MCVs are not collected. Instead, models for AI-based cardinality estimation are collected.
• If the index for creating multi-column statistics is deleted and no other index contains the multi-column combination, the multi-column statistics will be deleted in the next ANALYZE operation.
• If the value of this parameter decreases, the new index generates multi-column statistics based on the value of this parameter. The generated multi-column statistics that exceed the value of this parameter will not be deleted.
• If you want to disable the multi-column statistics on a specific combination only, you can retain the value of this parameter and run the ALTER TABLE tablename disable statistics ((column list)) DDL command to disable the statistics on multiple columns in a specific combination.

Value range: an integer ranging from 1 to 4 The value 1 indicates that statistics about multiple columns are not automatically collected.

Default value: 1

constraint_exclusion

Parameter description: Controls the query optimizer's use of table constraints to optimize queries.

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

Value range: enumerated values

• on, true, yes, and 1 indicate that constraints for all tables are examined.
• off, false, no, and 0 indicate that no constraints are examined.
• partition indicates that only constraints for inheritance child tables and UNION ALL subqueries are examined.
  

  When constraint_exclusion is set to on, the optimizer compares query conditions with the table's CHECK constraints, and omits scanning tables for which the conditions contradict the constraints.

Default value: partition

Currently, constraint_exclusion is enabled by default only for cases that are often used to implement table partitioning. Turning this feature on for all tables imposes extra planning on simple queries, and provides no benefit for simple queries. If you have no partitioned tables, set it to off.

cursor_tuple_fraction

Parameter description: Specifies the optimizer's estimated fraction of a cursor's rows that are retrieved.

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

Value range: a floating point number ranging from 0.0 to 1.0

Smaller values of this setting bias the optimizer towards using fast start plans for cursors, which will retrieve the first few rows quickly while perhaps taking a long time to fetch all rows. Larger values put more emphasis on the total estimated time. At the maximum setting of 1.0, cursors are planned exactly like regular queries, considering only the total estimated time and how soon the first rows might be delivered.

Default value: 0.1

from_collapse_limit

Parameter description: Specifies whether the optimizer merges sub-queries into upper queries based on the resulting FROM list. The optimizer merges sub-queries into upper queries if the resulting FROM list would have no more than this many items.

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

Value range: an integer ranging from 1 to 2147483647

Smaller values reduce planning time but may lead to inferior execution plans.

Default value: 8

join_collapse_limit

Parameter description: Specifies whether the optimizer rewrites JOIN constructs (except FULL JOINS) into lists of FROM items based on the number of the items in the result list.

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

Value range: an integer ranging from 1 to 2147483647

• Setting this parameter to 1 prevents join reordering. As a result, the join order specified in the query will be the actual order in which the relations are joined. The query optimizer does not always choose the optimal join order. Therefore, advanced users can temporarily set this variable to 1, and then specify the join order they desire explicitly.
• Smaller values reduce planning time but lead to inferior execution plans.

Default value: 8

plan_mode_seed

Parameter description: This is a commissioning parameter. Currently, it supports only OPTIMIZE_PLAN and RANDOM_PLAN. The value 0 (for OPTIMIZE_PLAN) indicates the optimized plan using the dynamic planning algorithm. Other values are for RANDOM_PLAN, which indicates that the plan is randomly generated. –1 indicates that users do not specify the value of the seed identifier. In this case, the optimizer generates a random integer from 1 to 2147483647 and a random execution plan based on the generated integer. A GUC parameter value from 1 to 2147483647 is regarded as the seed identifier, based on which the optimizer generates a random execution plan.

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

Value range: an integer ranging from –1 to 2147483647

Default value: 0

• If this parameter is set to RANDOM_PLAN, the optimizer generates a random execution plan that may not be the optimal one. Therefore, to guarantee the query performance, the default value 0 is recommended during upgrade, scale-out, scale-in, and O&M.
• If this parameter is not set to 0, the specified plan hint will not be used.

enable_random_datanode

Parameter description: Specifies whether the query of the replication table is conducted on a random DN. A complete replication table is stored on each each DN for random retrieval to release the pressure on nodes.

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

Value range: Boolean

• on indicates that the function of querying the replication table on a random DN is enabled.
• off indicates that the function of querying the replication table on a random DN is disabled.

Default value: on

hashagg_table_size

Parameter description: Specifies the hash table size during the execution of the HASH JOIN operation.

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

Value range: an integer ranging from 0 to 1073741823

Default value: 0

enable_bloom_filter

Parameter description: Specifies whether the BloomFilter optimization is used. This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

• on indicates that the BloomFilter optimization can be used.
• off indicates that the BloomFilter optimization cannot be used.

Default value: on

enable_extrapolation_stats

Parameter description: Specifies whether the extrapolation logic is used for data of DATE type based on historical statistics. The logic can increase the accuracy of estimation for tables whose statistics are not collected in time, but will possibly provide an overlarge estimation due to incorrect extrapolation. Enable the logic only in scenarios where the data of DATE type is periodically inserted. This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

• on indicates that the extrapolation logic is used for data of DATE type based on historical statistics.
• off indicates that the extrapolation logic is not used for data of DATE type based on historical statistics.

Default value: off

autoanalyze

Parameter description: Specifies whether to automatically collect statistics on tables that have no statistics when a plan is generated. autoanalyze cannot be used for foreign or temporary tables. To collect statistics, manually perform the ANALYZE operation. If an exception occurs in the database during the execution of autoanalyze on a table, after the database is recovered, the system may still prompt you to collect the statistics of the table when you run the statement again. In this case, manually perform the ANALYZE operation on the table to synchronize statistics. This parameter is a SUSET parameter. Set it based on instructions provided in Table 1.

Value range: Boolean

• on indicates that the table statistics are automatically collected.
• off indicates that the table statistics are not automatically collected.

Default value: off

query_dop

Parameter description: Specifies the user-defined degree of parallelism (DOP). This parameter is a USERSET parameter. Set it based on instructions provided in Table 1.

Value range: an integer ranging from –64 to 64

A value ranging from 1 to 64 indicates that the fixed SMP is enabled and the system will use the specified DOP.

0 indicates that the SMP adaptation is enabled, and the system will dynamically select the optimal DOP based on resource usage and plan characteristics.

A value ranging from –64 to –1 indicates that the SMP adaptation is enabled, and the system limits the DOP that can be adaptively selected.

• After enabling concurrent queries, ensure you have sufficient CPU, memory, network, and I/O resources to achieve the optimal performance.
• To prevent performance deterioration caused by an overly large value of query_dop, the system calculates the maximum number of available CPU cores for a DN and uses the number as the upper limit for this parameter. If the value of query_dop is greater than 4 and also the upper limit, the system resets query_dop to the upper limit.

Default value: 1

enable_analyze_check

Parameter description: Checks whether statistics were collected about tables whose reltuples and relpages are displayed as 0 in pg_class during plan generation.

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

Value range: Boolean

• on indicates that the tables will be checked.
• off indicates that the tables will not be checked.

Default value: off

enable_sonic_hashagg

Parameter description: Specifies whether to use the hash aggregation operator designed for column-oriented hash tables when certain constraints are met.

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

Value range: Boolean

• on indicates that the hash aggregation operator designed for column-oriented hash tables is used when certain constraints are met.
• off indicates that the hash aggregation operator designed for column-oriented hash tables is not used.

• If enable_sonic_hashagg is enabled and the Hash Agg operator designed based on the column-oriented hash table is used when the query meets the constraint condition, the memory usage of the Hash Agg operator can be reduced. However, in scenarios where the performance is significantly improved based on code generation, the performance of the operator may deteriorate.
• If enable_sonic_hashagg is enabled and the Hash Agg operator designed based on the column-oriented hash table is used when the query meets the constraint condition, the operator is displayed as Sonic Hash Aggregation in the execution plan and execution information of Explain Analyze/Performance; when the query does not meet the constraint condition, the operator is displayed as Hash Aggregation. For details, see section "SQL Tuning Guide > Introduction to the SQL Execution Plan > Description" in Developer Guide.

Default value: on

enable_sonic_hashjoin

Parameter description: Specifies whether to use the hash join operator designed for column-oriented hash tables when certain constraints are met.

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

Value range: Boolean

• on indicates that the hash join operator designed for column-oriented hash tables is used when certain constraints are met.
• off indicates that the hash join operator designed for column-oriented hash tables is not used.

• Currently, the parameter can be used only for Inner Join.
• If enable_sonic_hashjoin is enabled, the memory usage of query using the Hash Inner operator can be reduced. However, in scenarios where the code generation technology can significantly improve performance, the performance of the operator may deteriorate.
• If enable_sonic_hashjoin is enabled and the Hash Join operator designed based on the column-oriented hash table is used when the query meets the constraint condition, the operator is displayed as Sonic Hash Join in the execution plan and execution information of Explain Analyze/Performance; when the query does not meet the constraint condition, the operator is displayed as Hash Join. For details, see section "SQL Tuning Guide > Introduction to the SQL Execution Plan > Description" in Developer Guide.

Default value: on

enable_sonic_optspill

Parameter description: Specifies whether to optimize the number of files to be written to disks for the Hash Join operator designed for column-oriented hash tables. If this parameter is set to on, the number of files written to disks does not increase significantly when the Hash Join operator writes a large number of files to disks.

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

Value range: Boolean

• on indicates that the optimization is enabled.
• off indicates that the optimization is disabled.

Default value: on

plan_cache_mode

Parameter description: Specifies the policy for generating an execution plan in the prepare statement.

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

Value range: enumerated values

• auto indicates that the custom plan or generic plan is selected by default.
• force_generic_plan indicates that the generic plan (soft parse) is forcibly used. The generic plan is a plan generated after you run a prepared statement. The plan policy binds parameters to the plan when you run the EXECUTE statement and execute the plan. The advantage of this plan is that repeated optimizer overheads can be avoided in each execution. The disadvantage is that the plan may not be optimal when data skew occurs for the bound parameters and may result in poor plan execution performance.
• force_custom_plan indicates that the custom plan (hard parse) is forcibly used. The custom plan is a plan generated after you run a prepared statement where parameters in the EXECUTE statement are embedded. The custom plan generates a plan based on specific parameters in the EXECUTE statement. This plan generates a preferred plan based on specific parameters each time and has good execution performance. The disadvantage is that the plan needs to be regenerated before each execution, resulting in a large amount of repeated optimizer overhead.

This parameter is valid only for prepared statements. It is used when the parameterized field in a prepared statement has severe data skew.

Default value: auto

enable_router

Parameter description: Specifies whether to enable the manual node pushdown function.

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

Value range: Boolean

• on indicates that the performance logs are output.
• off indicates that the performance logs are not output.

Default value: off

router

Parameter description: Controls the detailed attributes of the router function. This parameter is valid only when enable_router and enable_light_proxy are enabled. This parameter is used to calculate the DN where the given distribution column is located based on the hash distribution column of the table. After the router is set, the supported SQL statements are pushed down to the DN for execution. If the router is incorrectly configured, data may be saved to an incorrect DN, causing unpredictable problems. Therefore, be cautious when running this command.

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

Value range: a string

This parameter consists of two parts: 'schema_name.table_name,"distribute_keys"'. The meanings are as follows:

• schema_name.table_name: indicates the schema name and table name. If schema_name is not set, the default value current_schema is used.
• distribute_keys: Values of all distribution columns in the distribution table are separated by commas (,). The sequence of the values must be the same as that of the distribution columns in the table.

Default value: empty

enable_auto_explain

Parameter description: Specifies whether to enable the function of automatically printing execution plans. This parameter is used to locate slow stored procedures or slow queries and is valid only for the currently connected CN.

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

Value range: Boolean. The value on indicates that the function is enabled, and the value off indicates that the function is disabled.

Default value: off

auto_explain_level

Parameter description: Specifies the log level for automatically printing execution plans.

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

Value range: Enumeration type. The value can be log or notice. log indicates that the execution plan is printed in logs. notice indicates that the execution plan is printed in notification mode.

Default value: log

auto_explain_log_min_duration

Parameter description: Specifies the minimum duration of execution plans that are automatically printed. Only execution plans whose duration is greater than the value of auto_explain_log_min_duration will be printed.

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

Value range: an integer ranging from 0 to 2147483647. The unit is ms.

• 0: All executed plans are printed.
• 3000: All execution plans will be printed if the execution of a statement takes more than 3000 ms.

Default value: 0

max_datanode_for_plan

Parameter description: Specifies the number of execution plans to be displayed on the DN when an FQS plan is generated. The number of plans that are displayed on the DN is determined by the smaller value between the number of DNs on the cluster and the value of this parameter.

For statements executed by PBE, only plans generated in kernel prepared precompilation mode can be displayed. Plans generated in JDBC precompilation mode cannot be displayed.

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

Value range: an integer ranging from 0 to 8192

Default value: 0

enable_inner_unique_opt

Parameter description: Specifies that Inner Unique is optimized for nested loop join, hash join, and sort merge join. That is, the number of matching times is reduced when the attribute corresponding to the inner table in the join condition meets the uniqueness constraint.

This parameter 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_optimization

Parameter description: Specifies whether to optimize B-tree index scanning (IndexScan and IndexOnlyScan) in the Astore engine.

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

Value range: Boolean

• on: used.
• off: not used.

Default value: off

session_sequence_cache

Parameter description: Specifies the sequence value applied for one-time interaction in the current session. The unused values are automatically discarded after the session ends. When using sequence to import data in batches, you can increase the value of this parameter to improve the insertion speed and high concurrency performance. When a single data record is inserted concurrently, set this parameter to 1 to reduce the sequence change. If you have high requirements on continuity, you need to specify the required cache when creating a sequence. If the value of this parameter is greater than that of cache, the value automatically becomes invalid.

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

Value range: an integer ranging from 1 to 2147483647

Default value: 10

The default value is 10. In high-concurrency scenarios, the performance of single and batch insertion is good.

enable_dynamic_samplesize

Parameter description: Specifies whether to dynamically adjust the number of sampled rows. For a large table with more than one million rows, the number of sampled rows is dynamically adjusted during statistics collection to improve statistics accuracy.

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

Value range: Boolean

• on: indicates that this function is enabled.

• off: indicates that the function is disabled.

Default value: on

The function of dynamically adjusting the number of sampled rows supports only absolute sampling.