【调优实践】自定义存储过程不下推

1. 问题描述

XX局点某业务定义了一个FUNCTION，用于去除字符串中多余逗号，FUNCTION定义如下：

CREATE OR REPLACE FUNCTION cbgisc.f_ioc_logst_dist_str(p_source_str character varying, delimiter character varying)
 RETURNS character varying
 LANGUAGE plpgsql
 NOT FENCED
AS $function$ 
 
DECLARE

 T VARCHAR;--临时变量
 S VARCHAR;--源字符串
 P INT;--记录分隔符位置
 
 RET_STR   VARCHAR;
 
BEGIN
    SELECT STRING_AGG(REGEXP_SPLIT_TO_TABLE,DELIMITER)::VARCHAR
      INTO RET_STR
      FROM (
    SELECT  REGEXP_SPLIT_TO_TABLE 
    FROM REGEXP_SPLIT_TO_TABLE(P_SOURCE_STR,',') 
    GROUP BY REGEXP_SPLIT_TO_TABLE
    )
    ;
    
    RETURN RET_STR;

END;
$function$

2. 优化改写

FUNCTION定义没有指定易变属性和下推属性，默认情况下是不下推的；只有显示指定下推属性SHIPPABLE才支持下推。在SQL中引用该FUNCTION，则查询不支持下推，可能引发CN瓶颈。同时自定义FUNCTION使用SQL编写，性能一般都很差。通过FUNCTION定义分析，我们可以看出，是将字符串先按照逗号分隔进行爆炸，爆炸后再使用分隔符合并成一行，目的其实是去除多余逗号，因此我们可以尝试使用正则表达式实现该功能，正则表达式如下：

regexp_replace('123,2335,,121,,,22,,,,,5','[,]+',',','g')

改写后SQL性能提升效果明显。

参考该方法可以实现各种多余字符的去重，比如空格等。