浅谈GBase 8c优化器之Join查询下推

（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)

1. Join下推到DN执行，DN之间直接进行数据重分布，交换数据，无需CN参与；CBO优化器选择小表t2做重分布；

2. Sort下推到DN，CN只需做归并排序，避免CN成为性能瓶颈；