ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > > 典型SQL调优点-语句下推调优

典型SQL调优点-语句下推调优

作者：GaussDB_数据库 时间：2020-01-15 09:53:42 0 删除 编辑

语句下推介绍

目前，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)
  • 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当前版本不支持下推的场景和原因如下：

    序号

    场景

    不下推原因

    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 = 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;

    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
    ) h
    inner 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的递归部分又嵌套另一个recursive查询。

不支持下推的函数

首先介绍函数的易变性。在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;

其执行计划为：

可见，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：使排序下推

请参考案例：使排序下推。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69959250/viewspace-3118130/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 软件卸载-卸载集群
下一篇： 开发者指南-最佳实践
请登录后发表评论 登录
全部评论
GaussDB_数据库

注册时间：2020-01-06

  • 博文量
    2907
  • 访问量
    19232

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593