All Products
Search

This Product

    MaxCompute:Optimizer

    Document Center

    MaxCompute:Optimizer

    Last Updated:Aug 21, 2026

    The MaxCompute optimizer is cost-based. It uses metadata, such as the number of rows and average string length, to accurately estimate costs. This topic describes how to collect metadata in MaxCompute to optimize query performance.

    Background information

    Without accurate metadata, the optimizer can miscalculate costs and generate inefficient execution plans. Therefore, metadata is essential for the optimizer. Table metadata primarily consists of column statistics (Column stats). These statistics are used to calculate other metadata.

    MaxCompute provides two methods to collect statistics:

    • Asynchronous collection framework (Analyze): You can collect statistics asynchronously using the analyze command. This method must be initiated manually.

      Note

      The MaxCompute client version must be 0.35 or later.

    • Synchronous collection framework (Freeride): This method automatically collects column stats as data is generated. It is more automated but can affect query latency.

    MaxCompute collects the following column stats metrics for different data types.

    Column stats metric/Data type

    Numeric types (TINYINT, SMALLINT, INT, BIGINT, DOUBLE, DECIMAL, NUMERIC)

    Character types (STRING, VARCHAR, CHAR)

    Binary type (BINARY)

    Boolean type (BOOLEAN)

    Date types (TIMESTAMP, DATE, INTERVAL)

    Complex types (MAP, STRUCT, ARRAY)

    min (minimum)

    Y

    N

    N

    N

    Y

    N

    max (maximum)

    Y

    N

    N

    N

    Y

    N

    nNulls (number of nulls)

    Y

    Y

    Y

    Y

    Y

    Y

    avgColLen (average column length)

    N

    Y

    Y

    N

    N

    N

    maxColLen (maximum column length)

    N

    Y

    Y

    N

    N

    N

    ndv (number of distinct values)

    Y

    Y

    Y

    Y

    Y

    N

    topK (top K most frequent values)

    Y

    Y

    Y

    Y

    Y

    N

    Note

    Y indicates that the metric is supported. N indicates that the metric is not supported.

    Scenarios

    The following table describes the scenarios for each column stats metric.

    Column stats metric

    Feature

    Scenario

    Description

    min (minimum) or max (maximum)

    Obtains the minimum or maximum value to improve optimization accuracy.

    Scenario 1: Estimate the number of output records.

    If only the data type is provided, the value range is large. If the minimum and maximum values are provided, the optimizer can more accurately estimate the selectivity of filter conditions and generate a better execution plan.

    Scenario 2: Push down filter conditions to the storage layer to reduce the amount of data read.

    In MaxCompute, a filter condition such as a < -90 can be pushed down to the storage layer to reduce data reads. However, a condition such as a + 100 < 10 cannot be pushed down because of the potential for `a` to overflow. If the maximum value of `a` is known, the optimizer can safely transform the second condition into an equivalent one that can be pushed down. This allows more conditions to be pushed down, reducing data reads and saving costs.

    nNulls (number of nulls)

    Uses information about null values to improve evaluation efficiency.

    Scenario 1: Reduce NULL checks when a job runs.

    When a job runs, it must check for NULL values for any data type. If `nNulls` is known to be 0, this check can be skipped to improve computing performance.

    Scenario 2: Clip data based on filter conditions.

    If all values in a column are NULL, a typical filter condition can be converted to an always false condition and then clipped. This improves clipping efficiency.

    avgColLen (average column length) or maxColLen (maximum column length)

    Obtains column length information to estimate resource consumption and reduce shuffles.

    Scenario 1: Estimate memory for hash clustered tables.

    For example, `avgColLen` can be used to estimate the memory consumption of variable-length fields and, in turn, the memory consumption of a record. This allows the optimizer to selectively perform an Auto MapJoin, which builds a hash clustered table and uses a broadcast mechanism to eliminate a shuffle operation. For scenarios with large input tables, eliminating a shuffle significantly improves performance.

    Scenario 2: Reduce the amount of data shuffled.

    None.

    ndv (number of distinct values)

    Uses cardinality information to improve the quality of the execution plan.

    Scenario 1: Estimate the number of output records for a JOIN.

    • Data bloat: When the `ndv` of the join keys in both tables is much smaller than the number of rows, it indicates a high number of duplicate values and a high probability of data bloat. The optimizer can take measures to avoid problems caused by data bloat.

    • Data filtering: When the `ndv` of the smaller table is much smaller than that of the larger table, a large amount of data from the larger table will be filtered out after the JOIN operation. The optimizer can use this information to make optimization decisions.

    Scenario 2: JOIN ordering.

    Based on the estimated number of output records, the optimizer can also automatically adjust the JOIN order. For example, it can move JOIN operations that filter data to an earlier stage and move JOIN operations that cause data bloat to a later stage.

    topK (top K most frequent values)

    Estimates data distribution to reduce the performance impact of data skew.

    Scenario 1: Optimize JOIN operations on skewed data.

    When the inputs for a JOIN are both large and the smaller table cannot be fully loaded into memory for a MapJoin, one data stream may be skewed while others are not. MaxCompute can automatically convert the operation to use a MapJoin for the skewed data and a MergeJoin for the non-skewed data. The results from both parts are then merged. This feature provides significant benefits for large-volume JOINs and reduces the manual effort required after a failure.

    Scenario 2: Estimate the number of output records.

    Estimating the number of output records using `ndv`, `min`, and `max` assumes an even data distribution. If data is significantly skewed, this assumption leads to inaccurate estimations. Skewed data is handled as a special case, and the even distribution assumption is applied to the remaining data.

    Instructions for using Analyze

    Collect column stats

    This section describes how to use Analyze with partitioned and non-partitioned tables.

    • Non-partitioned tables

      You can collect column stats for specific columns or all columns.

      1. Create a non-partitioned table named `analyze2_test` using the MaxCompute client. The following is a sample command:

        create table if not exists analyze2_test (tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, double1 double, decimal1 decimal, decimal2 decimal(20,10), string1 string, varchar1 varchar(10), boolean1 boolean, timestamp1 timestamp, datetime1 datetime ) lifecycle 30;

      2. Insert data into the table. The following is a sample command:

        insert overwrite table analyze2_test select * from values (1Y, 20S, 4, 8L, 123452.3, 12.4, 52.5, 'str1', 'str21', false, timestamp '2018-09-17 00:00:00', datetime '2018-09-17 00:59:59') ,(10Y, 2S, 7, 11111118L, 67892.3, 22.4, 42.5, 'str12', 'str200', true, timestamp '2018-09-17 00:00:00', datetime '2018-09-16 00:59:59') ,(20Y, 7S, 4, 2222228L, 12.3, 2.4, 2.57, 'str123', 'str2', false, timestamp '2018-09-18 00:00:00', datetime '2018-09-17 00:59:59') ,(null, null, null, null, null, null, null, null, null, null, null , null) as t(tinyint1, smallint1, int1, bigint1, double1, decimal1, decimal2, string1, varchar1, boolean1, timestamp1, datetime1);

      3. Run the analyze command to collect column stats for a single column, multiple columns, or all columns. The following are sample commands:

        -- Collect column stats for the tinyint1 column.
