AnalyticDB:Beam sorting optimization (V7.0)

Performance comparison

The following table shows query times for a compound sort key versus an interleaved sort key on the lineorder_flat Beam table (1 TB, Star Schema Benchmark (SSB)). Sort key columns are LO_ORDERDATE and P_BRAND.

Key takeaway: Use a compound sort key when queries filter primarily on the first column or on it with high selectivity. Use an interleaved sort key when queries filter on the second column alone or on multiple columns together.

Define a compound sort key

A compound sort key sorts data by the listed columns in order. Queries that filter on a prefix of those columns—starting from the first—benefit most.

To create a Beam table with a compound sort key, use ORDER BY in the CREATE TABLE statement:

CREATE TABLE beam_example (
    id integer,
    name text,
    ftime timestamp
)
USING beam
DISTRIBUTED BY (id)
ORDER BY(id);

Maintain a compound sort key

After data is written, a background process sorts it automatically based on data volume and file count. To trigger sorting immediately, run OPTIMIZE:

OPTIMIZE beam_example;

Define an interleaved sort key

To create a Beam table with an interleaved sort key, use ZORDER BY in the CREATE TABLE statement:

CREATE TABLE beam_example_interleaved (
    id integer,
    name text,
    ftime timestamp,
    region varchar,
    age integer
)
USING beam
DISTRIBUTED BY (id)
ZORDER BY(name, region, age);

Maintain an interleaved sort key

After data is written, a background process sorts it automatically. As data grows, value distributions can shift, causing data skew that reduces block-skipping efficiency. Check for skew regularly and re-sort when needed.

Check skew for a specific table:

SELECT * FROM adbpg_toolkit.pg_get_interleaved_skew('beam_example_interleaved'::regclass)

Sample output:

 relid | colname  | skew |  suggestion
-------+----------+------+---------------
 17139 | name     | 0.46 |
 17139 | region   | 0.54 | NEED OPTIMIZE
 17139 | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

Check skew across all interleaved sorting tables in the current database:

SELECT * FROM adbpg_toolkit.pg_stat_interleaved_skew;

Sample output:

 relid |         relname          | colname  | skew |  suggestion
-------+--------------------------+----------+------+---------------
 17139 | beam_example_interleaved | name     | 0.46 |
 17139 | beam_example_interleaved | region   | 0.54 | NEED OPTIMIZE
 17139 | beam_example_interleaved | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

Output columns:

When the OVER ALL row shows NEED OPTIMIZE, re-sort the table:

OPTIMIZE beam_example_interleaved;