3.4 典型 SQL 调优点
3.4.2 语句下推调优
语句下推介绍
目前，GaussDB(DWS)优化器在分布式框架下制定语句的执行策略时，有三种执行计 划方式：生成下推语句计划、生成分布式执行计划、生成发送语句的分布式执行计 划。
● 下推语句计划：指直接将查询语句从CN发送到DN进行执行，然后将执行结果返 回给CN。
● 分布式执行计划：指CN对查询语句进行编译和优化，生成计划树，再将计划树发 送给DN进行执行，并在执行完毕后返回结果到CN。
● 发送语句的分布式执行计划：上述两种方式都不可行时，将可下推的查询部分组 成查询语句（多为基表扫描语句）下推到DN进行执行，获取中间结果到CN，然 后在CN执行剩下的部分。
在第3种策略中，要将大量中间结果从DN发送到CN，并且要在CN运行不能下推的部 分语句，会导致CN成为性能瓶颈（带宽、存储、计算等）。在进行性能调优的时候，
应尽量避免只能选择第3种策略的查询语句。
执行语句不能下推是因为语句中含有不支持下推的函数或者不支持下推的语法。一般 都可以通过等价改写规避执行计划不能下推的问题。
查看执行计划是否下推
执行计划是否下推可以依靠如下方法快速判断：
步骤1 将GUC参数“enable_fast_query_shipping”设置为off，使查询优化器使用分布式框 架策略。
SET enable_fast_query_shipping = off;
步骤2 查看执行计划。
如果执行计划中有Data Node Scan节点，那么此执行计划为不可下推的执行计划；如 果执行计划中有Streaming节点，那么计划是可以下推的。
例如如下业务SQL：
select
count(ss.ss_sold_date_sk order by ss.ss_sold_date_sk)c1 from store_sales ss, store_returns sr
where
sr.sr_customer_sk = ss.ss_customer_sk;
执行计划如下，可以看出此SQL语句不能下推。
QUERY PLAN
---Aggregate
-> Hash Join
Hash Cond: (ss.ss_customer_sk = sr.sr_customer_sk)
-> Data Node Scan on store_sales "_REMOTE_TABLE_QUERY_"
Node/s: All datanodes -> Hash
-> Data Node Scan on store_returns "_REMOTE_TABLE_QUERY_"
Node/s: All datanodes (8 rows)
----结束
不支持下推的语法
以如下三个表定义说明不支持下推的SQL语法。
CREATE TABLE CUSTOMER1 ( C_CUSTKEY BIGINT NOT NULL , C_MKTSEGMENT CHAR(10) NOT NULL , C_COMMENT VARCHAR(117) NOT NULL )DISTRIBUTE BY hash(C_CUSTKEY);
CREATE TABLE test_stream(a int,b float); --float不支持重分布 CREATE TABLE sal_emp ( c1 integer[] ) DISTRIBUTE BY replication;
● 不支持returning语句下推
explain update customer1 set C_NAME = 'a' returning c_name;
QUERY PLAN Update on customer1 (cost=0.00..0.00 rows=30 width=187) Node/s: All datanodes
Node expr: c_custkey
-> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=187) Node/s: All datanodes
(5 rows)
● count(distinct expr)中的字段不支持重分布，则不支持下推
explain verbose select count(distinct b) from test_stream;
QUERY PLAN
--- Aggregate (cost=2.50..2.51 rows=1 width=8) Output: count(DISTINCT test_stream.b)
-> Data Node Scan on test_stream "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8) Output: test_stream.b
Node/s: All datanodes
Remote query: SELECT b FROM ONLY public.test_stream WHERE true (6 rows)
● 不支持distinct on用法下推
explain verbose select distinct on (c_custkey) c_custkey from customer1 order by c_custkey;
QUERY PLAN
--- Unique (cost=49.83..54.83 rows=30 width=8) Output: customer1.c_custkey
-> Sort (cost=49.83..52.33 rows=30 width=8) Output: customer1.c_custkey
Sort Key: customer1.c_custkey
-> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8)
explain select * from test_stream t1 full join test_stream t2 on t1.a=t2.b;
QUERY PLAN
--- Hash Full Join (cost=0.38..0.82 rows=30 width=24)
Hash Cond: ((t1.a)::double precision = t2.b)
-> Data Node Scan on test_stream "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=12) Node/s: All datanodes
-> Hash (cost=0.00..0.00 rows=30 width=12)
-> Data Node Scan on test_stream "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=12)
Node/s: All datanodes (7 rows)
● 不支持数组表达式下推
explain verbose select array[c_custkey,1] from customer1 order by c_custkey;
QUERY PLAN
--- Sort (cost=49.83..52.33 rows=30 width=8) Output: (ARRAY[customer1.c_custkey, 1::bigint]), customer1.c_custkey
Sort Key: customer1.c_custkey
-> Data Node Scan on "__REMOTE_SORT_QUERY__" (cost=0.00..0.00 rows=30 width=8) Output: (ARRAY[customer1.c_custkey, 1::bigint]), customer1.c_custkey
Node/s: All datanodes
Remote query: SELECT ARRAY[c_custkey, 1::bigint], c_custkey FROM ONLY public.customer1 WHERE true ORDER BY 2
(7 rows)
● With Recursive当前版本不支持下推的场景和原因如下：
序号 场景 不下推原因
1 包含外表、HDFS表的查询场景 LOG: SQL can't be shipped, reason: RecursiveUnion contains HDFS Table or ForeignScan is not shippable（LOG为CN日志中 打印的不下推原因，下同）
外表、HDFS表，当前版本暂不支 持下推。
序号 场景 不下推原因
2 多nodegroup场景 LOG: SQL can't be shipped, reason: With-Recursive under multi-nodegroup scenario is not shippable
基表存储nodegroup不相同，或者 计算nodegroup与基表不相同，当 前版本暂不支持下推。
3 WITH recursive t_result AS ( SELECT dm,sj_dm,name,1 as level FROM test_rec_part
WHERE sj_dm > 10 UNION
SELECT t2.dm,t2.sj_dm,t2.name||' > '||
t1.name,t1.level+1 FROM t_result t1
JOIN test_rec_part t2 ON t2.sj_dm = t1.dm )SELECT * FROM t_result t;
LOG: SQL can't be shipped, reason: With-Recursive does not contain "ALL" to bind recursive
& none-recursive branches UNION不带ALL，需要去重。
4 WITH RECURSIVE x(id) AS
(select count(1) from pg_class where oid=1247
UNION ALL
SELECT id+1 FROM x WHERE id < 5 ), y(id) AS
(select count(1) from pg_class where oid=1247
UNION ALL
SELECT id+1 FROM x WHERE id < 10 )SELECT y.*, x.* FROM y LEFT JOIN x USING (id) ORDER BY 1;
LOG: SQL can't be shipped, reason: With-Recursive contains system table is not shippable 基表中有系统表。
5 WITH RECURSIVE t(n) AS ( VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100 )SELECT sum(n) FROM t;
LOG: SQL can't be shipped, reason: With-Recursive contains only values rte is not shippable 基表扫描只有VALUES子句，仅在 CN上即可完成执行。
6 select a.ID,a.Name, (with recursive cte as (
select ID, PID, NAME from b where b.ID = 1union all
select parent.ID,parent.PID,parent.NAME from cte as child join b as parent on child.pid=parent.id
where child.ID = a.ID )select NAME from cte limit 1 ) cName
from(
select id, name, count(*) as cnt from a group by id,name ) a order by 1,2;
LOG: SQL can't be shipped, reason: With-Recursive recursive term correlated only is not shippable
相关子查询的关联条件仅在递归部 分，非递归部分无关联条件。
序号 场景 不下推原因 7 WITH recursive t_result AS (
select * from(
SELECT dm,sj_dm,name,1 as level FROM test_rec_part
WHERE sj_dm < 10 order by dm limit 6 offset 2)
UNION all
SELECT t2.dm,t2.sj_dm,t2.name||' > '||
t1.name,t1.level+1 FROM t_result t1
JOIN test_rec_part t2 ON t2.sj_dm = t1.dm )SELECT * FROM t_result t;
LOG: SQL can't be shipped, reason: With-Recursive contains conflict distribution in none-recursive(Replicate)
recursive(Hash)
非递归部分带limit为Replicate计 划，递归部分为Hash计划，计划 存在冲突。
8 with recursive cte as
(select * from rec_tb4 where id<4 union all
select h.id,h.parentID,h.name from (with recursive cte as
(select * from rec_tb4 where id<4 union all
select h.id,h.parentID,h.name from rec_tb4 h inner join cte c on h.id=c.parentID
)SELECT id ,parentID,name from cte order by parentID
) hinner join cte c on h.id=c.parentID )SELECT id ,parentID,name from cte order by parentID,1,2,3;
LOG: SQL can't be shipped, reason: Recursive CTE references recursive CTE "cte"
多层Recursive嵌套，即recursive
● random如果出现CTE中，也不下推。因为这种场景下下推可能出现结果错误。
对于用户自定义函数，可以在创建函数的时候指定provolatile和proshippable属性的 值，详细请参考CREATE FUNCTION。
对于函数不能下推的场景：
● 如果是系统函数，建议根据业务等价替换这个函数。
● 如果是自定义函数，建议分析客户业务场景，看函数的provolatile和proshippable 属性定义是否正确。
实例分析：自定义函数
对于自定义函数，如果对于确定的输入，有确定的输出，则应将函数定义为 immutable类型。
利用TPCDS的销售信息举个例子，比如我们要写一个函数，获取商品的打折情况，需 要一个计算折扣的函数，我们可以将这个函数定义为：
CREATE FUNCTION func_percent_2 (NUMERIC, NUMERIC) RETURNS NUMERIC AS 'SELECT $1 / $2 WHERE $2 > 0.01'
LANGUAGE SQL VOLATILE;
执行下列语句：
SELECT func_percent_2(ss_sales_price, ss_list_price) FROM store_sales;
其执行计划为：
可见，func_percent_2并没有被下推，而是将ss_sales_price和ss_list_price收到CN上，
再进行计算，消耗大量CN的资源，而且计算缓慢。
由于该自定义函数对确定的输入有确定的输出，如果将该自定义函数改为：
CREATE FUNCTION func_percent_1 (NUMERIC, NUMERIC) RETURNS NUMERIC AS 'SELECT $1 / $2 WHERE $2 > 0.01'
LANGUAGE SQL IMMUTABLE;
执行语句：
SELECT func_percent_1(ss_sales_price, ss_list_price) FROM store_sales;
其执行计划为：
可见函数func_percent_1被下推到DN执行，提升了执行效率（TPCDS 1000X，
3CN18DN，查询效率提升100倍以上）。
实例分析 2：使排序下推
请参考案例：使排序下推。