Hologres:ANALYZE and auto-analyze

ANALYZE statements

Statistics determine whether a suitable execution plan can be generated. Hologres collects sample statistics about data distribution and characteristics, table statistics, column statistics, the number of rows, the number of columns, field width, cardinality, frequency, maximum value, minimum value, high-frequency values, and bucket distribution characteristics. These statistics can help the optimizer update the estimated cost for operator execution, narrow down the query range, and estimate the optimal join order, the memory overheads, and the degree of parallelism (DOP). This way, the optimizer can generate an optimal execution plan.

Auto-analyze feature

Hologres V0.10 and later support the auto-analyze feature. After you enable this feature, you do not need to repeatedly and manually execute ANALYZE statements. Hologres determines whether to automatically perform ANALYZE operations on relevant tables based on the number of tables that are created, the amount of written data, and the amount of modified data. This reduces the complexity of operations and prevents omissions in statistics collection caused by omitted ANALYZE statements.

• Syntax

  • The following statements are used to check whether the auto-analyze feature is enabled:

    SHOW hg_enable_start_auto_analyze_worker;  -- Syntax in Hologres V1.1 and later.
    
    SHOW hg_experimental_enable_start_auto_analyze_worker; -- Syntax in Hologres V0.10.

  • The following statements are used to enable or disable the auto-analyze feature for databases. You can execute the statements only as a superuser.

     -- Syntax in Hologres V1.1 and later.
    ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = ON; -- Enable the auto-analyze feature for the specified database. The default value of the hg_enable_start_auto_analyze_worker parameter is ON.
    ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = OFF; -- Disable the auto-analyze feature for the specified database.
    
    -- Syntax in Hologres V0.10
    ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = ON; -- Enable the auto-analyze feature for the specified database. The default value of the hg_experimental_enable_start_auto_analyze_worker parameter is ON.
    ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = OFF; -- Disable the auto-analyze feature for the specified database.

• Limits

  When you use the auto-analyze feature of Hologres, take note of the following limits:

  • The auto-analyze feature is supported only in Hologres V0.10 and later. You can view the version of your Hologres instance on the instance details page in the Hologres console. If the version of your instance is earlier than V0.10, manually upgrade your Hologres instance in the Hologres console or join a DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a DingTalk group, see Obtain online support for Hologres.

  • Only superusers can enable or disable the auto-analyze feature.

  • The auto-analyze feature has the following limits on partitioned tables:

    • Before the auto-analyze feature is implemented on child tables that have been modified, an ANALYZE operation is performed on the relevant parent table.

    • By default, during data sampling, the system can scan up to 2²⁴ (16,777,216) data entries at a time. If the total number of data entries in child tables exceeds 16,777,216, the data entries are distributed to different partitions and only data entries in specific partitions are sampled. The total number of data entries in specific partitions cannot exceed 16,777,216.

      Note

      Partitioning does not affect statistics collection on the partition key column, but affects statistics collection on columns whose data is distributed in the same way as the partition key column, such as a column that has the same data as the partition key column. If such columns exist, some data in these columns may not be sampled and the number of data entries may be inaccurate. If you have questions, contact technical support in the DingTalk group (ID 32314975). The technical support team can help you adjust the maximum number of data entries that can be scanned at a time based on your business requirements.

  • By default, you can use the auto-analyze feature to collect statistics about up to 256 columns at a time. If a table has more than 256 columns, statistics about the first 256 columns are collected. You can change the value of the hg_experimental_auto_analyze_max_columns_count parameter to adjust the upper limit.

  • By default, each worker used by the auto-analyze feature can occupy 4 GB of memory. If a table has ultra-wide columns, an ANALYZE operation may fail due to the lack of memory resources for data sampling. You can change the value of the auto_analyze_work_memory_mb parameter to adjust the upper limit. However, you must pay attention to the size of the remaining memory available for your Hologres instance. The selection of a more advanced instance type for your Hologres instance allows for a greater number of workers, and the auto-analyze feature consumes more memory resources.

• How the auto-analyze feature works

  After you enable the auto-analyze feature, the system periodically searches for tables that require ANALYZE operations.

  • Regular tables, including internal tables such as non-partitioned tables and partitioned tables

    • The system checks for DML operations on a table at an interval of 1 minute. Such DML operations include INSERT, UPDATE, and DELETE operations and may cause data changes. If one of the following conditions is met, the system performs an ANALYZE operation to collect statistics about the table:

      • For a non-partitioned table, a DML operation is performed and more than 10% of data entries are changed. For a partitioned table, a DML operation is performed and more than 10% of data entries in the current partition are changed.

      • A TRUNCATE TABLE operation is performed to clear the table data.

      • A DDL operation is performed on the table. For example, a CREATE TABLE operation is performed to create a table, or an ALTER TABLE operation is performed to modify the table schema. CALL SET_TABLE_PROPERTY operations that are used to modify table attributes do not trigger an ANALYZE operation.

    • The system checks data changes in an internal table at an interval of 10 minutes. If more than 10% of data entries are changed compared with the previous check result, an ANALYZE operation is performed on this table at the backend.

      Note

      This prevents the system from omitting data changes caused by implicit DML operations, such as real-time data writes by using Realtime Compute for Apache Flink, Data Integration, or Holo Client.

  • Foreign tables

    • You can execute ANALYZE statements or enable the auto-analyze feature for a foreign table only if the foreign table maps to a MaxCompute table.

    • The system checks data changes and metadata changes in a foreign table at an interval of 4 hours. If the following condition is met, the system performs an ANALYZE operation to collect statistics about the table:

      The data of the MaxCompute table that the foreign table maps to is changed within 4 hours after the previous check is complete. This condition is met if the value of the last_modify_time parameter of the MaxCompute table is changed within 4 hours after the previous check is complete.

  Note

  A check and its corresponding ANALYZE operation are in the same scheduling task. A check can start only if the previous ANALYZE operation is complete and the interval between two consecutive checks specified by the scheduling task elapses.

