ANALYZE and AUTO ANALYZE

Why Are Statistics Needed?

The optimizer relies on table and column statistics to generate reasonable execution plans, including:

• Row count and column count

• Column width (Width)

• Number of Distinct Values (NDV)

• Most Common Values (MCV) and their frequencies

• Histogram and other distribution characteristics

This information guides the optimizer to:

• Estimate operator execution cost

• Prune the execution plan search space

• Select appropriate Join order and Join algorithms

• Estimate memory and parallelism

This results in a more optimal execution plan.

The ANALYZE command is the standard method for users to actively collect table/column statistics. When statistics are missing or inaccurate, the query plan may degrade significantly, such as abnormal Join ordering, which manifests as: query OOM, long execution time, or high instance CPU consumption.

AUTO ANALYZE is the standard method for the Hologres system to automatically collect table/column statistics. Since AUTO ANALYZE is an asynchronous background system behavior, there is a delay of tens of seconds to minutes from when table data changes to when AUTO ANALYZE discovers, schedules, and completes the automatic statistics collection. Therefore, in some specific scenarios, it is recommended that users manually run ANALYZE to ensure statistics are collected in a timely manner.

When to Manually Execute ANALYZE

Manual execution is recommended in the following scenarios:

• After INSERT / UPDATE / DELETE operations that import/update/delete a large amount of data on a table, and the table will be queried immediately, it is recommended to run ANALYZE on the table after the INSERT / UPDATE / DELETE;

• When multi-table Join performance degrades significantly, execute column-level ANALYZE on key Join columns and Group By columns;

• After executing CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA for a foreign table, and it will be queried immediately, it is recommended to run ANALYZE on the newly created foreign table before querying to collect initial statistics;

• After executing CREATE EXTERNAL DATABASE, and it will be queried immediately, it is recommended to run ANALYZE on the tables that need to be queried under the external database before querying;

• When one of the following errors/symptoms occurs:

  • Multi-table Join OOM error: Query executor exceeded total memory limitation ..., or Query exceed per query memory limitation ...;

  • Multi-table Join error: Capacity error: BinaryArray cannot contain more than 2147483646 bytes ...;

  • Import or query tasks run abnormally long with uneven CPU utilization.

  • EXPLAIN <SQL> shows a Scan operator with an estimated row count of 1000, -> Seq Scan on tbl (cost=0.00..5.00 rows=1000 width=1); this indicates the table lacks statistics.

  • EXPLAIN <SQL> shows a Scan operator with an estimated row count of 1, -> Seq Scan on tbl (cost=0.00..1.00 rows=1 width=1); this indicates the table is estimated to have 0 rows. If the actual Scan result is not 0 rows, the statistics may be outdated, and a manual ANALYZE is recommended.

In the above scenarios, it is generally recommended to first manually run ANALYZE and observe whether performance recovers, then further tune the AUTO ANALYZE configuration.

Basic Syntax and Behavior

• Collect statistics for all columns of the entire table

ANALYZE table_name;

• Collects the table row count and uniformly collects Width, MCV, Histogram, NDV, and other statistics for all regular columns in the table;

• Uses a sampling-based approach to estimate various statistical values. The sampling operation initiates a sampling sub-SQL within the ANALYZE process.

• By default, ANALYZE randomly samples 30,000 rows from the table for statistics collection and computation. If the table has fewer than 30,000 rows, all rows are sampled.

• Collect statistics for specific columns only (recommended for key columns)

ANALYZE table_name(col1, col2, ...);

• Computes a more accurate NDV for the specified columns (typically using APPROX_COUNT_DISTINCT logic), which is more accurate than table-level sampling but more expensive;

• MCV, Histogram, Width, and other statistics are still obtained through sampling;

• When ANALYZE is executed multiple times on the same column, the later execution overwrites the old statistics for that column, but does not affect other unspecified columns.

Limitations and Considerations

• Which columns will not be analyzed

  • Unsupported types: When a column type is a User Defined Type or is not in the set of types that Hologres supports for statistics, statistics collection will be skipped:

    • Column types that do not support ANALYZE include: "char" (single character type), BIT, VARBIT, BYTEA, NAME, JSON, TSVECTOR, TSQUERY, OID, XID, CID, INET, POINT, LINE, LSEG, BOX, CIRCLE, PATH, POLYGON, BITARRAY, VARBITARRAY, BYTEAARRAY, INT2ARRAY, MONEYARRAY, NUMERICARRAY, TIMEARRAY, TIMETZARRAY, TIMESTAMPTZARRAY, TIMESTAMPARRAY, ANYARRAY, REGCLASS, DATEARRAY, or other INTERNAL types.

    • When the column type is unsupported, both manual ANALYZE and AUTO ANALYZE will be ignored;

    • For partitioned tables, column types that do not support incremental partition statistics merging will also not be analyzed. In addition to the types listed above, unsupported types also include: BOOLARRAY, INT4ARRAY, TEXTARRAY, BPCHARARRAY, VARCHARARRAY, INT8ARRAY, FLOAT4ARRAY, FLOAT8ARRAY.

  • Columns marked as dropped: Logical columns retained after ALTER TABLE ... DROP COLUMN (attisdropped = true) will not be analyzed;

  • Explicitly disabled by column attribute:

    • Columns with the column attribute enable_analyze = false will not be processed by ANALYZE or AUTO ANALYZE;

    • Columns with the column attribute enable_auto_analyze = false will not be processed by AUTO ANALYZE.

    • For details about these column attributes, see the subsequent sections.

  • JSONB column statistics not enabled:

    • If the column type is JSONB and enable_jsonb_stats is not enabled, JSONB statistics will not be collected for that column;

    • If you need to rely on JSONB column statistics (for example, complex JSON filter conditions), you need to first enable enable_jsonb_stats in the column attributes.

