GaussDB业务SQL包含自定义function，通过hint方式添加query_dop,smp不生效

• 问题现象

业务sql执行时间较长，需要通过smp增加并行提升执行效率，通过hint 方式， 发现query_dop 不生效。以下是sql及对应执行计划、相关表表结构。

执行计划

select表结构

insert表结构

• 技术背景

针对上述SQL案例，smp使用有如下约束条件，如下：

1. 当function为易变函数，smp不生效， 需要将function调整为i函数。

2. 当function不能下推到dn执行，smp不生效，需要将function修改为SHIPPABLE。

3. 当业务表为分区表且创建了global索引，smp 不生效， 需要将业务表修改为local索引。

function创建规则如下：

不主动指定 IMMUTABLE | STABLE ， 默认创建 v 函数 | s 函数，即默认指定 VOLATILE | STABLE。

不主动指定 SHIPPABLE，默认指定 NOT SHIPPABLE, 即该函数不下推到dn上执行。

pg_proc 字段说明

验证如下：

不主动指定 IMMUTABLE | STABLE ， 默认创建 v 函数，即默认指定 VOLATILE。

不主动指定 SHIPPABLE，默认指定 NOT SHIPPABLE, 即该函数不下推到dn上执行。

gaussdb=> CREATE FUNCTION func1 RETURN integer
gaussdb->     AS  
gaussdb$>     BEGIN
gaussdb$>         RETURN 2;
gaussdb$>     END;
gaussdb$>     /
CREATE FUNCTION
 
--创建function ， 默认是v 函数
gaussdb=> select proname, provolatile, proshippable  from pg_proc where proname = 'func1';
 proname | provolatile | proshippable 
---------+-------------+--------------
 func1   | v           | f

• 验证案例

1. select 查询结果中使用到了v函数（易变 volatile），smp将不生效，需调整为i (不可变的 immutable) 函数或者s（稳定的 stable）函数。

gaussdb=> CREATE FUNCTION func1 RETURN integer
gaussdb->     AS  
gaussdb$>     BEGIN
gaussdb$>         RETURN 2;
gaussdb$>     END;
gaussdb$>     /
CREATE FUNCTION
 
--创建function ， 默认是v 函数
gaussdb=> select proname, provolatile, proshippable  from pg_proc where proname = 'func1';
 proname | provolatile | proshippable 
---------+-------------+--------------
 func1   | v           | f

