2.3 SQL 调优指南
2.3.6 典型 SQL 调优点
2.3.6.2 语句下推调优
目前，GaussDB(for openGauss)优化器在分布式框架下制定语句的执行策略时，有三 种执行计划方式：生成下推语句计划、生成分布式执行计划、生成发送语句的分布式 Node Scan on”则说明语句已下推给DN去执行。下面我们从三个维度场景介绍下语 句下推以及其支持的范围。
1 单表查询语句下推
在分布式数据库中对于单表查询而言，当前语句是否可以下推需要判断CN是否要进一 步参与计算而不是简单收集数据。如果CN要进一步对DN结果进行计算则语句不可下 推。通常带有agg, windows function, limit/offset, sort, distinct等关键字都不可下 推。
● 可下推：简单查询，无需在CN进一步计算则可以下推。
postgres=# explain select * from t where c1 > 1;
QUERY PLAN
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes
(2 rows)
● 不可下推：带有limit子句，对于CN而言不能简单发语句给DN并收集数据，明显 与limit语义不符。
postgres=# explain select * from t limit 1;
QUERY PLAN Limit (cost=0.00..0.00 rows=1 width=12)
-> Data Node Scan on "__REMOTE_LIMIT_QUERY__" (cost=0.00..0.00 rows=1 width=12) Node/s: All datanodes
(3 rows)
● 不可下推：带有聚集函数CN不能简单下推语句，而应该对从DN收集结果进一步 聚集运算处理。
postgres=# explain select sum(c1), count(*) from t;
QUERY PLAN Aggregate (cost=0.10..0.11 rows=1 width=20)
-> Data Node Scan on "__REMOTE_GROUP_QUERY__" (cost=0.00..0.00 rows=20 width=4)
postgres=# create table t(c1 int, c2 int, c3 int)distribute by hash(c1);
CREATE TABLE
postgres=# create table t1(c1 int, c2 int, c3 int)distribute by hash(c1);
CREATE TABLE
● 可下推：join条件满足两个表hash分布列属性。
postgres=# explain select * from t1 join t on t.c1 = t1.c1;
QUERY PLAN
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes
(2 rows)
● 不可下推：join条件不满足hash分布列属性，即t1.c2不是t1表的分布列。
postgres=# explain select * from t1 join t on t.c1 = t1.c2;
QUERY PLAN
Hash Join (cost=0.25..0.53 rows=20 width=24)
Hash Cond: (t1.c2 = t.c1)
-> Data Node Scan on t1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=20 width=12) Node/s: All datanodes
-> Hash (cost=0.00..0.00 rows=20 width=12)
-> Data Node Scan on t "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=20 width=12) Node/s: All datanodes
(7 rows)
3 特殊场景
对于有一些特殊场景通常无法下推，例如语句中带有with recursive子句，列存表等不 支持下推。
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倍以上）。
不支持下推的函数
首先介绍函数的易变性。在GaussDB(for openGauss)中共分三种形态：
●
IMMUTABLE
表示该函数在给出同样的参数值时总是返回同样的结果。
●
STABLE
表示该函数不能修改数据库，对相同参数值，在同一次表扫描里，该函数的返回 值不变，但是返回值可能在不同SQL语句之间变化。
●
VOLATILE
表示该函数值可以在一次表扫描内改变，因此不会做任何优化。
函数易变性可以查询pg_proc的provolatile字段获得，i代表IMMUTABLE，s代表 STABLE，v代表VOLATILE。另外，在pg_proc中的proshippable字段，取值范围为t/f/
NULL，这个字段与provolatile字段一起用于描述函数是否下推。
● 如果函数的provolatile属性为i，则无论proshippable的值是否为t，则函数始终可 以下推。
● 如果函数的provolatile属性为s或v，则仅当proshippable的值为t时，函数可以下 推。
● random，exec_hadoop_sql，exec_on_extension如果出现CTE中，也不下推。因 为这种场景下下推可能出现结果错误。
对于用户自定义函数，可以在创建函数的时候指定provolatile和proshippable属性的 值，详细请参考CREATE FUNCTION语法。
对于函数不能下推的场景：
● 如果是系统函数，建议根据业务等价替换这个函数。
● 如果是自定义函数，建议分析客户业务场景，看函数的provolatile和proshippable 属性定义是否正确。
不支持下推的语法
以如下三个表定义说明不支持下推的SQL语法。
postgres=# CREATE TABLE CUSTOMER1 ( C_CUSTKEY BIGINT NOT NULL ) DISTRIBUTE BY hash(C_CUSTKEY);
postgres=# CREATE TABLE test_stream(a int,b float); --float不支持重分布 postgres=# CREATE TABLE sal_emp ( c1 integer[] ) DISTRIBUTE BY replication;
● 不支持returning语句下推
postgres=# 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)
● 不支持聚集函数中使用order by语句的下推
postgres=# explain verbose select count ( c_custkey order by c_custkey) from customer1;
QUERY PLAN
--- Aggregate (cost=2.50..2.51 rows=1 width=8) Output: count(customer1.c_custkey ORDER BY customer1.c_custkey)
-> Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 rows=30 width=8) Output: customer1.c_custkey
Node/s: All datanodes
Remote query: SELECT c_custkey FROM ONLY public.customer1 WHERE true (6 rows)
● count(distinct expr)中的字段不支持重分布，则不支持下推
postgres=# 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用法下推
postgres=# 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)
postgres=# 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 包含外表的查询场景 LOG: SQL can't be shipped, reason:
RecursiveUnion contains HDFS Table or ForeignScan is not
shippable（LOG为CN日志中打印的 不下推原因，下同）
外表，当前版本暂不支持下推。
2 多nodegroup场景 LOG: SQL can't be shipped, reason:
With-Recursive under multi-nodegroup scenario is not shippable
基表存储nodegroup不相同，或者计 算nodegroup与基表不相同，当前版 本暂不支持下推。
3 UNION不带ALL，需要去重 LOG: SQL can't be shipped, reason:
With-Recursive does not contain
"ALL" to bind recursive & none-recursive branches
例如: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;
序号 场景 不下推原因
4 基表中有系统表 LOG: SQL can't be shipped, reason:
With-Recursive contains system table is not shippable
例如：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;
5 基表扫描只有VALUES子句，仅在
CN上即可完成执行。 LOG: SQL can't be shipped, reason:
With-Recursive contains only values rte is not shippable 例如：WITH RECURSIVE t(n) AS ( VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100 )SELECT sum(n) FROM t;
6 相关子查询的关联条件仅在递归部
分，非递归部分无关联条件。 LOG: SQL can't be shipped, reason:
With-Recursive recursive term correlated only is not shippable 例如：select a.ID,a.Name,
(with recursive cte as (
select ID, PID, NAME from b where b.ID = 1 union 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;
序号 场景 不下推原因 7 非递归部分带limit为Replicate计
划，递归部分为 Hash计划，计划 存在冲突。
LOG: SQL can't be shipped, reason:
With-Recursive contains conflict distribution in
none-recursive(Replicate) recursive(Hash) 例如：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;
8 多层Recursive嵌套，即recursive的 递归部分又嵌套另一个recursive查 询。
LOG: SQL can't be shipped, reason:
Recursive CTE references recursive CTE "cte"
例如：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;
GaussDB(for openGauss)根据子查询在SQL语句中的位置把子查询分成了子查询、子 链接两种形式。