• ANALYZE limitations for data lake tables

  • ANALYZE is not supported for specific Timestamp, Version, Branch, Snapshot, or Tag individually

Typical Usage

• Recommended typical usage

  • Execute column-level ANALYZE on Join columns, Group By columns, and filter condition columns:

ANALYZE tablename (order_id, user_id, dt);

• • After import or batch update, execute on tables that are significantly affected:

ANALYZE tablename;

• • Execute on foreign tables (FOREIGN TABLE / IMPORT FOREIGN SCHEMA / tables under EXTERNAL DATABASE) before the first query:

ANALYZE foreign_table;

• Skip statistics collection for specific columns by setting column attributes

  • Hologres supports controlling whether ANALYZE or AUTO ANALYZE is executed on a column through column-level attributes, which is applicable to the following scenarios:

    • Ultra-wide columns (such as very long Text) have high statistics collection overhead, and collecting statistics for them provides limited or no benefit to the query plan;

    • Certain columns never participate in Joins, filtering, or aggregation, and do not need statistics collection;

    • Need to reduce resource consumption of ANALYZE or AUTO ANALYZE.

  • The control parameters are the following column options:

    • enable_analyze: Controls whether the column participates in manual ANALYZE and automatic AUTO ANALYZE. Default is true;

    • enable_auto_analyze: Controls only whether the column participates in automatic AUTO ANALYZE. Default is true.

  • Setting method (via ALTER TABLE command):

-- 禁用某列的 ANALYZE 和 AUTO ANALYZE
ALTER TABLE t ALTER COLUMN bitmap_col SET (enable_analyze = false);

-- 禁用某列的 AUTO ANALYZE，但仍允许手动 ANALYZE
ALTER TABLE t ALTER COLUMN large_text_col SET (enable_auto_analyze = false);

-- 恢复默认行为（重新启用）
ALTER TABLE t ALTER COLUMN bitmap_col RESET (enable_analyze);
ALTER TABLE t ALTER COLUMN large_text_col RESET (enable_auto_analyze);

• • Considerations:

    • After setting enable_analyze = false, the column will be automatically skipped when executing ANALYZE table_name;, but can still be explicitly collected via manual ANALYZE table_name(col);;

    • After setting enable_auto_analyze = false, the column will not be collected by AUTO ANALYZE, but can still be explicitly collected via manual ANALYZE table_name(col);;

    • If a column has significant impact on the query plan (such as Join columns or filter condition columns), it is not recommended to disable its statistics collection.

Scenarios and Limitations

• In Hologres V2.0 and later, incremental partition ANALYZE is enabled by default without manual configuration. For details, refer to the product version notes;

• When enabled, executing ANALYZE on a child partition will attempt to combine its statistics with those of other child partitions to produce the parent table statistics;

• The prerequisite for merging parent table statistics is that, apart from the child partition currently being analyzed, all other child partitions under the parent table must already have statistics (including row count statistics and column statistics);

• Therefore, you can:

  • During initial setup, execute ANALYZE on the parent table first. The system will automatically detect child partitions that need ANALYZE and analyze them one by one, ultimately merging the results into the parent table statistics;

  • When new partitions are added later, you only need to execute ANALYZE on the newly added child partitions.

Brief Example

BEGIN;
DROP TABLE IF EXISTS t_parent;
CREATE TABLE t_parent(a int, b int) PARTITION BY LIST (a);
CREATE TABLE child1 PARTITION OF t_parent FOR VALUES IN (1);
CREATE TABLE child2 PARTITION OF t_parent FOR VALUES IN (2);
COMMIT;
insert into child1 values (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 1), (1, 2);
insert into child2 values (2, 1), (2, 2), (2, 3), (2, 4), (2, 5), (2, 5), (2, 5), (2, 5), (2, 5), (2, 5);

-- child2 lacks statistics, so parent table statistics cannot be merged
dbname=# ANALYZE child1;
INFO:  auto merging of leaf partition stats to calculate root partition stats is not possible because partition child2 is not analyzed
ANALYZE

-- child1 already has statistics. After analyzing child2, parent table statistics can be automatically merged
dbname=# ANALYZE child2;
ANALYZE

dbname=# SELECT tablename,
       attname,
       null_frac         AS "NF",
       avg_width,
       n_distinct,
       most_common_vals  AS "MCV",
       most_common_freqs AS "MCV_FRAQ"
FROM   pg_stats
WHERE  tablename = 't_parent';

 tablename | attname | NF | avg_width | n_distinct |  MCV  |  MCV_FRAQ
-----------+---------+----+-----------+------------+-------+------------
 t_parent  | a       |  0 |         4 |          2 | {1,2} | {0.5,0.5}
 t_parent  | b       |  0 |         4 |       -0.2 | {1,5} | {0.45,0.3}
