GUC parameters provided by Hologres and how to use the GUC parameters. - Hologres

To support a wide range of scenarios, Hologres provides Grand Unified Configuration (GUC) parameters. This topic describes the GUC parameters provided by Hologres and how to use the GUC parameters.

GUC parameters

Query the value of a GUC parameter

You can execute the SHOW statement to query the setting or default value of a GUC parameter. Sample statements:

Check whether the auto-analyze feature is enabled:
```
show hg_enable_start_auto_analyze_worker;
```
Query the maximum number of partitions that can be hit by each query of a MaxCompute table:
```
show hg_foreign_table_max_partition_limit;
```

Configure GUC parameters

You can configure GUC parameters at the session level or database level.

Note

You must configure GUC parameters for databases or sessions based on the business scenarios and the characteristics of the parameters. We recommend that you do not configure all GUC parameters at the database level.

Configure GUC parameters at the session level
You can use the SET statement to configure GUC parameters at the session level. If you configure GUC parameters at the session level, the parameters take effect only in the current session. After the connection is closed, the parameter settings become invalid. We recommend that you use GUC parameters followed by SQL statements.
- Syntax:
```
set <GUC_name> = <values>;
```
  The GUC_name parameter specifies the name of the GUC parameter and the values parameter specifies the value of this GUC parameter.
- Examples:
```
-- Enable the auto-analyze feature.
set hg_enable_start_auto_analyze_worker = on;

-- Change the maximum number of partitions that can be hit by each query of a MaxCompute table to 1024.
set hg_foreign_table_max_partition_limit =1024;
```
Configure GUC parameters at the database level
You can use the alter database xx set xxx statement to configure GUC parameters at the database level. The parameter settings take effect for the specified database after you close the current connection and re-establish a connection. If you create a database, you must manually configure the GUC parameters for the database.
- Syntax:
```
alter database <db_name> set <GUC_name> = <values>;
```
  The db_name parameter specifies the name of the database. The GUC_name parameter specifies the name of the GUC parameter, and the values parameter specifies the value of the GUC parameter.
- Examples:
```
-- Enable the auto-analyze feature for a database.
alter database testdb set hg_enable_start_auto_analyze_worker = on;

-- Change the maximum number of partitions that can be hit by each query of a MaxCompute table to 1,024 for a database.
alter database testdb set hg_foreign_table_max_partition_limit =1024;
```

