AnalyticDB:Accelerate queries on column-oriented tables using sort keys and rough indexes

Choose a sorting method

Use these guidelines to select the right method for your query patterns:

• If most queries filter on the leading column of the sort key, use compound sorting. It produces the fastest response times for leading-column predicates. Note that re-sorting with compound sorting takes longer than with interleaved sorting because it performs additional data analysis.

• If queries filter on non-leading columns or on arbitrary subsets of the sort key, use interleaved sorting. An interleaved sort key supports up to eight columns. The more sort key columns a query references, the greater the performance benefit.

• If you are unsure, start with compound sorting. It is the simpler choice and performs best when queries have a clear leading filter column.

TPC-H benchmark: sort key impact on range queries

This section demonstrates how compound sorting improves query performance for rough set indexes compared to a full table scan, using a TPC-H Lineitem table that stores seven years of data.

This implementation of TPC is derived from the TPC Benchmark and is not comparable to published TPC Benchmark results, as this implementation does not comply with all the requirements of the TPC Benchmark.

Test setup:

1. Create a 32-node instance.

2. Write 13 billion rows to the Lineitem table.

3. Query data in the time range from 1997-09-01 to 1997-09-30, comparing results when data is sorted by l_shipdate versus unsorted.

Compound vs. interleaved: performance across query shapes

The following example uses two tables with identical data and sort keys to show how the two methods perform across different query shapes.

Test setup:

• Two tables (test and test_multi), each with four columns: id, num1, num2, value

• Sort key: (id, num1, num2) on both tables

• 10 million rows per table

• test sorted with compound sorting (SORT test)

• test_multi sorted with interleaved sorting (MULTISORT test_multi)

Create tables and insert data:

CREATE TABLE test (id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

CREATE TABLE test_multi (id int, num1 int, num2 int, value varchar)
WITH (APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY (id)
ORDER BY (id, num1, num2);

INSERT INTO test (id, num1, num2, value)
SELECT g,
    (random() * 10000000)::int,
    (random() * 10000000)::int,
    (ARRAY['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 + 1)]
FROM generate_series(1, 10000000) AS g;

INSERT INTO test_multi SELECT * FROM test;

SORT test;
MULTISORT test_multi;

Point query performance

All three queries filter on the sort key columns, but at different positions.

-- Q1: filter on the leading column (id)
SELECT * FROM test WHERE id = 100000;
SELECT * FROM test_multi WHERE id = 100000;

-- Q2: filter on the second column (num1)
SELECT * FROM test WHERE num1 = 8766963;
SELECT * FROM test_multi WHERE num1 = 8766963;

-- Q3: filter on the second and third columns (num1, num2)
SELECT * FROM test WHERE num1 = 100000 AND num2 = 2904114;
SELECT * FROM test_multi WHERE num1 = 100000 AND num2 = 2904114;

Range query performance

-- Q1: range filter on the leading column (id)
SELECT count(*) FROM test WHERE id > 5000 AND id < 100000;
SELECT count(*) FROM test_multi WHERE id > 5000 AND id < 100000;

-- Q2: range filter on the second column (num1)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000;

-- Q3: range filter on the second and third columns (num1, num2)
SELECT count(*) FROM test WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000;
SELECT count(*) FROM test_multi WHERE num1 > 5000 AND num1 < 100000 AND num2 < 100000;

Key takeaways

• Compound sorting wins on the leading column. Q1 results show compound sorting has a shorter query response time than interleaved sorting when the filter targets the first sort key column.

• Interleaved sorting wins on non-leading columns. Q2 and Q3 results show interleaved sorting significantly outperforms compound sorting when queries bypass the leading column.

• Interleaved sorting scales with column count. The more sort key columns a query references, the larger the performance advantage of interleaved sorting (Q3 vs. Q2).

This test uses 10 million rows — a modest size for AnalyticDB for PostgreSQL. The performance difference between the two methods is more pronounced with larger tables.