gaussdb=> create table stu2 (
gaussdb(> id int ,
gaussdb(> sid int,
gaussdb(> sname int
gaussdb(> );
CREATE TABLE
 
--默认执行计划
gaussdb=> explain select *, func1 AS pk_id  from stu2;
 id |      operation       | E-rows | E-width |    E-costs      
----+----------------------+--------+---------+----------------
  1 | ->  Seq Scan on stu2 |   1945 |      12 | 0.000..515.700
(1 row)
--smp 不生效
gaussdb=> explain insert /*+ set(query_dop 4) set(enable_force_smp on)*/ into stu1 select * from stu2;
 id |        operation        | E-rows | E-width |    E-costs     
----+-------------------------+--------+---------+----------------
  1 | ->  Insert on stu1      |   1945 |      12 | 0.000..515.700
  2 |    ->  Seq Scan on stu2 |   1945 |      12 | 0.000..515.700
(2 rows)
 
--将 func1 修改为 i 函数
gaussdb=> ALTER FUNCTION func1() IMMUTABLE ;
ALTER FUNCTION
 
gaussdb=> select proname, provolatile, proshippable  from pg_proc where proname = 'func1';
    proname | provolatile | proshippable 
   ---------+-------------+--------------
    func1   | i           | f
   (1 row)
--smp 生效
gaussdb=> explain select /*+ set(query_dop 4) set(enable_force_smp on)*/ *, func1 AS pk_id  from stu2;
 id |                 operation                  | E-rows | E-width |    E-costs    
----+--------------------------------------------+--------+---------+---------------
  1 | ->  Streaming(type: LOCAL GATHER dop: 1/4) |   1945 |      12 | 0.000..11.992
  2 |    ->  Seq Scan on stu2                    |   1945 |      12 | 0.000..7.362
(2 rows)

2. insert 使用了自定义函数，当函数为STABLE/VOLATILE类型的函数，且函数的属性是NOT SHIPPABLE的时候（不能下推到dn执行），smp将不生效，需要调整函数属性为SHIPPABLE

gaussdb=> CREATE FUNCTION func2 RETURN integer
gaussdb->     AS  
gaussdb$>     BEGIN
gaussdb$>         RETURN 2;
gaussdb$>     END;
gaussdb$>     /
CREATE FUNCTION
 
--创建function ， 默认不下推dn执行gaussdb=> select proname, provolatile, proshippable  from pg_proc where proname = 'func2';
 proname | provolatile | proshippable 
---------+-------------+--------------
 func2   | v           | f

gaussdb=> create table stu1 (
gaussdb(> id int ,
gaussdb(> sid int,
gaussdb(> sname int,
gaussdb(> pk_id int  DEFAULT func2()
gaussdb(> );
CREATE TABLE
gaussdb=> create table stu2 (
gaussdb(> id int ,
gaussdb(> sid int,
gaussdb(> sname int
gaussdb(> );
CREATE TABLE
 
--默认执行计划
gaussdb=> explain insert  into stu1 select * from stu2;
 id |        operation        | E-rows | E-width |    E-costs    
----+-------------------------+--------+---------+---------------
  1 | ->  Insert on stu1      |   1945 |      12 | 0.000..29.450
  2 |    ->  Seq Scan on stu2 |   1945 |      12 | 0.000..29.450
(2 rows)
 
--smp 不生效
gaussdb=> explain insert /*+ set(query_dop 4) set(enable_force_smp on)*/ into stu1 select * from stu2;
    id |        operation        | E-rows | E-width |    E-costs     
   ----+-------------------------+--------+---------+----------------
     1 | ->  Insert on stu1      |   1945 |      12 | 0.000..515.700
     2 |    ->  Seq Scan on stu2 |   1945 |      12 | 0.000..515.700
 
-- 调整 func2 函数属性为SHIPPABLE
gaussdb=> ALTER FUNCTION func2() SHIPPABLE;
ALTER FUNCTION
 
gaussdb=> select proname, provolatile, proshippable  from pg_proc where proname = 'func2';
 proname | provolatile | proshippable 
---------+-------------+--------------
 func2   | v           | t
-- smp 生效
gaussdb=> explain insert /*+ set(query_dop 4) set(enable_force_smp on)*/ into stu1 select * from stu2;
 id |                 operation                  | E-rows | E-width |    E-costs     
----+--------------------------------------------+--------+---------+----------------
  1 | ->  Streaming(type: LOCAL GATHER dop: 1/4) |      0 |       0 | 0.000..493.613
  2 |    ->  Insert on stu1                      |   1945 |      12 | 0.000..493.613
  3 |       ->  Seq Scan on stu2                 |   1945 |      12 | 0.000..493.613
(3 rows)

3. 分区表创建global 索引不支持smp。需要将分区表调整成普通表或者将global索引修改为local索引。

gaussdb=> create table stu1(
gaussdb(> id int ,
gaussdb(> sid int,
gaussdb(> sname int);
CREATE TABLE
 
gaussdb=> create table stu2 (
gaussdb(> id int ,
gaussdb(> sid int,
gaussdb(> sname int
gaussdb(> ) PARTITION BY RANGE (id) (
gaussdb(>     PARTITION p1 VALUES LESS THAN (200),
gaussdb(>     PARTITION pmax VALUES LESS THAN (MAXVALUE)
gaussdb(> );
CREATE TABLE
 
--创建global 索引
gaussdb=> CREATE INDEX idx_st2 ON stu2(id) GLOBAL;
CREATE INDEX
--默认执行计划
gaussdb=> explain insert  into stu1 select * from stu2;
 id |               operation                | E-rows | E-width |    E-costs    
----+----------------------------------------+--------+---------+---------------
  1 | ->  Insert on stu1                     |   1945 |      12 | 0.000..29.450
  2 |    ->  Partition Iterator              |   1945 |      12 | 0.000..29.450
  3 |       ->  Partitioned Seq Scan on stu2 |   1945 |      12 | 0.000..29.450
(3 rows)
 
 Predicate Information (identified by plan id) 
-----------------------------------------------
   2 --Partition Iterator
         Iterations: 2
   3 --Partitioned Seq Scan on stu2
         Selected Partitions:  1..2
(4 rows)
 
--smp 不生效
gaussdb=> explain insert /*+ set(query_dop 4) set(enable_force_smp on)*/ into stu1 select * from stu2;
 id |               operation                | E-rows | E-width |    E-costs    
----+----------------------------------------+--------+---------+---------------
  1 | ->  Insert on stu1                     |   1945 |      12 | 0.000..29.450
  2 |    ->  Partition Iterator              |   1945 |      12 | 0.000..29.450
  3 |       ->  Partitioned Seq Scan on stu2 |   1945 |      12 | 0.000..29.450
(3 rows)
 
 Predicate Information (identified by plan id) 
-----------------------------------------------
   2 --Partition Iterator
         Iterations: 2
   3 --Partitioned Seq Scan on stu2
         Selected Partitions:  1..2
(4 rows)
 
--修改索引为本地索引
gaussdb=> DROP INDEX idx_st2;
DROP INDEX
gaussdb=> 
gaussdb=> CREATE INDEX idx_st2 ON stu2(id) LOCAL;
CREATE INDEX
 
--smp 生效
gaussdb=> explain insert /*+ set(query_dop 4) set(enable_force_smp on)*/ into stu1 select * from stu2;
 id |                 operation                  | E-rows | E-width |   E-costs    
----+--------------------------------------------+--------+---------+--------------
  1 | ->  Streaming(type: LOCAL GATHER dop: 1/4) |      0 |       0 | 0.000..7.362
  2 |    ->  Insert on stu1                      |   1945 |      12 | 0.000..7.362
  3 |       ->  Partition Iterator               |   1945 |      12 | 0.000..7.362
  4 |          ->  Partitioned Seq Scan on stu2  |   1945 |      12 | 0.000..7.362
(4 rows)
 
 Predicate Information (identified by plan id) 
-----------------------------------------------
   3 --Partition Iterator
         Iterations: 2
   4 --Partitioned Seq Scan on stu2
         Selected Partitions:  1..2
(4 rows)

结论：

1. select 语句中使用的ks_auth.F_IS_IDNO为易变函数，smp不生效，需要调整为i函数。

2. insert 对应的表TMP_WATCHLIST_CLIENT_PRE使用的函数dsc_ora_ext.dsc_fn_sys_guid() 不能下推到dn执行，smp不生效，需要修改为SHIPPABLE。

3. T_NOR_CLIENT为分区表且创建了global索引，smp 不生效，需要修改为local索引。