ApsaraDB for SelectDB:Statistics

Syntax

ANALYZE < TABLE | DATABASE table_name | db_name >
    [ (column_name [, ...]) ]
    [ [ WITH SYNC ] [ WITH SAMPLE PERCENT | ROWS ] ];

Parameters

Examples

Collect statistics by sampling 10% of rows:

ANALYZE TABLE lineitem WITH SAMPLE PERCENT 10;

Collect statistics by sampling 100,000 rows:

ANALYZE TABLE lineitem WITH SAMPLE ROWS 100000;

How health score works

Statistics health is a value from 0 to 100. Automatic collection is triggered when all of the following conditions are met:

• The table has been updated since the last collection.

• The health score has dropped below the configured threshold.

• For large tables: the minimum collection interval has elapsed since the last collection.

Health score details:

• A table with no statistics has a health score of 0.

• After each import transaction, SelectDB estimates the new health based on the proportion of rows updated.

• If health falls below table_stats_health_threshold (default: 60), the table is considered stale and scheduled for collection.

Large table strategy

For tables larger than huge_table_lower_bound_size_in_bytes (default: 5 GiB):

• SelectDB uses sampled collection automatically, sampling huge_table_default_sample_rows rows (default: 4,194,304).

• Collection runs at most once per huge_table_auto_analyze_interval_in_millis (default: 12 hours), regardless of health score changes within that window.

To increase sampling depth for more accurate statistics, raise huge_table_default_sample_rows.

Limit collection to off-peak hours

To avoid impacting production workloads, set a time window for automatic collection:

SET auto_analyze_start_time = '02:00:00';
SET auto_analyze_end_time = '06:00:00';

Disable automatic collection

SET enable_auto_analyze = false;

External catalog

Automatic collection is disabled by default for external catalogs to prevent excessive resource consumption from large historical datasets. Enable or disable it per catalog:

-- Enable automatic collection for an external catalog
ALTER CATALOG external_catalog SET PROPERTIES ('enable.auto.analyze'='true');

-- Disable automatic collection for an external catalog
ALTER CATALOG external_catalog SET PROPERTIES ('enable.auto.analyze'='false');

View collection jobs

SHOW [AUTO] ANALYZE < table_name | job_id >
    [ WHERE [ STATE = [ "PENDING" | "RUNNING" | "FINISHED" | "FAILED" ] ] ];

Example:

SHOW ANALYZE 245073\G;

*************************** 1. row ***************************
              job_id: 245073
        catalog_name: internal
             db_name: default_cluster:tpch
            tbl_name: lineitem
            col_name: [l_returnflag,l_receiptdate,l_tax,l_shipmode,l_suppkey,l_shipdate,
                        l_commitdate,l_partkey,l_orderkey,l_quantity,l_linestatus,l_comment,
                        l_extendedprice,l_linenumber,l_discount,l_shipinstruct]
            job_type: MANUAL
       analysis_type: FUNDAMENTALS
             message:
last_exec_time_in_ms: 2023-11-07 11:00:52
               state: FINISHED
            progress: 16 Finished  |  0 Failed  |  0 In Progress  |  16 Total
       schedule_type: ONCE

Output fields:

View table-level statistics

SHOW TABLE STATS <table_name>;

Example:

SHOW TABLE STATS lineitem\G

*************************** 1. row ***************************
updated_rows: 0
 query_times: 0
   row_count: 6001215
updated_time: 2023-11-07
     columns: [l_returnflag, l_receiptdate, l_tax, l_shipmode, l_suppkey, l_shipdate,
               l_commitdate, l_partkey, l_orderkey, l_quantity, l_linestatus, l_comment,
               l_extendedprice, l_linenumber, l_discount, l_shipinstruct]
     trigger: MANUAL

Output fields:

View column-level task status

Each collection job spawns one task per column. To view task status for a specific job:

SHOW ANALYZE TASK STATUS [job_id]

Example:

SHOW ANALYZE TASK STATUS 20038;

+---------+----------+---------+----------------------+----------+
| task_id | col_name | message | last_exec_time_in_ms | state    |
+---------+----------+---------+----------------------+----------+
| 20039   | col4     |         | 2023-06-01 17:22:15  | FINISHED |
| 20040   | col2     |         | 2023-06-01 17:22:15  | FINISHED |
| 20041   | col3     |         | 2023-06-01 17:22:15  | FINISHED |
| 20042   | col1     |         | 2023-06-01 17:22:15  | FINISHED |
+---------+----------+---------+----------------------+----------+

View column statistics

SHOW COLUMN [cached] STATS table_name [ (column_name [, ...]) ];

Example:

SHOW COLUMN STATS lineitem(l_tax)\G

*************************** 1. row ***************************
  column_name: l_tax
        count: 6001215.0
          ndv: 9.0
     num_null: 0.0
    data_size: 4.800972E7
avg_size_byte: 8.0
          min: 0.00
          max: 0.08
       method: FULL
         type: FUNDAMENTALS
      trigger: MANUAL
  query_times: 0
 updated_time: 2023-11-07 11:00:46

Stop a collection job

KILL ANALYZE job_id;

The job ID is returned by ANALYZE in asynchronous mode, or retrieved from SHOW ANALYZE.

Example:

KILL ANALYZE 52357;

Session variables

FE configuration items

These items control background behavior. In most cases, the defaults are sufficient.

The "Stats table not available..." error appears after running ANALYZE

Run SHOW BACKENDS to confirm that all BE nodes are in a normal state. If the BEs look healthy, check the tablet status of the internal statistics table:

ADMIN SHOW REPLICA STATUS FROM __internal_schema.[tbl_in_this_db];

Make sure all tablets show a normal status. The FE checks tablet availability before accepting any ANALYZE request and rejects it if any tablet in __internal_schema.column_statistics is unavailable.

Statistics collection fails on a large table

Use sampled collection instead of a full scan. The resources available to ANALYZE are strictly limited, and a full scan of a large table can time out or exhaust BE memory:

ANALYZE TABLE <table_name> WITH SAMPLE PERCENT 10;

Adjust the sampling ratio based on table size and the accuracy you need.