PolarDB-X pushes physical SQL statements down to the storage layer as a query rewrite optimization. Operations that run at the storage layer filter data close to where it lives, reduce data transmission between layers, and execute in parallel — all of which speed up query execution.
Use EXPLAIN OPTIMIZER <SQL statement> to inspect the query rewrite process and confirm whether operations are pushed down.
Operations that can be pushed down
PolarDB-X can push the following operations down to the storage layer:
| Operation | Operators |
|---|---|
| Filter | WHERE, HAVING |
| Aggregate | COUNT, GROUP BY |
| Sort | ORDER BY |
| Deduplicate | DISTINCT |
| Function | NOW() and similar functions |
| Join | Same-sharding-strategy tables, broadcast tables |
| Subquery | Converted to SemiJoin or AntiJoin before pushdown |
Push down filter and project operations
EXPLAIN OPTIMIZER select c_custkey,c_name from customer where c_custkey = 1;c_custkey is a partition key of the table.
In this execution plan, the filter operation (WHERE c_custkey = 1) and the project operation (selecting c_custkey and c_name) are pushed down to the LogicalView operator. The storage layer filters rows before returning data, which reduces the volume of data transmitted.
Push down sort and limit operations
EXPLAIN OPTIMIZER select * from customer order by c_name limit 10
Both the sort operation (ORDER BY c_name) and the limit operation (LIMIT 10) are pushed down to LogicalView. Each storage node sorts and limits its local data, so less data flows through the network and memory consumption in PolarDB-X is reduced.
Push down aggregate operations
EXPLAIN OPTIMIZER select count(*) from customer group by c_nationkey;The aggregate operation is pushed down to LogicalView. Results depend on whether c_nationkey is a partition key.
When c_nationkey is a partition key:
When c_nationkey is not a partition key:
JoinClustering
When a query joins multiple tables, the JoinClustering operator re-sorts the join operations to place pushdown-eligible JOIN clauses in adjacent positions. This reordering ensures that the storage layer handles as many joins as possible.
The following example joins three tables — t2, t1, and l2:
EXPLAIN select t2.id from t2 join t1 on t2.id = t1.id join l2 on t1.id = l2.id;
Project(id="id")
HashJoin(condition="id = id AND id = id0", type="inner")
Gather(concurrent=true)
LogicalView(tables="t2_[0-3],l2_[0-3]", shardCount=4, sql="SELECT `t2`.`id`, `l2`.`id` AS `id0` FROM `t2` AS `t2` INNER JOIN `l2` AS `l2` ON (`t2`.`id` = `l2`.`id`) WHERE (`t2`.`id` = `l2`.`id`)")
Gather(concurrent=true)
LogicalView(tables="t1", shardCount=2, sql="SELECT `id` FROM `t1` AS `t1`")The original join order is t2 JOIN t1, then t1 JOIN l2. After JoinClustering reorders the operations, the join between t2 and l2 moves into a single LogicalView and executes at the storage layer. The remaining join with t1 uses a HashJoin at the compute layer.
Push down join operations
For a join to be pushed down to the storage layer, both conditions must be met:
The two tables have identical sharding strategies — the same database shard keys, table shard keys, sharding function, and shard count.
The join condition uses shard key values that are equal across both tables.
Join operations between a broadcast table and any other table type are always pushed down.
EXPLAIN OPTIMIZER select * from t1, t2 where t1.id = t2.id;
When conditions are met, the join executes in parallel across shards in the storage layer, which improves query throughput.
Push down subqueries
Subquery pushdown follows a three-step process:
PolarDB-X converts the subquery to a
SemiJoinorAntiJoinoperation.If the conditions for join pushdown are met, the
SemiJoinorAntiJoinis pushed down toLogicalView.At the storage layer, the
SemiJoinorAntiJoinis converted back to a subquery for execution.
EXPLAIN OPTIMIZER select * from t1 where id in (select id from t2);
When the subquery pushes down successfully, it executes in parallel at the storage layer and avoids transferring the full inner table to the compute layer.