(2 rows)

-- After this, when new partitions are added, you only need to ANALYZE the child partition. Parent table statistics will be automatically merged through sampling the child partition only.

ANALYZE on the Parent Table

Sometimes multiple child partitions of a partitioned table have undergone data changes, and you do not want to ANALYZE each child partition individually. In this case, you can perform a single ANALYZE on the parent table.

When incremental partition ANALYZE is enabled, manually executing ANALYZE on the parent table causes the system to adaptively select child partitions that need statistics updates, rather than blindly analyzing all child partitions. This mechanism significantly reduces the ANALYZE overhead for large partitioned tables while ensuring the accuracy of statistics.

-- Execute ANALYZE on the parent table
ANALYZE partition_parent_table;

INFO:  will analyze 5 part tables first (stats changed 3, unable-to-merge 2)

ANALYZE for Logical Partitioned Tables

A logical partitioned table is a partition table type unique to Hologres. Unlike standard PostgreSQL partitioned tables (which create physical child tables via PARTITION BY), a logical partitioned table remains physically as a single table and only partitions data logically based on partition column values, providing more flexible partition management capabilities.

The ANALYZE logic for logical partitioned tables is essentially the same as for physical partitioned tables. The only difference is that child partitions of a logical partitioned table are not separate tables, but like physical child partitions, they each still have their own independent row count statistics and column statistics.

-- Create a logical partitioned table
CREATE TABLE user_events (
    user_id INT NOT NULL,
    event_type TEXT NOT NULL,
    event_date DATE NOT NULL,
    event_data INT
)
LOGICAL PARTITION BY LIST(user_id, event_date);

-- Insert data into different logical partitions
INSERT INTO user_events SELECT 1, 'login', '2024-11-01', i FROM generate_series(1, 1000) i;
INSERT INTO user_events SELECT 1, 'logout', '2024-11-02', i FROM generate_series(1, 1000) i;
INSERT INTO user_events SELECT 2, 'purchase', '2024-11-03', i FROM generate_series(1, 1000) i;
INSERT INTO user_events SELECT 3, 'view', '2024-11-04', i FROM generate_series(1, 1000) i;

-- Execute ANALYZE on the parent table
ANALYZE VERBOSE user_events;

INFO:  will analyze 4 logical partitions first
INFO:  analyzing hologres table "public.user_events" PARTITION (user_id=1,event_date='2024-11-01')
INFO:  analyzing hologres table "public.user_events" PARTITION (user_id=1,event_date='2024-11-02')
INFO:  analyzing hologres table "public.user_events" PARTITION (user_id=2,event_date='2024-11-03')
INFO:  analyzing hologres table "public.user_events" PARTITION (user_id=3,event_date='2024-11-04')
INFO:  try to merge root partition
INFO:  automatically merging leaf partition stats to calculate root partition stats

-- Execute ANALYZE on a single logical partition
ANALYZE user_events PARTITION (user_id=1, event_date='2024-11-01');

-- Execute ANALYZE on multiple logical partitions
ANALYZE user_events
    PARTITION (user_id=1, event_date='2024-11-01')
    PARTITION (user_id=2, event_date='2024-11-03');

-- Execute ANALYZE on specific columns of a specified logical partition
ANALYZE user_events
    PARTITION (user_id=1, event_date='2024-11-01') (user_id, event_type);

SELECT
    unique_name,
    schema_name,
    table_name,
    total_rows,
    sample_rows,
    nattr
FROM hologres_statistic.hg_table_statistic
WHERE table_name = 'user_events'
ORDER BY unique_name;

| unique_name | schema_name | table_name | total_rows | sample_rows | nattr |
|-------------|-------------|------------|------------|-------------| ----- |
| user_events | public | user_events | 4000 | 0 | 4 |
| user_events.a1b2c3d4e5f6... | public | user_events | 1000 | 1000 | 4 |
| user_events.f6e5d4c3b2a1... | public | user_events | 1000 | 1000 | 4 |
| user_events.1234567890ab... | public | user_events | 1000 | 1000 | 4 |
| user_events.abcdef123456... | public | user_events | 1000 | 1000 | 4 |

Switch and Scope

-- 查看当前数据库是否开启 AUTO ANALYZE
SHOW hg_enable_start_auto_analyze_worker;

-- 关闭（仅临时排查、规避问题时使用）
ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = OFF;

-- 重置回默认值ON（推荐）
ALTER DATABASE dbname RESET hg_enable_start_auto_analyze_worker;

ALTER TABLE my_table SET (autovacuum_enabled = false);

-- 推荐，重置以恢复默认
ALTER TABLE my_table RESET (autovacuum_enabled);

-- 或者显式设置为 true（不推荐）
ALTER TABLE my_table SET (autovacuum_enabled = true);

SELECT relname, reloptions FROM pg_class WHERE relname = 'my_table';

 relname  |         reloptions
----------+----------------------------
 my_table | {autovacuum_enabled=false}
(1 row)

-- 禁用某列的 AUTO ANALYZE，但仍允许手动 ANALYZE
ALTER TABLE t ALTER COLUMN large_text_col SET (enable_auto_analyze = false);

