你的数仓函数结果不稳定,可能是属性指定错了

摘要:本文简单介绍GaussDB(DWS)函数下推属性的相关知识,并提供几个函数属性相关的典型案例供大家参考。

本文分享自华为云社区《GaussDB(DWS)函数下推属性介绍》,原文作者:Arrow0lf。

用户在使用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. 总结

创建自定义函数时,要正确指定函数的属性,确保函数属性符合预期,防止因函数属性设置不正确导致的性能下降或结果集不稳定。

点击关注,第一时间了解华为云新鲜技术~


开发者之家
生于云,长于云,让开发者成为决定性力量

生于云,长于云,让开发者成为决定性力量

1.4k 声望
1.7k 粉丝
0 条评论
推荐阅读
【贺】来自开发者的点赞,华为云开发者联盟入选 2022 中国技术品牌影响力企业榜
2023 年 1 月 4 日,中国技术先锋年度评选 | 2022 中国技术品牌影响力企业榜单正式发布。作为中国领先的新一代开发者社区,SegmentFault 思否依托数百万开发者用户数据分析,各科技企业在国内技术领域的行为及影...

华为云开发者联盟阅读 627

终于卷完了!Redis 打怪升级进阶成神之路(2023 最新版)!
是一种非关系型数据库服务,它能解决常规数据库的并发能力,比如传统的数据库的IO与性能的瓶颈,同样它是关系型数据库的一个补充,有着比较好的高效率与高性能。专注于key-value查询的redis、memcached、ttserver。

民工哥11阅读 989

封面图
硬卷完了!MongoDB 打怪升级进阶成神之路( 2023 最新版 )!
前面我们学习:MySQL 打怪升级进阶成神之路、Redis 打怪升级进阶成神之路,然后我们还在继续 NoSQL 的卷王之路。从第一篇文章开始,我们逐步详细介绍了 MogoDB 基础概念、安装和最基本的CURD操作、索引和聚合、工...

民工哥6阅读 553

封面图
初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go4阅读 1.7k评论 2

封面图
又一款内存数据库横空出世,比 Redis 更强,性能直接飙升一倍!杀疯了
KeyDB是Redis的高性能分支,专注于多线程,内存效率和高吞吐量。除了多线程之外,KeyDB还具有仅在Redis Enterprise中可用的功能,例如Active Replication,FLASH存储支持以及一些根本不可用的功能,例如直接备份...

民工哥4阅读 1.7k评论 2

封面图
面试官:请说一下如何优化结构体的性能?
使用内存对齐机制优化结构体性能,妙啊!前言之前分享过2篇结构体文章:10秒改struct性能直接提升15%,产品姐姐都夸我好棒 和 Go语言空结构体这3种妙用,你知道吗? 得到了大家的好评。这篇继续分享进阶内容:结...

王中阳Go4阅读 3.8k评论 2

封面图
MySQL百万数据深度分页优化思路分析
一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看,最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万...

一个程序员的成长7阅读 919

封面图

生于云,长于云,让开发者成为决定性力量

1.4k 声望
1.7k 粉丝
宣传栏