GUC parameter	Description	Remarks	Example
hg_enable_start_auto_analyze_worker	These parameters are used to specify whether to enable the auto-analyze feature and configure the auto-analyze feature. For more information, see ANALYZE and auto-analyze.	The default value is `on` for Hologres V1.1 and later.	set hg_enable_start_auto_analyze_worker = on;
hg_auto_check_table_changes_interval		Default value: `10min`.	set hg_auto_check_table_changes_interval = '10min';
hg_auto_check_foreign_table_changes_interval		Default value: `4h`.	set hg_auto_check_foreign_table_changes_interval = '4h';
hg_auto_analyze_max_sample_row_count		Default value: `16777216`.	set hg_auto_analyze_max_sample_row_count = 16777216;
hg_fixed_api_modify_max_delay_interval		Default value: `3day`.	set hg_fixed_api_modify_max_delay_interval = '3day';
hg_foreign_table_max_partition_limit	This parameter specifies the maximum number of partitions that can be hit by each query of a MaxCompute table.	Default value: `512`. Valid values: `0 to 1024`.	set hg_foreign_table_max_partition_limit = 128;
hg_experimental_query_batch_size	These parameters are used to optimize the performance of querying MaxCompute tables in Hologres. For more information, see Optimize the performance of querying MaxCompute tables in Hologres.	Default value: `8192`.	set hg_experimental_query_batch_size = 4096;
hg_foreign_table_split_size		Default value: `64`. We recommend that you do not set this parameter to an excessively large value.	set hg_foreign_table_split_size = 128;
hg_foreign_table_executor_max_dop		The default value of this parameter is the same as the number of CPU cores of the Hologres instance. The maximum value is `128`.	set hg_foreign_table_executor_max_dop = 32;
hg_foreign_table_executor_dml_max_dop		Default value: `32`.	set hg_foreign_table_executor_dml_max_dop = 16;
hg_enable_access_odps_orc_via_holo		The default value is `on` for Hologres V1.1 and later.	set hg_enable_access_odps_orc_via_holo = on;
hg_experimental_enable_result_cache	This parameter specifies whether to enable result caching.	Default value: `on`. We recommend that you do not disable the result caching feature.	set hg_experimental_enable_result_cache = on;
optimizer_join_order	These parameters are used to optimize the performance of querying internal tables. For more information, see Optimize performance of queries on Hologres internal tables.	Default value: `exhaustive`. You can use this parameter followed by an SQL query statement.	set optimizer_join_order = query;
optimizer_enable_motion_broadcast		Default value: `on`. You can set this parameter to off based on your business requirements.	set optimizer_enable_motion_broadcast = off;
optimizer_enable_motion_redistribute		Default value: `on`. You can set this parameter to off based on your business requirements.	set optimizer_enable_motion_redistribute =off;
optimizer_force_multistage_agg		Default value: `off`. You can set this parameter to on based on your business requirements.	set optimizer_force_multistage_agg = on;
hg_anon_enable	This parameter specifies whether to enable the data masking feature. For more information, see Mask data.	Default value: `off`. We recommend that you enable the data masking feature at the database level based on your business requirements.	alter database <db_name> set hg_anon_enable = on;
hg_experimental_encryption_options	This parameter specifies whether to enable data encryption and is used to configure the data encryption feature. For more information, see Encrypt data in Hologres.	Default value: `off`. We recommend that you enable the data encryption feature at the database level based on your business requirements.	alter database <db_name> set hg_experimental_encryption_options='AES256,623c26ee-xxxx-xxxx-xxxx-91d323cc4855,AliyunHologresEncryptionDefaultRole,187xxxxxxxxxxxxx';
statement_timeout	This parameter specifies the timeout period of active queries. For more information, see Manage queries.	Default value: `8h`. We recommend that you configure the time granularity at the session level based on your business requirements.	set statement_timeout = 5000 ;
idle_in_transaction_session_timeout	This parameter specifies the timeout period of idle queries. For more information, see Manage queries.	Default value: `10min`. We recommend that you configure this parameter at the database level. Otherwise, if transaction leaks occur, the database is locked.	alter database db_name set idle_in_transaction_session_timeout=300000;
idle_session_timeout	This parameter specifies the timeout period after which an idle connection is automatically released. For more information, see Manage connections.	Default value: `0`, which indicates that an idle connection is not automatically released. We recommend that you do not use the default value. Otherwise, the number of connections to your Hologres instance may reach the upper limit and connection leaks occur.	alter database <db_name> SET idle_session_timeout = 600000;
hg_experimental_functions_use_pg_implementation	This parameter specifies the time range for data type conversion functions that are supported by Hologres. By default, the time range supported by the `to_char`, `to_date`, and `to_timestamp` functions is `1925 to 2282`. You can configure this parameter to extend the time range to 0000 to 9999. For more information, see Data type conversion functions.	Hologres V1.1.31 and later support this parameter. After you configure this parameter for a data type conversion function, the time range supported by the function is `0000 to 9999`.	set hg_experimental_functions_use_pg_implementation = 'to_char';
hg_experimental_approx_count_distinct_precision	This parameter is used to adjust the margin of error for the APPROX_COUNT_DISTINCT function. For more information, see APPROX_COUNT_DISTINCT.	Default value: `17`. Valid values: `12 to 20`.	set hg_experimental_approx_count_distinct_precision = 20;
timezone	This parameter specifies the time zone.	Default value: `GMT-8:00`.	set timezone='GMT-8:00';
hg_experimental_enable_create_table_like_properties	This parameter specifies whether to copy both table schemas and table properties such as the primary key and index. For more information, see CREATE TABLE LIKE.	Default value: `off`.	set hg_experimental_enable_create_table_like_properties=true;
hg_experimental_affect_row_multiple_times_keep_first	The parameters specify the conflict processing policy for the `INSERT ON CONFLICT` statement when you insert a row that has the same primary key values as existing data. For more information, see INSERT ON CONFLICT(UPSERT).	Default value: `off`.	set hg_experimental_affect_row_multiple_times_keep_first = on;
hg_experimental_affect_row_multiple_times_keep_last		Default value: `off`.	set hg_experimental_affect_row_multiple_times_keep_last = on;
hg_experimental_enable_read_replica	This parameter specifies whether to enable shard-level replication and is used to configure the shard-level replication feature. For more information, see Shard-level replication for high throughout.	Default value: `on`.	set hg_experimental_enable_read_replica = on;
hg_experimental_display_query_id	This parameter specifies whether to display the query ID in the NOTICE field on the client. This parameter is valid for HoloWeb and the PostgreSQL client. If you use the Java Database Connectivity (JDBC) driver, you must use `statement.getWarnings()` to query the NOTICE field and obtain the query ID from the NOTICE field.	Default value: off.	set hg_experimental_display_query_id =on;