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

GUC parameters

Parameter Description Remarks Example
hg_enable_start_auto_analyze_worker Specifies whether to enable the auto-analyze feature and configures the auto-analyze feature. For more information, see ANALYZE and auto-analyze. Default value: on. By default, the auto-analyze feature is enabled for instances of 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 Queries 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 Optimizes 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 great 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 Default value: on. By default, the new engine for external table queries is enabled for instances of Hologres V1.1 and later. set hg_enable_access_odps_orc_via_holo = on;
hg_experimental_enable_result_cache 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 Optimizes the performance of querying internal tables. For more information, see Optimize the performance of internal table queries. 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 as needed. set optimizer_enable_motion_broadcast = off;
optimizer_enable_motion_redistribute Default value: on. You can set this parameter to off as needed. set optimizer_enable_motion_redistribute =off;
optimizer_force_multistage_agg Default value: off. You can set this parameter to on as needed. set optimizer_force_multistage_agg = on;
hg_anon_enable Specifies whether to enable the data masking feature. For more information, see Data masking (Beta). Default value: off. We recommend that you enable the data masking feature at the database level as needed. alter databse <db_name> set hg_anon_enable = on;
hg_experimental_encryption_options Specifies whether to enable data encryption and configures the data encryption feature for a database. For more information, see Encrypt data in Hologres. Default value: off. We recommend that you enable the data encryption feature at the database level as needed. alter databse <db_name> set hg_experimental_encryption_options='AES256,623c26ee-xxxx-xxxx-xxxx-91d323cc4855,AliyunHologresEncryptionDefaultRole,187xxxxxxxxxxxxx';
statement_timeout Specifies the timeout period of active queries. For more information, see Manage queries. Default value: 8h. We recommend that you set the time granularity based on different business and sessions. set statement_timeout = 5000 ;
idle_in_transaction_session_timeout Specifies the timeout period of idle queries. For more information, see Manage queries. Default value: 10min. We recommend that you set this parameter at the database level. Otherwise, if transaction leakage occurs, a specific database is locked. alter database db_name set idle_in_transaction_session_timeout=300000;
idle_session_timeout 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 lead to connection leaks. alter database <db_name> SET idle_session_timeout = 600000;
hg_experimental_functions_use_pg_implementation Extends the time range for the 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 set this parameter to extend the time range to 0000 to 9999. For more information, see Data type conversion functions. You can set this parameter for instances of Hologres V1.1.31 and later. After you set 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 Adjusts 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 Specifies the time zone. Default value: GMT-8:00. set timezone='GMT-8:00';
hg_experimental_enable_create_table_like_properties 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 Specifies whether to execute the INSERT ON CONFLICT statement to set the policy that is used to deal with conflicts when you insert a row that shares the same primary key value with an existing row. For more information, see INSERT ON CONFLICT. Default value: off. set hg_experimental_affect_row_multiple_times_keep_first = on;
hg_experimental_affect_row_multiple_times_keep_last set hg_experimental_affect_row_multiple_times_keep_last = on;
hg_experimental_enable_read_replica Specifies whether to enable shard-level replication and configures the shard-level replication feature to improve the availability of queries. For more information, see Shard-level replication for high throughout (Beta). Default value: on. set hg_experimental_enable_read_replica = on;

Query the value of a GUC parameter

You can execute the SHOW statement to query the value of a GUC parameter. The following part provides two examples:
  • Execute the following statement to check whether the auto-analyze feature is enabled for the specified database:
    show hg_enable_start_auto_analyze_worker;
  • Execute the following statement to view the maximum number of partitions that can be hit by each query of a MaxCompute table:
    show hg_foreign_table_max_partition_limit;

Set GUC parameters

You can specify that GUC parameters take effect at the database level or the session level.
Note You must set GUC parameters for databases or sessions based on the business scenarios and the characteristics of the parameters. We recommend that you do not specify that GUC parameters take effect only at the database level.
  • Set GUC parameters for a session
    You can use the SET statement to set GUC parameters at the session level. If you set GUC parameters for a session, they take effect only in the current session. After the connection is closed, the parameters 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 for the current session.
      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 for the current session.
      set hg_foreign_table_max_partition_limit =1024;
  • Set GUC parameters for a database
    You can use the alter database xx set xxx statement to set GUC parameters at the database level. After you set this parameter for a data type conversion function, the GUC parameters take effect for the specified database. The settings of the GUC parameters take effect after you close the current connection and re-establish a connection. If you create a database, you must manually set 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 the specified 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 1024 for the specified database.
      alter database testdb set hg_foreign_table_max_partition_limit =1024;