Sort Push-Down

Preface

The previous articles discussed the Query Optimization Process and Project and Filter Push-Downof Klustron, and in this article we will discuss Sort-Based Pushdown Optimization for sorting queries.

1. Order By Pushdown

In the execution plan of an SQL query that includes the "order by" clause, the Sort operation is pushed down to the RemoteScan operator.

The Sort pushdown operation is performed asynchronously, with instructions executed in parallel across various data nodes. After filtering the data, the sorted results are fed back to the computing nodes, reducing the workload on those nodes.

Testing and Viewing the Execution Plan of Query Pushdown

To enable Sort-Based Pushdown Optimization, the following parameter needs to be set to true in Klustron:

set enable_remote_orderby_pushdown=true;

Test statement:

select c_zip from customer1 order byc_zip;

View the execution plan:

explain select c_zip fromcustomer1 order by c_zip;

According to the above execution plan, the RemoteScan operator conveys the remote sorting operation to each relevant storage node. The storage nodes then feed the results back to the computing node for Merge Append.

If the Sort-Based Pushdown Optimization feature is turned off, the execution plan will change and the sorting operation will be performed on the computing node.

The demo is as follows:

set enable_remote_orderby_pushdown=false;

Sorting operation is performed on the computing node:

Execution process of the statement: After being rewritten on the computing node, the statement is executed on two data nodes, pulling the values that meet the conditions to the computing node for sorting, and then feeding the sorted results back to the client.

2. Performance Comparison

Performance Comparison Environment:

Click the link below to log in to the Klustron online experience system:

zettatech.tpddns.cn:8000/ci/index.php/Main/PGList

在 SQL 框中输入排序操作的语句，按执行键执行。

Enter the SQL statement with sorting operation in the SQL box and click the execute button.

The left window displays the execution information of Klustron community version (which does not support Sort-Based Pushdown Optimization), and the right window displays the execution information of Klustron enterprise version with pushdown operation.

Comparison shows that after "order by" pushdown, the execution efficiency is improved (the execution time decreased from 4 milliseconds to 3 milliseconds).

END