算子下推是GBase 8c关键技术之一,可以把各种复杂的SQL进行下推执行,最小化数据移动,这是相对于基于分库分表的中间件方案的核心优势。
1.1 单表查询下推
单表查询,不管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)
1.2 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)
l Join下推到DN执行,DN之间直接进行数据重分布,交换数据,无需CN参与;CBO优化器选择小表t2做重分布;
l Sort下推到DN,CN只需做归并排序,避免CN成为性能瓶颈;