DWS函数小结【华为云DWS Studio SQL编辑器体验】

1、函数属性

创建函数的时候，可以指定函数属性，与函数下推相关的属性为易失性级别 和下推属性

易失属性：

• IMMUTABLE：该属性的函数不会修改数据库，并且保证在任何情况下同样的输入参数永远返回同样的结果；IMMUTABLE属性是一定能下推到DN执行的，不管下推属性是否为SHIPPABLE

• STABLE：该属性的函数不会修改数据库，并且保证在同一个查询中，对于同样的输入参数，函数返回的结果相同；是否下推DN由SHIPPABLE属性决定

• VOLATILE：该属性的函数对于同样的输入参数，函数的返回结果可能不同，典型的如timeofday；是否下推DN由SHIPPABLE属性决定

注意：创建函数时如果未明确指定，则默认为VOLATILE

查看 pg_proc 视图的 provolatile 字段（i表示immutable，s表示stable，v表示volatile）

下推属性：

• SHIPPABLE：函数可以下推到DN执行

• NOT SHIPPABLE：函数不能下推到DN执行

注意：创建函数时如果未明确指定，则默认为NOT SHIPPABLE

重要：用户在使用GaussDB(DWS)时，应该正确指定函数属性，错误指定函数属性不仅会导致查询语句执行效率低，而且可能会导致结果集不稳定的情况

对于函数不能下推的场景：

• 如果是系统函数，建议根据业务等价替换这个函数。

• 如果是自定义函数，建议分析客户业务场景，看函数的provolatile和proshippable属性定义是否正确。

2、函数下推逻辑

函数下推逻辑分为两部分：

1. 函数体是否下推：通过函数属性（shippable）判断

2. 函数体内的语句是否下推：当函数体不下推的时候，函数体内的SQL语句进行独立的下推判断（优化器自动判断）

2.1 调用函数的语句不包含用户表

当调用函数的语句不包含用户表的时候，这个语句总是在CN上执行，函数体不下推，此时执行逻辑如下：

1. 先执行函数：语句中没有用户表，这个语句总是在CN上执行

2. 然后执行函数体的语句，如果不存在不下推的因素，那么总是可以下推的（走下推框架：FQS 或 stream计划）

注意：

• 单独调用函数（调用函数的语句不包含用户表），函数中包含表关联查询的SQL，查询结果是准确的

• 函数下推只有在函数调用语句中直接出现用户表的时候才有意义

• 函数体里面的执行计划没办法直接对外展示，可以设置 enable_track_record_subsql=on 记录函数体内部的语句

案例1：调用函数的语句不包含用户表

初始化环境（后面的案例默认也一样）

CREATE TABLE student (               
        id integer,                   
        name character varying(50),   
        age integer                   
)                                     
WITH (orientation=row, compression=no)
DISTRIBUTE BY HASH(id);
​
CREATE TABLE stuscore(               
        id integer,                   
        score integer
)                                     
WITH (orientation=row, compression=no)
DISTRIBUTE BY HASH(id);
​
insert into student(id,name,age) values(1,'bob',17),(3,'peter',18),(4,'david',15),(8,'alice','17');
insert into stuscore(id,score) values(1,88),(3,68),(8,100),(4,90);
​
CREATE OR REPLACE FUNCTION mytest.get_student_id(name character varying)
    RETURNS TABLE(id integer)
    LANGUAGE plpgsql
    VOLATILE NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
BEGIN
    RETURN QUERY (SELECT id FROM mytest.student WHERE name=$1);
END $function$;

查看单独调用函数的计划

mt=# explain verbose select * from get_student_id('alice');
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
  id |                 operation                  | E-rows | E-distinct | E-width | E-costs 
 ----+--------------------------------------------+--------+------------+---------+---------
   1 | ->  Function Scan on mytest.get_student_id |   1000 |            |       4 | 10.25   
 
           Predicate Information (identified by plan id)          
 -----------------------------------------------------------------
   1 --Function Scan on mytest.get_student_id
         Function Call: get_student_id('alice'::character varying)
 
 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Function Scan on mytest.get_student_id
         Output: id
 
              ====== Query Summary =====              
 -----------------------------------------------------
 Parser runtime: 0.024 ms
 Planner runtime: 0.100 ms
 Unique SQL Id: 2670906503
 Unique SQL Hash: sql_86483405ae1397043b362361587d0c30

计划中没有函数是否下推的说明，因为函数下推只有在函数调用语句中直接出现用户表的时候才有意义

2.2 调用函数的语句包含用户表

当调用函数的语句包含用户表的时候，语句是否下推由函数属性（shippable）决定

案例2：函数属性not shippable，调用函数的语句包含用户表

查看调用函数的计划，函数不可下推

mt=# explain verbose select get_student_id('alice') from stuscore;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
  id |                       operation                       | E-rows | E-distinct | E-width | E-costs 
 ----+-------------------------------------------------------+--------+------------+---------+---------
   1 | ->  Data Node Scan on stuscore "_REMOTE_TABLE_QUERY_" |  60000 |            |       0 | 314.70  
 
              SQL Diagnostic Information              
 -----------------------------------------------------
 Statistic Not Collect:
         mytest.stuscore(id)
 SQL is not plan-shipping
         reason: Function get_student_id() can not be shipped
 
           Targetlist Information (identified by plan id)           
 -------------------------------------------------------------------
   1 --Data Node Scan on stuscore "_REMOTE_TABLE_QUERY_"
         Output: get_student_id('alice'::character varying)
         Node/s: All datanodes
         Remote query: SELECT * FROM ONLY mytest.stuscore WHERE true
 
              ====== Query Summary =====              
 -----------------------------------------------------
 Parser runtime: 0.030 ms
 Planner runtime: 0.404 ms
 Unique SQL Id: 2115266142
 Unique SQL Hash: sql_cad5e14f4d372458179ead4e9173b13e

案例3：函数属性shippable，调用函数的语句包含用户表

修改函数定义

CREATE OR REPLACE FUNCTION mytest.get_student_id(name character varying)
    RETURNS TABLE(id integer)
    LANGUAGE plpgsql
    VOLATILE NOT FENCED SHIPPABLE
AS $function$
DECLARE
BEGIN
    RETURN QUERY (SELECT id FROM mytest.student WHERE name=$1);
END $function$;

查看调用函数的计划，函数可下推

mt=# explain verbose select get_student_id('alice') from stuscore;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
  id |                  operation                   | E-rows | E-distinct | E-width | E-costs 
 ----+----------------------------------------------+--------+------------+---------+---------
   1 | ->  Data Node Scan on "__REMOTE_FQS_QUERY__" |      0 |            |       0 | 0.00    
 
                                    Targetlist Information (identified by plan id)                                    
 ---------------------------------------------------------------------------------------------------------------------
   1 --Data Node Scan on "__REMOTE_FQS_QUERY__"
         Output: (get_student_id('alice'::character varying))
         Node/s: All datanodes
         Remote query: SELECT mytest.get_student_id('alice'::character varying) AS get_student_id FROM mytest.stuscore
 
              ====== Query Summary =====              
 -----------------------------------------------------
 Parser runtime: 0.042 ms
 Planner runtime: 0.137 ms
 Unique SQL Id: 2115266142
 Unique SQL Hash: sql_cad5e14f4d372458179ead4e9173b13e