analyze table analyze2_test compute statistics for columns (tinyint1); 

-- Collect column stats for the smallint1, string1, boolean1, and timestamp1 columns.
analyze table analyze2_test compute statistics for columns (smallint1, string1, boolean1, timestamp1);

-- Collect column stats for all columns.
analyze table analyze2_test compute statistics for columns;

      4. Run the show statistic command to check the collected column stats. The following are sample commands:

        -- Check the collected column stats for the tinyint1 column.
show statistic analyze2_test columns (tinyint1);

-- Check the collected column stats for the smallint1, string1, boolean1, and timestamp1 columns.
show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

-- Check the collected column stats for all columns.
show statistic analyze2_test columns;

        The following results are returned:

        -- Collected column stats for the tinyint1 column.
ID = 20201126085225150gnqo****
tinyint1:MaxValue:      20                   -- Corresponds to max.
tinyint1:DistinctNum:   4.0                  -- Corresponds to ndv.
tinyint1:MinValue:      1                    -- Corresponds to min.
tinyint1:NullNum:       1.0                  -- Corresponds to nNulls.
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- Corresponds to topK. 10=1.0 indicates that the value 10 appears once. topK displays up to the top 20 most frequent values.

-- Collected column stats for the smallint1, string1, boolean1, and timestamp1 columns.
ID = 20201126091636149gxgf****
smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

string1:MaxLength       6.0                  -- Corresponds to maxColLen.
string1:AvgLength:      3.0                  -- Corresponds to avgColLen.
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

