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

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

After you run SORT <tablename>, the system sorts the table data. After the data is sorted, AnalyticDB for PostgreSQL can use the physical order of the data to push SORT operators down to the storage layer for computation acceleration. If your SQL statements can take advantage of the physical order of the underlying data, you can benefit from the acceleration. This feature accelerates SORT, AGG, and JOIN operators based on the sort key.

Note

Sorting acceleration requires the data to be fully ordered. After you write data, run SORT <tablename> again to sort the data.
Sorting acceleration is enabled by default.

Example

In the following example, the same query statement is executed on the test table far to compare the query time before and after sorting acceleration.

Create the test table far. The statement is as follows:

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

Write 1,000,000 rows of data. The statement is as follows:
```
INSERT INTO far VALUES(generate_series(0, 1000000), 1);
```
After the data is imported, sort the data. The statement is as follows:
```
SORT far;
```

The query performance comparison is as follows:

Note The query times in this example are for reference only. Query time is affected by multiple factors such as data volume, computing resources, and network conditions. The actual query time prevails.

ORDER BY acceleration

Before sorting acceleration (unsorted data):

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

After sorting acceleration, the query takes only 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 (unsorted data):

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

After sorting acceleration:

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 (unsorted data), 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 enable JOIN sorting acceleration, disable ORCA and enable merge join. The statements are as follows:
```
SET enable_mergejoin TO on;
SET optimizer TO off;
```
  The JOIN query is executed to verify the effect of sorting acceleration, and the 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