-- 恢复默认行为（重新启用）
ALTER TABLE t ALTER COLUMN large_text_col RESET (enable_auto_analyze);

Trigger Logic of AUTO ANALYZE

AUTO ANALYZE combines multiple types of signals to determine whether statistics need to be updated for a particular table:

1. Data Change Volume

   • Applies only to regular Hologres tables (excludes foreign tables);

   • At 1-minute intervals, collects the row count of INSERT/UPDATE/DELETE operations observed by the FE on each table. If the change exceeds the threshold, an AUTO ANALYZE is triggered (fast response);

   • At 10-minute intervals, retrieves the insert/update/delete change row counts from the storage engine. If the change exceeds the threshold, an AUTO ANALYZE is triggered (precise calibration);

2. Schema Changes

   • Applies only to regular Hologres tables (excludes foreign tables);

   • At 1-minute intervals, collects tables with the following changes and performs AUTO ANALYZE:

     • ADD / DROP COLUMN;

     • Partitioned tables (including logical partition tables) ATTACH / DETACH child partitions.

3. Missing Statistics / Unable to Merge Parent Partition Table Statistics

   • Applies to both regular Hologres tables and foreign tables;

   • At 1-minute intervals, collects tables/columns with missing statistics and child partitions that cannot meet the conditions for incremental statistics merging.