-- Collected column stats for all columns.
ID = 20201126092022636gzm1****
tinyint1:MaxValue:      20
tinyint1:DistinctNum:   4.0
tinyint1:MinValue:      1
tinyint1:NullNum:       1.0
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}

smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

int1:MaxValue:  7
int1:DistinctNum:       3.0
int1:MinValue:  4
int1:NullNum:   1.0
int1:TopK:      {4=2.0, 7=1.0}

bigint1:MaxValue:       11111118
bigint1:DistinctNum:    4.0
bigint1:MinValue:       8
bigint1:NullNum:        1.0
bigint1:TopK:   {8=1.0, 2222228=1.0, 11111118=1.0}

double1:MaxValue:       123452.3
double1:DistinctNum:    4.0
double1:MinValue:       12.3
double1:NullNum:        1.0
double1:TopK:   {12.3=1.0, 67892.3=1.0, 123452.3=1.0}

decimal1:MaxValue:      22.4
decimal1:DistinctNum:   4.0
decimal1:MinValue:      2.4
decimal1:NullNum:       1.0
decimal1:TopK:  {2.4=1.0, 12.4=1.0, 22.4=1.0}

decimal2:MaxValue:      52.5
decimal2:DistinctNum:   4.0
decimal2:MinValue:      2.57
decimal2:NullNum:       1.0
decimal2:TopK:  {2.57=1.0, 42.5=1.0, 52.5=1.0}

string1:MaxLength       6.0
string1:AvgLength:      3.0
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

