SQL优化 -- KingbaseES 提升子查询

对于KingbaseES（包括 PostgreSQL），并不是所有的子查询都能提升。当查询含有集合操作、聚合操作、sort/limit/with/group by、volatile函数等，是不支持提升的。 

本文针对Group by复杂子查询如何提升提供修改案例。修改前的例子如下，可以看出因为子查询无法提升，SQL只能走hashjoin。

explain
select * from t01 as a, (select t01id, count(*) tups from t02 group by t01id) as b
where a.id = b.t01id and a.c1 = 100;
                               QUERY PLAN
-----------------------------------------------------------------------------------
Hash Join  (cost=20467.23..20693.85 rows=9 width=53)
  Hash Cond: (t02.t01id = a.id)
  ->  HashAggregate  (cost=20435.00..20535.16 rows=10016 width=12)
        Group Key: t02.t01id
        ->  Seq Scan on t02  (cost=0.00..15435.00 rows=1000000 width=4)
  ->  Hash  (cost=32.11..32.11 rows=9 width=41)
        ->  Bitmap Heap Scan on t01 a  (cost=4.35..32.11 rows=9 width=41)
              Recheck Cond: (c1 = 100)
              ->  Bitmap Index Scan on idx_t01_c1  (cost=0.00..4.35 rows=9 width=0)
                    Index Cond: (c1 = 100)

从执行计划可以看出，连接操作是在group by 操作结束后，针对group by 结果进行连接。也就是group by 子查询没有提升。

解决方案一：any(array()) 改写

使用any和array将关联条件，转化为稳定函数

explain
with a as (select * from t01 as a where a.c1 = 100)
select * from a, (select t01id, count(*) tups from t02 group by t01id) as b
where a.id = b.t01id and  b.t01id = any (array (select id from a)) ;
                               QUERY PLAN
-----------------------------------------------------------------------------------
Hash Join  (cost=2659.27..2684.79 rows=43 width=84)
  Hash Cond: (t02.t01id = a.id)
  CTE a
    ->  Bitmap Heap Scan on t01 a_1  (cost=4.35..32.11 rows=9 width=41)
          Recheck Cond: (c1 = 100)
          ->  Bitmap Index Scan on idx_t01_c1  (cost=0.00..4.35 rows=9 width=0)
                Index Cond: (c1 = 100)
  InitPlan 2 (returns $1)
    ->  CTE Scan on a a_2  (cost=0.00..0.18 rows=9 width=4)
  ->  HashAggregate  (cost=2626.68..2638.63 rows=956 width=44)
        Group Key: t02.t01id
        ->  Bitmap Heap Scan on t02  (cost=52.08..2619.15 rows=1005 width=10)
              Recheck Cond: (t01id = ANY ($1))
              ->  Bitmap Index Scan on idx_t02_t01id  (cost=0.00..51.83 rows=1005 width=0)
                    Index Cond: (t01id = ANY ($1))
  ->  Hash  (cost=0.18..0.18 rows=9 width=40)
        ->  CTE Scan on a  (cost=0.00..0.18 rows=9 width=40)

解决方案二：lateral 改写

explain
select * from t01 as a,
    lateral (select t01id, count(*) tups from t02 as b where a.id = b.t01id group by t01id)
where a.c1 = 100;
                               QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop  (cost=9.56..3390.67 rows=900 width=85)
  ->  Bitmap Heap Scan on t01 a  (cost=4.35..32.11 rows=9 width=41)
        Recheck Cond: (c1 = 100)
        ->  Bitmap Index Scan on idx_t01_c1  (cost=0.00..4.35 rows=9 width=0)
              Index Cond: (c1 = 100)
  ->  GroupAggregate  (cost=5.21..371.17 rows=100 width=44)
        Group Key: b.t01id
        ->  Bitmap Heap Scan on t02 b  (cost=5.21..369.17 rows=101 width=10)
              Recheck Cond: (a.id = t01id)
              ->  Bitmap Index Scan on idx_t02_t01id  (cost=0.00..5.18 rows=101 width=0)
                    Index Cond: (t01id = a.id)

可以看到，修改后的SQL，外部的条件可以传入的 group by子查询内部，也就是子查询可以提升。