3.4 典型 SQL 调优点
3.4.3 子查询调优
– exist_sublink：对应EXIST、NOT EXIST语句
– any_sublink：对应op Any(select…)语句，其中OP可以是IN,<,>,=操作符 – all_sublink：对应op ALL(select…)语句，其中OP可以是IN,<,>,=操作符 – rowcompare_sublink：对应record op (select …)语句
– expr_sublink：对应(SELECT with single targetlist item ...)语句 – array_sublink：对应ARRAY(select…)语句
– cte_sublink：对应with query(…)语句
其中OLAP、HTAP场景中常用的sublink为exist_sublink、any_sublink，在 GaussDB(DWS)的优化引擎中对其应用场景做了优化（子链接提升），由于SQL 语句中子查询的使用的灵活性，会带来SQL子查询过于复杂造成性能问题。子查 询从大类上来看，分为非相关子查询和相关子查询：
– 非相关子查询None-Correlated SubQuery
子查询的执行不依赖于外层父查询的任何属性值。这样子查询具有独立性， ---Streaming (type: GATHER)
Node/s: All datanodes
– 相关子查询Correlated-SubQuery
子查询的执行依赖于外层父查询的一些属性值（如下列示例t2.c1 = t1.c1条件 中的t1.c1）作为内层查询的一个AND-ed条件。这样的子查询不具备独立 性，需要和外层查询按分组进行求解。
例如：
select t1.c1,t1.c2 from t1 where t1.c1 in ( select c2 from t2
where t2.c1 = t1.c1 AND t2.c2 in (2,3,4) ); QUERY PLAN
---Streaming (type: GATHER)
Node/s: All datanodes -> Seq Scan on t1 Filter: (SubPlan 1) SubPlan 1 -> Result
Filter: (t2.c1 = t1.c1) -> Materialize
-> Streaming(type: BROADCAST) Spawn on: All datanodes -> Seq Scan on t2
Filter: (c2 = ANY ('{2,3,4}'::integer[])) (12 rows)
GaussDB(DWS)对 SubLink 的优化
针对SubLink的优化策略主要是让内层的子查询提升(pullup)，能够和外表直接做关联 查询，从而避免生成SubPlan+Broadcast內表的执行计划。判断子查询是否存在性能风 险，可以通过explain查询语句查看Sublink的部分是否被转换成SubPlan+Broadcast的 执行计划。
例如：
● 目前GaussDB(DWS)支持的Sublink-Release场景 – IN-Sublink无相关条件
▪
不能包含上一层查询的表中的列（可以包含更高层查询表中的列）。▪
不能包含易变函数。– Exist-Sublink包含相关条件
Where子句中必须包含上一层查询的表中的列，子查询的其它部分不能含有 上层查询的表中的列。其它限制如下。
▪
子查询必须有from子句。▪
子查询不能含有with子句。▪
子查询不能含有聚集函数。▪
子查询里不能包含集合操作、排序、limit、windowagg、having操作。▪
不能包含易变函数。– 包含聚集函数的等值相关子查询的提升
子查询的where条件中必须含有来自上一层的列，而且此列必须和子查询本 层涉及表中的列做相等判断，且这些条件必须用and连接。其它地方不能包含 上层的列。其它限制条件如下。
▪
子查询中where条件包含的表达式(列名)必须是表中的列。▪
子查询的Select关键字后，必须有且仅有一个输出列，此输出列必须是聚 集函数(如max)，并且聚集函数的参数(t2.c2)不能是来自外层表(t1)中的 列。聚集函数不能是count。例如，下列示例可以提升。
select * from t1 where c1 >(
select max(t2.c1) from t2 where t2.c1=t1.c1 );
下列示例不能提升，因为子查询没有聚集函数。
select * from t1 where c1 >(
select t2.c1 from t2 where t2.c1=t1.c1 );
下列示例不能提升，因为子查询有两个输出列。
select * from t1 where (c1,c2) >(
select max(t2.c1),min(t2.c2) from t2 where t2.c1=t1.c1 );
▪
子查询必须是from子句。▪
子查询中不能有groupby、having、集合操作。▪
子查询只能是inner join。例如：下列示例不能提升。
select * from t1 where c1 >(
select max(t2.c1) from t2 full join t3 on (t2.c2=t3.c2) where t2.c1=t1.c1 升。select * from t3 where t3.c1=(
select t1.c1
select * from t3 where t3.c1=(
select t1.c1
where t1.a = (select avg(a) from t3 where t1.b = t3.b) or exists (select * from t4 where t1.c = t4.c);
将OR-ed连接的EXIST相关子查询OR字句的提升过程：
i. 提取where条件中，or子句中的opExpr。为：t1.a = (select avg(a) from t3 where t1.b = t3.b)
ii. 这个op操作中包含subquery，判断是否可以提升，如果可以提升，重写 subquery为：select avg(a), t3.b from t3 group by t3.b，生成not null 条件t3.b is not null，并将这个opexpr用这个not null条件替换。此时 SQL变为：
select a, c
from t1 left join (select avg(a) avg, t3.b from t3 group by t3.b) as t3 on (t1.a = avg and t1.b = t3.b)
where t3.b is not null or exists (select * from t4 where t1.c = t4.c);
iii. 再次提取or子句中的exists sublink，exists (select * from t4 where t1.c
= t4.c)，判断是否可以提升，如果可以提升，转换subquery为：select t4.c from t4 group by t4.c生成NotNull条件t4.c is not null提升查询，
SQL变为：
select a, c
from t1 left join (select avg(a) avg, t3.b from t3 group by t3.b) as t3 on (t1.a = avg and t1.b = t3.b)
left join (select t4.c from t4 group by t4.c) where t3.b is not null or t4.c is not null;
● 目前GaussDB(DWS)不支持的Sublink-Release场景
select distinct t1.a, t2.a
from t1 left join t2 on t1.a=t2.a and not exists (select a,b from test1 where test1.a=t1.a and test1.b=t2.a);
改写为
with temp as
( select * from (select t1.a as a, t2.a as b from t1 left join t2 on t1.a=t2.a)
)select distinct a,b from temp
where not exists (select a,b from test1 where temp.a=test1.a and temp.b=test1.b);
– 出现在targetlist里的相关子查询无法提升(不含count) 例如：
explain (costs off)
select (select c2 from t2 where t1.c1 = t2.c1) ssq, t1.c2 from t1
where t1.c2 > 10;
执行计划为：
explain (costs off)
select (select c2 from t2 where t1.c1 = t2.c1) ssq, t1.c2 from t1
where t1.c2 > 10;
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes
说明
SSQ和CSSQ的解释如下：
● SSQ：ScalarSubQuery一般指返回1行1列scalar值的sublink，简称SSQ。
● CSSQ：Correlated-ScalarSubQuery和SSQ相同不过是指包含相关条件的SSQ。
上述SQL语句可以改写为：
with ssq as
( select t2.c2 from t2 )select ssq.c2, t1.c2
from t1 left join ssq on t1.c1 = ssq.c2 where t1.c2 > 10;
改写后的执行计划为：
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes
select (select count(*) from t2 where t2.c1=t1.c1) cnt, t1.c1, t3.c1 from t1,t3
where t1.c1=t3.c1 order by cnt, t1.c1;
执行计划为
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes t1.c1=t2.t1不匹配时需要输出0，因此可以使用一个case-when NULL then 0 else count(*)来代替。
上述SQL语句可以改写为：
with ssq as
( select count(*) cnt, c1 from t2 group by c1 )select case when
ssq.cnt is null then 0 else ssq.cnt
end cnt, t1.c1, t3.c1
from t1 left join ssq on ssq.c1 = t1.c1,t3 where t1.c1 = t3.c1
order by ssq.cnt, t1.c1;
改写后的执行计划为
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes
select t1.c1, t1.c2 from t1
where t1.c1 = (select agg() from t2.c2 > t1.c2);
对于非等值相关条件的SubLink目前无法提升，从语义上可以通过做2次join
（一次CorrelationKey，一次rownum自关联）达到提升改写的目的。
改写方案有两种。
▪
子查询改写方式select t1.c1, t1.c2 from t1, (
select t1.rowid, agg() aggref from t1,t2
where t1.c2 > t2.c2 group by t1.rowid ) dt /* derived table */
where t1.rowid = dt.rowid AND t1.c1 = dt.aggref;
▪
CTE改写方式WITH dt as
( select t1.rowid, agg() aggref from t1,t2
where t1.c2 > t2.c2 group by t1.rowid )select t1.c1, t1.c2
from t1, derived_table
where t1.rowid = derived_table.rowid AND t1.c1 = derived_table.aggref;
须知
create table master_table (a int);
create table sub_table(a int, b int);
select a from master_table group by a having a in (select a from sub_table);
上述事例中存在一个相关性子查询，为了提升查询的性能，可以将sub_table修改为一 个relication表，并且在字段a上创建一个index。
示例2：修改select语句，将子查询修改为和主表的join，或者修改为可以提升的 subquery，但是在修改前后需要保证语义的正确性。
explain (costs off)select * from master_table as t1 where t1.a in (select t2.a from sub_table as t2 where t1.a
= t2.b);
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes
explain(costs off) select * from master_table as t1 where exists (select t2.a from sub_table as t2 where t1.a
= t2.b and t1.a = t2.a);
QUERY PLAN
Streaming (type: GATHER)
Node/s: All datanodes