文档首页 > > 开发指南> 优化查询性能> 改进查询> 典型SQL调优点> 语句下推调优

语句下推调优

分享
更新时间: 2019/06/24 GMT+08:00

语句下推介绍

目前,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_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)
  • 不支持递归范围表(WITH RECURSIVE)查询的下推
    explain WITH RECURSIVE customer1_rescursive (c_name,c_nationkey) AS (SELECT 1,c_nationkey FROM customer1 WHERE c_custkey = 1) SELECT * FROM customer1_rescursive;
                                             QUERY PLAN                                          
    ------------------------------------------------------------------
     CTE Scan on customer1_rescursive  (cost=0.00..20.00 rows=1000 width=8)
       CTE customer1_rescursive
         ->  Data Node Scan on customer1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=1 width=4)
               Node/s: dn_6003_6004
    (4 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)

不支持下推的函数

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

  • 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如果出现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:使排序下推

请参考案例:使排序下推

分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区