4. Foreign Tables

   • Applies only to foreign tables introduced through CREATE FOREIGN TABLE, IMPORT FOREIGN SCHEMA, or the Auto Load mechanism under non-External Database contexts;

   • Currently only supports AUTO ANALYZE for MaxCompute foreign tables;

   • At 4-hour intervals, periodically checks all foreign tables in the DB. Between two checks, if there are external data changes (the criterion is that the corresponding foreign table's last_modify_timestamp falls between the two check intervals), an AUTO ANALYZE is triggered.

5. Fallback Statistics

   • During the 1:00-5:00 AM window, a "fallback" AUTO ANALYZE is performed on tables that have continuous changes but have not triggered the threshold (>5000 row changes, <10% change volume), to capture data distribution drift in columns (for example, date-type fields written after midnight the next day are completely different from the previous day, resulting in distribution changes).

6. Access Hotspot Tables (External Database only)

   • After enabling the enable_auto_analyze parameter on an External Database, the Hologres system begins tracking recently accessed tables since the last system startup and adds them to the observation list;

   • At 1-hour intervals, tables in the observation list that are "frequently accessed recently" trigger an AUTO ANALYZE;

   • Note that after a system restart, the accessed table list is cleared and recording starts over.

AUTO ANALYZE for External Database

• Starting from Hologres V3.0, Hologres supports the External Database feature. When creating an External Database, you can enable AUTO ANALYZE (disabled by default, see release notes for details):

-- 创建 External Database 时，开启 AUTO ANALYZE
-- 参考文档：https://help.aliyun.com/zh/hologres/developer-reference/create-external-database
CREATE EXTERNAL DATABASE <ext_database_name> WITH
  metastore_type 'maxcompute'
  mc_project 'project_name'
  enable_auto_analyze 'true';

-- 创建 External Database 之后，为其开启 AUTO ANALYZE
ALTER EXTERNAL DATABASE dbname WITH enable_auto_analyze 'true';

• The metastore_type scope supported by AUTO ANALYZE on External Database is: dlf, dlf-paimon, dlf-rest, maxcompute.

• The table formats supported by AUTO ANALYZE on External Database include: MaxCompute, Paimon, Iceberg.

• AUTO ANALYZE on External Database uses the Owner identity of the External Database by default, and employs Time-based One-Time Password (TOTP) authentication to execute ANALYZE tasks. If the Owner does not have sufficient table permissions, the system cannot successfully execute AUTO ANALYZE tasks.

• For a foreign table in an External Database to be eligible for AUTO ANALYZE, the following conditions must be met:

  • The External Database configuration hg_enable_start_auto_analyze_worker = on; (default is on)

  • The External Database attribute enable_auto_analyze is set to true (not configured by default, which means false, see release notes for details)

  • The External Database Owner must have query permissions for the data lake Project (if applicable) and the table

  • The table must have been accessed at least once within the last 3 days

    • AUTO ANALYZE under External Database only monitors foreign tables that have been accessed at least once;

    • After a system restart, if a foreign table is accessed at least once, the AUTO ANALYZE system adds it to the observation list and periodically triggers AUTO ANALYZE for these tables;

Resource Limits of AUTO ANALYZE

To prevent background AUTO ANALYZE tasks from impacting foreground user tasks, Hologres has established the following resource limits for the AUTO ANALYZE feature:

• When AUTO ANALYZE tasks execute, the default memory limit per worker is 4 GB. If the table data volume is too large, sampling may exceed the memory limit, causing the AUTO ANALYZE sampling SQL to fail. In such cases, only row count information can be collected, and column distribution information (MCV, Histogram, NDV, etc.) cannot be collected. You can adjust auto_analyze_work_memory_mb to change this behavior. The larger the instance specification, the higher the available memory limit for AUTO ANALYZE.

• The instance-level concurrency for simultaneously scheduled AUTO ANALYZE tasks typically does not exceed 4, and in extreme cases does not exceed 6.

• For child partition AUTO ANALYZE tasks under the same partitioned table, the maximum simultaneous scheduling concurrency is 3.

• By default, AUTO ANALYZE collects statistics for the first 256 columns. If a table has more than 256 columns, only the first 256 are collected (for partitioned tables, partition columns are prioritized and will be collected even if they are beyond position 256). You can adjust hg_experimental_auto_analyze_max_columns_count to change this value.

• The sampling sub-SQLs of AUTO ANALYZE tasks run using a low-priority Background Pool with limited query execution-level concurrency. This causes AUTO ANALYZE tasks to take longer than manual ANALYZE, which users do not need to be concerned about.

• For foreign tables, AUTO ANALYZE only collects column statistics for partition columns (e.g., MCV). It does not perform sampling to collect column statistics for columns other than partition columns.

Configurable Parameters of AUTO ANALYZE

By default, the Hologres AUTO ANALYZE feature does not require any parameter modifications.

In rare business scenarios (such as infrequent data writes/updates, query workloads that do not need statistics, or system load increases caused by AUTO ANALYZE), users can modify certain default parameters to adjust AUTO ANALYZE behavior for intervention or partial performance tuning purposes.

-- Superuser，DB级别修改 AUTO ANALYZE 参数的默认值
ALTER DATABASE dbname SET <GUC> = <values>;

Switch

How to enable fast row count estimation:

-- 关闭（仅适用于临时处置，或全库明确不需要统计信息的情况）
ALTER DATABASE dbname SET hg_experimental_get_fast_num_of_rows = OFF;

-- V3.2及以上，打开（重置回默认值）
ALTER DATABASE dbname RESET hg_experimental_get_fast_num_of_rows;

-- V3.1及以下，打开
ALTER DATABASE dbname SET hg_experimental_get_fast_num_of_rows = ON;

Usage Limits

• Fast row count estimation is applicable to regular tables (including partitioned tables), foreign tables in External Database, and other scenarios.

• The row count estimated by the fast row count estimation feature is not guaranteed to be completely accurate. For example, for performance reasons, on Hologres partitioned tables, only the row count statistics from up to 60 partitions (by default) are used to estimate the overall row count of the partitioned table.

• In V4.1.15 and earlier, fast row count estimation is not enabled by default for foreign table types (Foreign Table). It can be enabled through the parameter hg_experimental_enable_foreign_table_get_fast_num_of_rows. It is enabled by default in V4.1.16 and later.

• For foreign table types, fast row count estimation only supports MaxCompute foreign tables, Paimon foreign tables, and Iceberg foreign tables.

Examples

1. hg_experimental_get_fast_num_of_rows example

If a table has no statistics, enabling fast row count estimation can provide more accurate row counts. (Enabled by default in Hologres V3.2 and later)

-- V3.2+
create table test_tbl (a int);
insert into test_tbl select * from generate_series (1, 999);

-- 准确的行数估计（rows=999）
explain select count(1) from test_tbl ;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Final Aggregate  (cost=0.00..5.00 rows=1 width=8)
   ->  Gather  (cost=0.00..5.00 rows=10 width=8)
         ->  Partial Aggregate  (cost=0.00..5.00 rows=10 width=8)
               ->  Local Gather  (cost=0.00..5.00 rows=20 width=8)
                     ->  Partial Aggregate  (cost=0.00..5.00 rows=20 width=8)
                           ->  Seq Scan on test_tbl  (cost=0.00..5.00 rows=999 width=1)

The following is a real production case:

Without this feature enabled, if a table has no statistics, the Scan node in the query plan will show rows=1000 (indicating no statistics are available, and the plan is generated based on the default estimate of 1000 rows).

After enabling this feature, if a table has no statistics, the Scan node rows in the query plan will not equal 1000 (the system called the metadata from the underlying storage engine to obtain the table's row count).

Configurable Parameters for Fast Row Count Estimation

How to Identify ANALYZE and AUTO ANALYZE Records in Query Log

ANALYZE and AUTO ANALYZE, along with their sampling SQL statements, are recorded separately in the Query Log.

The execution records have the following characteristics in the Query Log:

ANALYZE and its sampling SQL are associated through the comments in the query field, or through extended_info->>'source_query_id'.

SELECT query_id,
       extended_info->>'src_query_id' as "source query id",
       application_name,
       status,
       duration
FROM   hologres.hg_query_log
WHERE  query_start >= now() - interval '1 hour'
  AND  application_name IN ('AutoAnalyze', 'Hologres SQL Generated BY AUTO ANALYZE')
ORDER BY query_start DESC
LIMIT  2;
      query_id       |   source query id   |            application_name            | status  | duration
---------------------+---------------------+----------------------------------------+---------+----------
 1004019226350863009 | 1004019226350778807 | Hologres SQL Generated BY AUTO ANALYZE | SUCCESS |      119
 1004019226350778807 |                     | AutoAnalyze                            | SUCCESS |     3276

Query Examples

• View recent AUTO ANALYZE execution records

SELECT usename,
       status,
       duration,
       query_start,
       query_end,
       query,
       application_name
FROM   hologres.hg_query_log
WHERE  query_start >= now() - interval '1 hour'
  AND  application_name IN ('AutoAnalyze')
ORDER BY query_start DESC
LIMIT  20;

       query_id,
       extended_info->>'src_query_id' as "source query id"

• View the AUTO ANALYZE execution history of a specific table

SELECT status,
       duration,
       query_start,
       query
FROM   hologres.hg_query_log
WHERE  query_start >= now() - interval '1 day'
  AND  application_name IN ('AutoAnalyze')
  AND  query LIKE '%my_table_name%'
ORDER BY query_start DESC;

• Summarize AUTO ANALYZE execution overview for the past 3 days

SELECT query_date,
       status,
       COUNT(*)          AS task_count,
       AVG(duration)     AS avg_duration_ms,
       MAX(duration)     AS max_duration_ms
FROM   hologres.hg_query_log
WHERE  query_start >= CURRENT_DATE::timestamptz - interval '2 day'
  AND  application_name IN ('AutoAnalyze')
  AND  command_tag = 'ANALYZE'
  AND  (status = 'SUCCESS' OR (
            message NOT LIKE '%does not exist%'
        AND message NOT LIKE '%retry later%'))
GROUP BY query_date, status
ORDER BY query_date DESC;

• View failed AUTO ANALYZE tasks in the past day

SELECT query_start,
       duration,
       message,
       query
FROM   hologres.hg_query_log
WHERE  query_start >= now() - interval '1 day'
  AND  application_name IN ('AutoAnalyze', 'Hologres SQL Generated BY AUTO ANALYZE')
  AND  status != 'SUCCESS'
  AND  (message NOT LIKE '%does not exist%'
        AND message NOT LIKE '%retry later%')
ORDER BY query_start DESC;

Notes

• Since AUTO ANALYZE connections are initiated with an internal administrator identity, regular users need the pg_read_all_stats role or database administrator privileges to view complete AUTO ANALYZE execution records.

• If you find that there are no AUTO ANALYZE records in the Query Log for an extended period, it is recommended to check whether the AUTO ANALYZE switch is enabled.

Viewing Table Statistics (hologres_statistic.hg_table_statistic)

Table statistics are stored in the hologres_statistic.hg_table_statistic table and can also be observed in system tables.

1. Query this table to get the statistics from the most recent ANALYZE.

SELECT schema_name,                -- 表的schema
       table_name,                 -- 表名
       user_name,                  -- 最近一次ANALYZE的用户
       schema_version,             -- 最近一次ANALYZE的表Schema版本
       total_rows,                 -- 最近一次ANALYZE的行数
       sample_rows,                -- 最近一次ANALYZE获取统计信息的采样行数
       analyze_timestamp,          -- 最近一次ANALYZE的结束时间
       analyze_count               -- 到当前为止总计的ANALYZE次数
FROM   hologres_statistic.hg_table_statistic
WHERE  unique_name = hologres.hg_internal_statistic_unique_name ('schemaname', 'tablename')
ORDER BY analyze_timestamp DESC;

-- 示例输出
 schema_name | table_name |    user_name     | schema_version | total_rows | sample_rows |  analyze_timestamp  | analyze_count
-------------+------------+------------------+----------------+------------+-------------+---------------------+---------------
 public      | test_fnr   |   BASIC$test_fnr |             -1 |        999 |         999 | 2026-03-02 22:05:29 |             2
(1 row)

V3.1 and earlier:

• Each table has 0 to n records in the hologres_statistic.hg_table_statistic table. 0 records means ANALYZE has never been performed, and 1 or more records means ANALYZE has been run.

• If there are two or more records, the schema_version of the two records must be different, because changes to the table schema (such as ADD COLUMN, CALL SET_TABLE_PROPERTY, etc.) generate a new version, which adds a new statistics record. The record corresponding to the old schema_version is no longer used.

• The following example query result shows that the same table has 2 records, and the schema_version of the second record is lower than the first. The second record is therefore invalid and will not be used, and you do not need to pay attention to it. Hologres does not currently clean up historical expired records in the hg_table_statistic table, and users do not need to worry about old data.

 schema_name | table_name |    user_name     | schema_version | total_rows | sample_rows |  analyze_timestamp  | analyze_count
-------------+------------+------------------+----------------+------------+-------------+---------------------+---------------
 public      | test_fnr   |   BASIC$test_fnr |             13 |        999 |         999 | 2026-03-01 18:05:29 |             2
 public      | test_fnr   |   BASIC$test_fnr |             12 |        999 |         999 | 2026-03-01 08:05:29 |             1
(1 row)

V3.1 and later:

• Each table has 0 to 1 records in the hologres_statistic.hg_table_statistic table. 0 records means ANALYZE has never been performed, and 1 record means ANALYZE has been run.

• The schema_version is uniformly set to -1 (representing Deprecated).

• This means that many DDL operations no longer invalidate statistics. For example, CALL SET_TABLE_PROPERTY will not re-trigger AUTO ANALYZE, and the existing statistics will continue to be used. Compared to Hologres V3.0 and earlier, the trigger frequency of AUTO ANALYZE is significantly reduced.

1. Querying row count and other statistics

Row count information is recorded in the reltuples field of the pg_class table.

-- relallvisible > 0：表有行数统计信息
-- relallvisible = 0：表行数未知，此时不能参考reltuples
-- relallvisible < 0：表无统计信息
SELECT relallvisible, reltuples FROM pg_class WHERE relname = 'test_table';

1. Querying column statistics

By querying the pg_stats view, you can obtain the statistics for all columns of the current table. For example, to get the statistics for the ds column of test_table:

select * from pg_stats where tablename = 'test_table' and attname = 'ds';

 schemaname | tablename  | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+------------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
 public     | test_table | ds      | f         |         0 |         4 |          1 | {20241104}       | {1}               |                  |             |                   |                        |
(1 row)

Viewing Tables with Missing Statistics

Hologres provides the view HG_STATS_MISSING (the actual view name may vary by version) to check tables in the current database that are missing statistics, making it easy to identify objects that need supplementary ANALYZE.

Common Issues and Troubleshooting

1. Possible reasons for 0 records returned from the hologres_statistic.hg_table_statistic table:

• ANALYZE has never been executed (neither manually nor by AUTO ANALYZE);

• AUTO ANALYZE is not working, or the table has not met the trigger conditions;

Recommended actions:

• First, manually execute:

ANALYZE schema_name.table_name;

• If the table still cannot be covered by AUTO ANALYZE for an extended period, it is recommended to check the AUTO ANALYZE switch and GUC configuration, or contact technical support.

1. Possible reasons for an obviously outdated analyze_timestamp:

• AUTO ANALYZE has been disabled or restricted (such as autovacuum_enabled = false);

• AUTO ANALYZE has insufficient resources or has failed (can be investigated in conjunction with logs/monitoring);

Recommended actions:

• Manually execute ANALYZE once and observe the results;

• Check:

  • Whether hg_enable_start_auto_analyze_worker is enabled;

  • Whether autovacuum_enabled = false has been mistakenly set or related GUC restrictions are too strict;

  • Check whether there are errors related to AUTO ANALYZE tasks in the Query Log.

1. Abnormal row count estimation in query plans (significantly too small or too large)

• Check whether the corresponding table is missing statistics or has outdated statistics;

• Check whether predicate columns have statistics (pg_stats), and perform column-level ANALYZE on critical columns;

• Check whether the fast row count estimation feature has been disabled or not enabled.

1. Inconsistent parent-child statistics for partitioned tables or missing statistics on the parent table

• Symptom: The partition parent table has no records in hologres_statistic.hg_table_statistic, or total_rows is significantly less than the sum of all child table row counts, or the partition column statistics MCV distribution of the parent table is outdated (for example, the ds column in pg_stats is missing yesterday's distribution);

• Troubleshooting points:

  • Check whether incremental partition ANALYZE / AUTO ANALYZE has been disabled;

SHOW hg_experimental_enable_incremental_analyze;
SHOW hg_experimental_enable_incremental_auto_analyze;

• Use the HG_STATS_MISSING view to check whether there are child tables that cannot be merged;

• Check whether there are errors related to AUTO ANALYZE tasks in the Query Log;

• Manually execute ANALYZE child_table; on child tables with missing statistics, or execute ANALYZE partition_parent_table; on the parent table during off-peak hours to let the system fill in and merge statistics.

1. Certain columns have no statistics in pg_stats

• Symptom: The table has the column, but the corresponding column statistics cannot be found in the pg_stats view.

• Troubleshooting points:

  • Check whether statistics have been disabled through column attributes (enable_analyze = false or enable_auto_analyze = false);

SELECT attoptions
  FROM pg_attribute
 WHERE attrelid = 'tablename'::regclass::oid
   AND attname = 'columnname';
-[ RECORD 1 ]----------------------
attoptions | {enable_analyze=false}

• Confirm whether the column has participated in ANALYZE (manual or AUTO ANALYZE). If necessary, execute column-level ANALYZE table_name(col_name);;

SELECT query_id,
       application_name,
       status,
       query_start,
       query
FROM   hologres.hg_query_log
WHERE  query_start >= now() - interval '1 hour'
  AND  application_name IN ('Hologres SQL Generated BY ANALYZE', 'Hologres SQL Generated BY AUTO ANALYZE')
  AND  query like '%tablename%'
ORDER BY query_start DESC
LIMIT  2;

• Whether the column is a special type (bytea, jsonb).

• If the table has executed AUTO ANALYZE but no columns have statistics, consider whether the AUTO ANALYZE task executed abnormally. For example, check whether AUTO ANALYZE timed out. Since auto_analyze_work_statement_timeout defaults to 1 hour, if the AUTO ANALYZE task in hologres.hg_query_log has a duration >= 3600000, in this case, only row count information is collected without column statistics.

1. AUTO ANALYZE has executed but the query plan has not significantly improved

• Symptom: AUTO ANALYZE records for a specific table can be seen in hologres.hg_query_log, but the actual query plan still uses obviously unreasonable row count estimates or Join order;

• Troubleshooting points:

  • Check whether AUTO ANALYZE timed out. Since auto_analyze_work_statement_timeout defaults to 1 hour, if the AUTO ANALYZE task in hologres.hg_query_log has a duration >= 3600s, in this case, only row count information is collected without column statistics.

  • Check whether there are Hints, fixed Join orders, or legacy session-level GUCs (such as enable_nestloop, etc.) interfering with the optimizer's choices;

  • Perform a manual ANALYZE on critical tables/columns and then re-run the query. If there is still no improvement, it is recommended to investigate further in conjunction with Query Log, FE logs, or by reporting to technical support.

1. OOM / memory limit exceeded issues caused by statistics

• Symptom: A complex multi-table Join reports error Query executor exceeded total memory limitation ..., or the plan selects an extremely unreasonable Join strategy (such as a large table driving a large table Nested Loop, etc.);

• Troubleshooting points:

  • Confirm whether related tables are missing statistics or have severely outdated statistics (by combining records in hg_table_statistic and AUTO ANALYZE execution records);

  • Perform column-level ANALYZE on Join columns and filter columns. If necessary, increase sampling precision or enable the fast row count estimation feature;

  • If the issue only occurs in certain extreme data distribution or peak scenarios, consult with technical support to evaluate whether adjustments to the statistics model or related GUCs (such as AUTO ANALYZE thresholds, memory limits, etc.) are needed.

Manual ANALYZE and Serverless Computing

• hg_serverless_computing_enable_analyze_statement

  • Description: Controls whether manual ANALYZE statements can be executed through Serverless tasks;

  • Behavior:

    • When set to on (default), the system allows ANALYZE statements to be offloaded to Serverless computing resources for execution, minimizing the impact of statistics tasks on the instance itself;

    • When set to off, ANALYZE executes on the local instance resources.

  • Typical recommendations:

    • In Serverless instances, the default is on, allowing only Serverless resources to complete statistics tasks;

    • When troubleshooting or when there are special requirements for resource usage, you can temporarily set it to off to explicitly run ANALYZE on local resources.

AUTO ANALYZE and Serverless Computing

When generating AUTO ANALYZE tasks, the system determines whether to use Serverless resources and how to control Serverless task behavior based on the following GUCs:

• hg_auto_analyze_run_with_serverless_computing

  • Description: Whether to execute AUTO ANALYZE tasks through Serverless computing resources. The default is off for regular instances and on for Serverless instances.

  • Behavior:

    • When set to on, AUTO ANALYZE tasks will send sampling computations to the Serverless resource pool;

    • When set to off, AUTO ANALYZE continues to complete statistics through the local instance resources.

-- DB级别设置
ALTER DATABASE datname SET hg_auto_analyze_run_with_serverless_computing = on;

• hg_auto_analyze_serverless_computing_query_priority

  • Description: The priority of Serverless AUTO ANALYZE tasks, ranging from 1~5. Higher values indicate higher priority. The default is 2;

  • Behavior:

    • When Serverless AUTO ANALYZE is enabled, the system sets the corresponding priority for each statistics task through SET hg_experimental_serverless_tasks_query_priority = <value>;;

    • This is applicable when sharing a resource pool with other Serverless tasks (such as ETL and offline queries) to control the preemption capability of statistics tasks in the overall queue;

  • Recommendations:

    • In production environments, it is typically kept at a medium priority (such as the default 2 or moderately increased to 3) to avoid competing for too many resources with core business jobs;

    • For scenarios with extremely high requirements for statistics timeliness and sufficient Serverless resources, the priority can be appropriately increased.

• hg_auto_analyze_serverless_computing_enable_persisted_snapshot

  • Description: Whether to use a persisted snapshot (persisted snapshot) in Serverless AUTO ANALYZE tasks, i.e., controlling whether to skip additional flush through SET hg_experimental_enable_persisted_snapshot = on/off;;

  • The default is off for non-Serverless instances and on for Serverless instances.

  • Behavior:

    • When set to on, statistics tasks will try to reuse the persisted snapshot, reducing the need to trigger a flush. This is more suitable for high-frequency statistics or scenarios with a large number of partitioned tables;

    • When set to off, statistics tasks will try to use a more up-to-date snapshot, which is suitable for scenarios with higher requirements for consistency or specific version isolation.

  • Recommendations:

    • Do not adjust unless necessary.

Usage Recommendations for Serverless Computing Scenarios

1. Prioritize using Serverless Computing to handle heavy ANALYZE tasks

   • When performing ANALYZE on tables or foreign tables with very large data volumes, it is recommended to enable:

     • Manual ANALYZE: set hg_computing_resource = 'serverless'; ANALYZE my_table;;

     • AUTO ANALYZE: ALTER DATABASE mydb SET hg_auto_analyze_run_with_serverless_computing = on;;

   • This migrates the statistics computation pressure to the Serverless resource pool, reducing interference with online queries on the instance.

2. Set Serverless AUTO ANALYZE task priority based on business importance

   • In Serverless instances, for critical business databases that are sensitive to statistics timeliness and depend on the latest statistics, you can appropriately increase hg_auto_analyze_serverless_computing_query_priority;

   • In regular instances, using local resources for AUTO ANALYZE is typically sufficient.

3. Follow the "small step trial" principle before adjusting parameters

   • Serverless Computing related GUCs are also advanced parameters. Before making adjustments, it is recommended to first verify in a test or staging environment:

     • Observe the queuing and execution duration of statistics tasks in the Serverless resource pool;

     • Monitor the impact on latency and resource usage of core business queries;

   • After confirming there are no significant side effects, roll out to the production environment by database or in phases.