Configure sorting acceleration-AnalyticDB(AnalyticDB)-阿里云帮助中心

This topic describes how AnalyticDB for PostgreSQL accelerates queries by leveraging the physical order of data.

Running SORT <tablename> physically sorts the table data. AnalyticDB for PostgreSQL then accelerates computations by pushing the SORT operator to the storage layer, leveraging this physical order. This process speeds up SORT, AGG, and JOIN operations for queries that use the sort key.

Note

This feature requires fully sorted data. You must run SORT <tablename> again after any data writes to maintain the sorted state.
Sorting acceleration is enabled by default.

Example

This example compares query times on the test table far before and after sorting acceleration.

Create the test table far with the following statement:

CREATE TABLE far(a int,  b int)
WITH (APPENDONLY=TRUE, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
DISTRIBUTED BY (a)  --distribution key
ORDER BY (a);       --sort key

Insert 1,000,000 rows with the following statement:

INSERT INTO far VALUES(generate_series(0, 1000000), 1);

Sort the imported data with the following statement:
```
SORT far;
```

The following examples compare query performance:

Note Actual query time varies depending on factors such as data volume, computing resources, and network conditions.

Order by acceleration

Before sorting acceleration

postgres=# select * from far order by a limit 1;
 a | b
---+---
 0 | 1
(1 row)
Time: 323.980 ms

With sorting acceleration, the query time drops to 6.971 ms.

select * from far order by a limit 1;
 a | b
----+----
 0 | 1
(1 row)
Time: 6.971 ms

Group by acceleration

Before sorting acceleration

postgres=# select a,count(*) from far  group by a limit 1;
    a    | count
---------+-------
 579229 |     1
(1 row)
Time: 779.368 ms

After sorting acceleration, the query time drops to 6.859 ms.

postgres=# select a, count(*) from far group by a limit 1;
 a | count
---+-------
 0 |     1
(1 row)
Time: 6.859 ms

Join acceleration

Before sorting acceleration, the JOIN query takes 289.075 ms:

postgres=# select * from far t1, far t2 where t1.a = t2.a limit 1;
 a | b | a | b
---+---+---+---
 2 | 1 | 2 | 1
(1 row)
Time: 289.075 ms

After sorting acceleration
Note To use sorting acceleration for JOIN queries, you must disable the ORCA optimizer and enable merge join with the following statements:
```
SET enable_mergejoin TO on;
SET optimizer TO off;
```
, the JOIN query takes 12.315 ms.
```
postgres=# select * from far t1, far t2 where t1.a = t2.a limit 1;
 a | b | a | b
---+---+---+---
 2 | 1 | 2 | 1
(1 row)
Time: 12.315 ms
```

-	Order by	Group by	Join
Before acceleration	323.980 ms	779.368 ms	289.075 ms
After acceleration	6.971 ms	6.859 ms	12.315 ms