(1)分片键上的join条件,直接下推到对应DN执行:
gbase=# EXPLAIN SELECT * FROM td1,td2 WHERE td1.a=td2.c ORDER BY a;
QUERY PLAN
------------------------------------------------------------------------------
Remote Subquery Scan on all (dn1,dn2,dn3) (cost=2.04..2.05 rows=1 width=16)
-> Sort (cost=2.04..2.05 rows=1 width=16)
Sort Key: td1.a
-> Nested Loop (cost=0.00..2.03 rows=1 width=16)
Join Filter: (td1.a = td2.c)
-> Seq Scan on td1 (cost=0.00..1.01 rows=1 width=8)
-> Seq Scan on td2 (cost=0.00..1.01 rows=1 width=8)
(7 rows)
(2)非分片键join条件,DN直接做数据交换,避免CN成为性能瓶颈:
gbase=# EXPLAIN SELECT * FROM td1,td2 WHERE td1.b=td2.b ORDER BY a;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Remote Subquery Scan on all (dn1,dn2,dn3) (cost=2.04..2.05 rows=1 width=16)
-> Sort (cost=2.04..2.05 rows=1 width=16)
Sort Key: td1.a
-> Nested Loop (cost=0.00..2.03 rows=1 width=16)
Join Filter: (td1.b = td2.b)
-> Remote Subquery Scan on all (dn1,dn2,dn3) (cost=100.00..101.02 rows=1 width=8)
Distribute results by H: b
-> Seq Scan on td1 (cost=0.00..1.01 rows=1 width=8)
-> Materialize (cost=100.00..101.03 rows=1 width=8)
-> Remote Subquery Scan on all (dn1,dn2,dn3) (cost=100.00..101.02 rows=1 width=8)
Distribute results by H: b
-> Seq Scan on td2 (cost=0.00..1.01 rows=1 width=8)
(12 rows)
- Join下推到DN执行,DN之间直接进行数据重分布,交换数据,无需CN参与;CBO优化器选择小表t2做重分布;
- Sort下推到DN,CN只需做归并排序,避免CN成为性能瓶颈;