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.

END