高斯数据库 (gaussDB) - SQL 调优指南 (6)

语句下推调优

语句下推介绍

目前,GaussDB 200 优化器在分布式框架下制定语句的执行策略时,有三种执行计划方
式:生成下推语句计划、生成分布式执行计划、生成发送语句的分布式执行计划。

  • 下推语句计划:指直接将查询语句从 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_NAME VARCHAR(25) NOT NULL
, C_ADDRESS VARCHAR(40) NOT NULL
, C_NATIONKEY INT NOT NULL
, C_PHONE CHAR(15) NOT NULL
, C_ACCTBAL DECIMAL(15,2) 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)
  • 不支持聚集函数中使用 order by 语句的下推
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) 中的字段不支持重分布,则不支持下推
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)
Output: customer1.c_custkey
Node/s: All datanodes
Remote query: SELECT c_custkey FROM ONLY public.customer1 WHERE true
(9 rows)

l Fulljoin 的 join 列如果不支持重分布,则不支持下推

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 当前版本不支持下推的场景和原因如下:
    image.png
    image.png

不支持下推的函数

首先介绍函数的易变性。在 GaussDB 200 中共分三种形态:

  • 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 属
    性定义是否正确。

实例分析:自定义函数

对于自定义函数,如果对于确定的输入,有确定的输出,则应将函数定义为 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;

其执行计划为:
image.png

可见,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;

其执行计划为:
image.png
可见函数 func_percent_1 被下推到 DN 执行,提升了执行效率(TPCDS 1000X,
3CN18DN,查询效率提升 100 倍以上)。

  
    展开阅读全文