单表查询,不管SQL的where条件是否带有分片键,优化器都可以生成下推的执行计划,包括sort/group by等复杂算子,都可以下推。
(1)分片键上的where条件,直接下推到对应DN执行:
gbase=# EXPLAIN SELECT * FROM td1 WHERE a=18 ORDER BY b;
QUERY PLAN
-----------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn2
-> Sort (cost=38.44..38.47 rows=11 width=8)
Sort Key: b
-> Seq Scan on td1 (cost=0.00..38.25 rows=11 width=8)
Filter: (a = 18)
(6 rows)
(2)非分片键where条件:DN先计算,CN做结果汇总,group by可以直接下推到DN:
gbase=# EXPLAIN SELECT * FROM td1 WHERE b=18 ORDER BY b;
QUERY PLAN
-----------------------------------------------------------------------------
Remote Subquery Scan on all (dn1,dn2,dn3) (cost=0.00..1.01 rows=1 width=8)
-> Seq Scan on td1 (cost=0.00..1.01 rows=1 width=8)
Filter: (b = 18)
(3 rows)