问题描述

我在GP中有一张分区表，使用普通SQL，执行计划走分区，而把它封装到FUNCTION里面，效率就差很多。普通SQL不到1秒钟，使用函数要20几秒，请问，这是为什么？
表格记录3000万左右，分区按月分区，6个segment,6个镜像。
Greenplum 4.3.16.1

单个SQL执行计划

explain select count(*) s_cnt from history where acctstarttime between to_date('2017-05-11 22:13:59','yyyymmdd') and to_date('2017-05-11 23:13:59','yyyymmdd');

                                              QUERY PLAN                                                   

Aggregate (cost=1.10..1.11 rows=1 width=8)
-> Gather Motion 6:1 (slice1; segments: 6) (cost=1.00..1.08 rows=1 width=8)

     ->  Aggregate  (cost=1.00..1.01 rows=1 width=8)
           ->  Append  (cost=0.00..1.00 rows=1 width=0)
                 ->  Seq Scan on history_1_prt_13 history  (cost=0.00..1.00 rows=1 width=0)
                       Filter: acctstarttime >= '2017-01-05'::date AND acctstarttime <= '2017-01-05'::date

Optimizer status: legacy query optimizer

单个SQL执行结果

select count(*) s_cnt from history where acctstarttime between to_date('2017-05-11 22:13:59','yyyymmdd') and to_date('2017-05-11 23:13:59','yyyymmdd');

s_cnt

 0

(1 row)

Time: 105.734 ms

创建函数

CREATE FUNCTION get_total_authcnt(v_starttime text,v_endtime text) RETURNS integer as

$$ DECLARE s_cnt integer; s_starttime TEXT; s_endtime text; BEGIN s_starttime:=v_starttime; s_endtime:=v_endtime; if length(s_starttime)=0 or s_endtime is null then select count(*) into s_cnt from history where acctstarttime between current_date + INTERVAL '-7 day' and current_date; else select count(*) into s_cnt from history where acctstarttime between to_date(s_starttime,'yyyy-mm-dd') and to_date(s_endtime,'yyyy-mm-dd'); end if; RETURN s_cnt; END; $$

language plpgsql ;

补充，函数也曾使用过immutable声明，但是结果还是一样。

函数调用结果

select get_total_authcnt('2017-05-11 22:13:59','2017-05-11 23:13:59');

get_total_authcnt

             0

(1 row)

Time: 16480.378 ms

最后经过自己努力，解决了！改成动态SQL！