你的数仓函数结果不稳定，可能是属性指定错了
摘要：本文简单介绍GaussDB(DWS)函数下推属性的相关知识，并提供几个函数属性相关的典型案例供大家参考。
用户在使用GaussDB(DWS)时，应该正确指定函数属性，错误指定函数属性不仅会导致查询语句执行效率低，而且可能会导致结果集不稳定的情况。本文简单介绍GaussDB(DWS)函数下推属性的相关知识，并提供几个函数属性相关的典型案例供大家参考。
1. 函数下推属性介绍
GaussDB(DWS)创建函数时，可以指定许多函数属性，其中，与函数下推相关的属性为易失性级别 和下推属性，其中：
易失性：
- IMMUTABLE：该属性的函数不会修改数据库，并且保证在任何情况下同样的输入参数永远返回同样的结果；
- STABLE：该属性的函数不会修改数据库，并且保证在同一个查询中，对于同样的输入参数，函数返回的结果相同；
- VOLATILE：该属性的函数对于同样的输入参数，函数的返回结果可能不通，典型的如timeofday，创建函数时如果未明确指定，则默认为VOLATILE；
下推属性：
- SHIPPABLE：函数可以下推到DN执行
- NOT SHIPPABLE：函数不能下推到DN执行，创建函数时如果未明确指定，则默认为NOT SHIPPABLE。
在GaussDB(DWS)中，IMMUTABLE属性的函数时一定能够下推到DN执行的，不管下推属性是否为SHIPPABLE，对于STABLE和VOLATILE属性的函数，函数是否能下推要看指定的SHIPPABLE属性。因此，在创建函数时如果同时指定了IMMUTABLE 和 NOT SHIPPABLE的属性，函数创建成功时会有以下提示：
NOTICE: Immutable function will be shippable anyway.
2. 函数下推属性典型案例
案例一：未指定函数易失性级别导致函数不下推
函数定义如下：
create function try_cast_int(p_in text, p_default int default 0) returns int as $$ begin begin return $1::int; exception when others then return p_default; end; end; $$ language plpgsql;
由于创建函数时未明确指定函数易失性级别和函数属性，函数默认为VOLATILE NOT SHIPPABLE，使用该函数时执行计划如下：
postgres=# explain verbose select try_cast_int(b) from test order by a; QUERY PLAN -------------------------------------------------------------------------------------- Sort (cost=13.91..14.04 rows=50 width=36) Output: (try_cast_int(test.b, 0)), test.a Sort Key: test.a -> Data Node Scan on "__REMOTE_SORT_QUERY__" (cost=0.00..12.50 rows=50 width=36) Output: try_cast_int(test.b, 0), test.a Node/s: All datanodes Remote query: SELECT a, b FROM ONLY public.test WHERE true ORDER BY 1 (7 rows)
可以看出该sql执行计划不下推，执行效率较低，分析该函数发现该函数可以指定为IMMUTABLE属性，让该函数可以下推，因此，可以通过以下方式优化：
ALTER FUNCTION try_cast_int(text,int) IMMUTABLE;
案例二：错误指定了函数下推属性导致结果集不稳定
下推函数能够下推到DN执行，与不下推函数相比有着更高的执行效率，有时开发者为了加快函数执行效率，所有自定义函数创建时都会指定为SHIPPABLE，某函数定义如下：
create function get_count() returns int SHIPPABLE as $$ declare result int; begin result = (select count(*) from test); --test表是hash表 return result; end; $$ language plpgsql;
调用该函数发现以下现象：
postgres=# select get_count(); get_count ----------- 2106 (1 row) postgres=# select get_count() from t_src; get_count ----------- 1032 (1 row)
发现加上from表之后函数的返回值结果发生了变化！为什么会出现这种情况呢？这是因为由于这个函数指定了SHIPPABLE的函数属性，因此生成计划时该函数会下推到DN上执行，该函数下推到DN后，由于函数定义中的test表是hash表，因此每个DN上只有该表的一部分数据，所以select count(*) from test返回的结果不是test表全量数据的结果，而是每个DN上部分数据的结果，因此导致加上from表后函数返回预期发生变化，优化方法：
（1）将函数改为不下推：alter function get_count() not shippable;
（2）将函数中用到的表改为复制表，这样每个DN上都是一份该表的全量数据，即使下推到DN执行，也能保证结果集符合预期。
3. 总结
创建自定义函数时，要正确指定函数的属性，确保函数属性符合预期，防止因函数属性设置不正确导致的性能下降或结果集不稳定。
本文分享自华为云社区《GaussDB(DWS)函数下推属性介绍》，原文作者：Arrow0lf。