varchar1:MaxLength      6.0
varchar1:AvgLength:     3.0
varchar1:DistinctNum:   4.0
varchar1:NullNum:       1.0
varchar1:TopK:  {str2=1.0, str200=1.0, str21=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

datetime1:DistinctNum:  3.0
datetime1:NullNum:      1.0
datetime1:TopK:         {1537117199000=2.0, 1537030799000=1.0}

    • Partitioned tables

      You can collect column stats for a specific partition.

      1. The following example shows the command to create a partitioned table named srcpart using the MaxCompute client:

        create table if not exists srcpart_test (key string, value string) partitioned by (ds string, hr string) lifecycle 30;

      2. Insert data into the table. The following is a sample command:

        insert into table srcpart_test partition(ds='20201220', hr='11') values ('123', 'val_123'), ('76', 'val_76'), ('447', 'val_447'), ('1234', 'val_1234');
insert into table srcpart_test partition(ds='20201220', hr='12') values ('3', 'val_3'), ('12331', 'val_12331'), ('42', 'val_42'), ('12', 'val_12');
insert into table srcpart_test partition(ds='20201221', hr='11') values ('543', 'val_543'), ('2', 'val_2'), ('4', 'val_4'), ('9', 'val_9');
insert into table srcpart_test partition(ds='20201221', hr='12') values ('23', 'val_23'), ('56', 'val_56'), ('4111', 'val_4111'), ('12333', 'val_12333');

      3. Run the analyze command to collect column stats for a specific partition. The following is a sample command:

        analyze table srcpart_test partition(ds='20201221') compute statistics for columns (key , value);

      4. Run the show statistic command to check the collected column stats. The following is a sample command:

        show statistic srcpart_test partition (ds='20201221') columns (key , value);

        The following results are returned:

        ID = 20210105121800689g28p****
(ds=20201221,hr=11) key:MaxLength       3.0
(ds=20201221,hr=11) key:AvgLength:      1.0
(ds=20201221,hr=11) key:DistinctNum:    4.0
(ds=20201221,hr=11) key:NullNum:        0.0
(ds=20201221,hr=11) key:TopK:   {2=1.0, 4=1.0, 543=1.0, 9=1.0}

(ds=20201221,hr=11) value:MaxLength     7.0
(ds=20201221,hr=11) value:AvgLength:    5.0
(ds=20201221,hr=11) value:DistinctNum:  4.0
(ds=20201221,hr=11) value:NullNum:      0.0
(ds=20201221,hr=11) value:TopK:         {val_2=1.0, val_4=1.0, val_543=1.0, val_9=1.0}

(ds=20201221,hr=12) key:MaxLength       5.0
(ds=20201221,hr=12) key:AvgLength:      3.0
(ds=20201221,hr=12) key:DistinctNum:    4.0
(ds=20201221,hr=12) key:NullNum:        0.0
(ds=20201221,hr=12) key:TopK:   {12333=1.0, 23=1.0, 4111=1.0, 56=1.0}

(ds=20201221,hr=12) value:MaxLength     9.0
(ds=20201221,hr=12) value:AvgLength:    7.0
(ds=20201221,hr=12) value:DistinctNum:  4.0
(ds=20201221,hr=12) value:NullNum:      0.0
(ds=20201221,hr=12) value:TopK:         {val_12333=1.0, val_23=1.0, val_4111=1.0, val_56=1.0}

    Refresh the record count of a table in metadata

    Various tasks in MaxCompute can change the number of records in a table. However, these tasks may not always update the record count accurately. This can happen because of the dynamic nature of distributed tasks and the timing of data updates. To ensure the record count is accurate, you can use the `analyze` command to refresh the value in the metadata. You can view the record count of a table in DataWorks Data Map. For more information, see View table details.

    • To refresh the record count for the entire table:

      set odps.sql.analyze.table.stats=only; 
analyze table <table_name> compute statistics for columns;

      table_name is the name of the table.

    • To refresh the record count for a specific column in the table:

      set odps.sql.analyze.table.stats=only; 
analyze table <table_name> compute statistics for columns (<column_name>);

      table_name is the name of the table, and column_name is the name of the column.

    • To refresh the record count for a specific column in a partition:

      set odps.sql.analyze.table.stats=only; 
analyze table <table_name> partition(<pt_spec>) compute statistics for columns (<column_name>);

      table_name is the name of the table, pt_spec is the partition value, and column_name is the name of the column.

    Instructions for using Freeride

    You can run the following two commands at the session level to set the properties:

    • set odps.optimizer.stat.collect.auto=true;: Enables the Freeride feature to automatically collect column stats for tables.

    • set odps.optimizer.stat.collect.plan=xx;: Configures a collection plan to collect specific column stats metrics for specified columns.

      -- Collect the avgColLen metric for the key column in the target_table table.
set odps.optimizer.stat.collect.plan={"target_table":"{\"key\":\"AVG_COL_LEN\"}"}

-- Collect the min and max metrics for the s_binary column and the topK and nNulls metrics for the s_int column in the target_table table.
set odps.optimizer.stat.collect.plan={"target_table":"{\"s_binary\":\"MIN,MAX\",\"s_int\":\"TOPK,NULLS\"}"};
    Note

    If statistics are not collected after you configure these properties, the Freeride feature may not be enabled. You can check the json summary tab in Logview for the odps.optimizer.stat.collect.auto property. If the property is not found, your server version is too low to support this feature. Alibaba Cloud will gradually upgrade MaxCompute servers to versions that support Freeride.

    The following list shows the mapping between column stats metrics and their identifiers in the set odps.optimizer.stat.collect.plan=xx; command:

    • min: MIN

    • max: MAX

    • nNulls: NULLS

    • avgColLen: AVG_COL_LEN

    • maxColLen: MAX_COL_LEN

    • ndv: NDV

    • topK: TOPK

    MaxCompute can trigger Freeride to collect column stats when you use create table, insert into, or insert overwrite.

    To demonstrate these three methods, first create a source table named `src_test` and insert data into it. The following are sample commands:

    create table if not exists src_test (key string, value string);
insert overwrite table src_test values ('100', 'val_100'), ('100', 'val_50'), ('200', 'val_200'), ('200', 'val_300');

    • create table: Column stats are collected when you create the target table. The following is a sample command:

      -- Create the target table.
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"target_test":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
create table target_test as select key, value from src_test;
-- Check the collected column stats.
show statistic target_test columns;

      The following results are returned:

      key:AvgLength: 3.0
key:NullNum:  0.0

    • insert into: Column stats are collected when you append data using insert into. The following is a sample command:

      -- Create a target table.
create table freeride_insert_into_table like src_test;
-- Append data.
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"freeride_insert_into_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
insert into table freeride_insert_into_table select key, value from src order by key, value limit 10;
-- Check the collected column stats.
show statistic freeride_insert_into_table columns;

    • insert overwrite: Column stats are collected when you overwrite data using insert overwrite. The following is a sample command:

      -- Create a target table.
create table freeride_insert_overwrite_table like src_test;
-- Overwrite data.
set odps.optimizer.stat.collect.auto=true;
set odps.optimizer.stat.collect.plan={"freeride_insert_overwrite_table":"{\"key\":\"AVG_COL_LEN,NULLS\"}"};
insert overwrite table freeride_insert_overwrite_table select key, value from src_test order by key, value limit 10;
-- Check the collected column stats.
show statistic freeride_insert_overwrite_table columns;