• Set the parameters of the auto-analyze feature

  After you enable the auto-analyze feature, Hologres periodically checks tables to determine whether to perform ANALYZE operations on the tables. If ANALYZE operations are performed, data is sampled and statistics are collected. This consumes the resources of your Hologres instance.

  The default settings may not apply to specific business scenarios. For example, if data is not frequently written to your Hologres instance, you can decrease the frequency of automatic ANALYZE operations by modifying the default settings of the auto-analyze feature. You can modify the default settings of the auto-analyze feature based on your business requirements. This allows you to optimize the feature performance in a fine-grained manner.

  Note

  You can modify the default settings as a superuser. When you enable this feature, you need to set the required parameters at the database level. The settings take effect 1 minute later.

  • Syntax

    -- Modify the default settings of the auto-analyze feature as a superuser.
    ALTER DATABASE <dbname> SET <GUC>=<values>;

    The dbname parameter specifies the name of the database, the GUC parameter specifies the name of the parameter, and the values parameter specifies the values of the GUC parameter.

  • Parameters

    Parameter	Description	Supported version	Default value	Example
    autovacuum_naptime	The interval between two consecutive checks for operations on tables. Unit: seconds.	Hologres V1.1.0 and later Note To modify the settings, contact technical support in the DingTalk group (ID 32314975).	60s	ALTER DATABASE <dbname> SET autovacuum_naptime = 60; ALTER DATABASE <dbname> SET autovacuum_naptime = '60s'; ALTER DATABASE <dbname> SET autovacuum_naptime = '10min';
    hg_auto_check_table_changes_interval	The interval between two consecutive checks of data changes in internal tables. Unit: seconds.	Hologres V1.1.0 and later	600s	-- Syntax in Hologres V1.1 and later ALTER DATABASE <dbname> SET hg_auto_check_table_changes_interval = '600s'; -- Syntax in Hologres V0.10 ALTER DATABASE <dbname> SET hg_experimental_auto_check_table_changes_interval = '600s';
    hg_auto_check_foreign_table_changes_interval	The interval between two consecutive checks of data changes in foreign tables. Unit: seconds.	Hologres V1.1.0 and later	14400s	-- Syntax in Hologres V1.1 and later ALTER DATABASE <dbname> SET hg_auto_check_foreign_table_changes_interval = '14400s'; -- Syntax in Hologres V0.10 ALTER DATABASE <dbname> SET hg_experimental_auto_check_foreign_table_changes_interval = '14400s';
    hg_experimental_auto_analyze_max_columns_count	The maximum number of columns whose statistics are automatically collected at a time.	Hologres V1.1.0 and later	256	ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_max_columns_count =300;
    auto_analyze_work_memory_mb	The maximum size of memory that can be occupied by the auto-analyze feature for a single table. Unit: MB.	Hologres V1.1.54 and later	By default, each worker used by the auto-analyze feature can occupy 4,096 MB (4 GB) of memory. The selection of a more advanced instance type for your Hologres instance allows for a greater number of workers, and the auto-analyze feature consumes more memory resources.	Change the upper limit of the memory size for a table to 9 GB: ALTER DATABASE <dbname> SETauto_analyze_work_memory_mb =9216;
    hg_experimental_auto_analyze_start_time	The beginning of the time range when ANALYZE operations are performed every day. Note The specified beginning and end of the time range must be in the same time zone. The beginning of the time range must be equal to or earlier than the end of the time range.	Hologres V1.1.54 and later	00:00 +0800	Set the beginning of the time range when ANALYZE operations are performed to 00:00 and the end of the time range to 06:00. In the daytime, data in foreign tables is not changed, and ANALYZE operations are not required. ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_start_time = '00:00 +0800'; ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_end_time = '06:00 +0800';
    hg_experimental_auto_analyze_end_time	The end of the time range when ANALYZE operations are performed every day.	Hologres V1.1.54 and later	23:59 +0800
    autovacuum_enabled	Specifies whether to enable the auto-analyze feature for the specified database.	Hologres V1.1.54 and later	true, which means to enable this feature for all tables in the database.	Disable the auto-analyze feature for a specified table. Then, ANALYZE operations are not performed on this table. Note You can use only the following statement to disable the auto-analyze feature for a Hologres internal table: ALTER TABLE <tablename> SET (autovacuum_enabled = false);

Query table statistics

SELECT schema_name,                -- The schema of the table.
       table_name,                 -- The name of the table.
       schema_version,             -- The version of the table.
       statistic_version,          -- The version of the statistics obtained by using the last ANALYZE operation.
       total_rows,                 -- The number of rows involved in the last ANALYZE operation.
       analyze_timestamp           -- The end of the time range when the last ANALYZE operation is performed.
FROM   hologres_statistic.hg_table_statistic
WHERE  table_name = '<tablename>'
ORDER BY analyze_timestamp DESC;

FAQ