为什么用函数比直接的查询慢10倍？

[siegebaoniu]

CREATE OR REPLACE FUNCTION dmt_production.get_retentionperformancereportbyschoolcourse
(
  IN  v_school          varchar,
  IN  v_startyear       integer,
  IN  v_startmonth      integer,
  IN  v_endyear         integer,
  IN  v_endmonth        integer,
  IN  v_offset_num      varchar,
  IN  v_coursetype      varchar,
  IN  v_coursecategory  varchar,
  IN  v_coursesize      varchar
)
RETURNS TABLE 
(
  country             varchar,
  businessline        varchar,
  school              varchar,
  schoolkey           varchar,
  coursetype          varchar,
  coursetypekey       varchar,
  offset_num          integer,
  retentionstartdate  varchar,
  retentionenddate    varchar,
  paid                bigint,
  std                 bigint,
  exstd               bigint,
  retentionrate       numeric,
  lpaid               bigint,
  lstd                bigint,
  lretentionrate      numeric,
  ppaid               bigint,
  pstd                bigint,
  pretentionrate      numeric
)
AS
$$
DECLARE
v_StartDate timestamp;
v_EndDate timestamp;
BEGIN

SELECT cast(v_startyear::varchar||'-'||v_startmonth::varchar||'-1 0:00:00.000' AS timestamp) INTO v_StartDate;
SELECT cast(v_endyear::varchar||'-'||v_endmonth::varchar||'-1 23:59:59.000' AS timestamp)+interval '1 month'-interval '1 day' INTO v_EndDate;

    RETURN QUERY 
    SELECT C.* 
,L.Paid AS LPaid
,L.STD AS LSTD
,L.RetentionRate AS LRetentionRate
,P.Paid AS PPaid
,P.STD AS PSTD
,P.RetentionRate AS PRetentionRate
FROM
(
SELECT T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
    ,cast(to_char(timeadd('MONTH',T.Offset_num,v_StartDate), 'TMyyyy month') as varchar) AS RetentionStartDate
    ,cast(to_char(timeadd('MONTH',T.Offset_num,v_EndDate), 'TMyyyy month') as varchar) AS RetentionEndDate
    ,COUNT(DISTINCT T.Customer_Key) AS Paid
    ,COUNT(DISTINCT T.STD) AS STD
    ,COUNT(DISTINCT T.ExSTD) AS ExSTD
    ,COUNT(DISTINCT T.STD)*1.0/COUNT(DISTINCT T.Customer_Key) AS RetentionRate
    FROM dmt_production.Retention_Table T
    WHERE (T.Date_Offset BETWEEN v_StartDate AND v_EndDate)
    AND T.School_Key IN (SELECT * from regexp_split_to_table(v_School,',')) AND T.Offset_num::varchar IN (SELECT * from regexp_split_to_table(v_Offset_num,',')) 
    AND T.Course_Type_Key IN (SELECT * from regexp_split_to_table(v_CourseType,',')) AND T.Course_Category_Key IN (SELECT * from regexp_split_to_table(v_CourseCategory,',')) AND T.Course_Size_Key IN (SELECT * from regexp_split_to_table(v_CourseSize,','))
    GROUP BY T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
)C
LEFT JOIN
(
SELECT T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
    ,COUNT(DISTINCT T.Customer_Key) AS Paid
    ,COUNT(DISTINCT T.STD) AS STD
    ,COUNT(DISTINCT T.STD)*1.0/COUNT(DISTINCT T.Customer_Key) AS RetentionRate
    FROM dmt_production.Retention_Table T
    WHERE (T.Date_Offset BETWEEN TIMEADD('YEAR',-1,v_StartDate) AND TIMEADD('YEAR',-1,v_EndDate))
    AND T.School_Key IN (SELECT * from regexp_split_to_table(v_School,',')) AND T.Offset_num::varchar IN (SELECT * from regexp_split_to_table(v_Offset_num,',')) 
    AND T.Course_Type_Key IN (SELECT * from regexp_split_to_table(v_CourseType,',')) AND T.Course_Category_Key IN (SELECT * from regexp_split_to_table(v_CourseCategory,',')) AND T.Course_Size_Key IN (SELECT * from regexp_split_to_table(v_CourseSize,','))
    GROUP BY T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
) L ON C.Country = L.Country
AND C.Business_Line = L.Business_Line
AND C.School = L.School
AND C.School_Key = L.School_Key
AND C.Course_Type = L.Course_Type
AND C.Course_Type_Key = L.Course_Type_Key
AND C.Offset_num = L.Offset_num
LEFT JOIN
(
SELECT T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
    ,COUNT(DISTINCT T.Customer_Key) AS Paid
    ,COUNT(DISTINCT T.STD) AS STD
    ,COUNT(DISTINCT T.STD)*1.0/COUNT(DISTINCT T.Customer_Key) AS RetentionRate
    FROM dmt_production.Retention_Table T
    WHERE (T.Date_Offset BETWEEN TIMEADD('YEAR',-2,v_StartDate) AND TIMEADD('YEAR',-2,v_EndDate))
    AND T.School_Key IN (SELECT * from regexp_split_to_table(v_School,',')) AND T.Offset_num::varchar IN (SELECT * from regexp_split_to_table(v_Offset_num,',')) 
    AND T.Course_Type_Key IN (SELECT * from regexp_split_to_table(v_CourseType,',')) AND T.Course_Category_Key IN (SELECT * from regexp_split_to_table(v_CourseCategory,',')) AND T.Course_Size_Key IN (SELECT * from regexp_split_to_table(v_CourseSize,','))
    GROUP BY T.Country,T.Business_Line,T.School,T.School_Key,T.Course_Type,T.Course_Type_Key,T.Offset_num
)P ON C.Country = P.Country
AND C.Business_Line = P.Business_Line
AND C.School = P.School
AND C.School_Key = P.School_Key
AND C.Course_Type = P.Course_Type
AND C.Course_Type_Key = P.Course_Type_Key
AND C.Offset_num = P.Offset_num;

END;
$$
LANGUAGE 'plpgsql';

上面是函数，需要40秒。如果直接查询，只需要4秒。因为是报表使用，所以更倾向于封装成函数。
本人是postgre新手。
请教各位大虾：
1. 写法上有没有优化的余地？（SQL Server里喜欢用临时表，但是postgre试了下，并没有提升）
2. 已经给School_Key，Offset_num，Course_Type_Key，Course_Category_Key，Course_Size_Key加了联合索引，速度比不加索引快了一些。加的对不对？
3. 试过RETURN QUERY EXECUTE，速度和RETURN QUERY是一样的。
4. 试过LANGUAGE sql，速度和LANGUAGE 'plpgsql' 也是一样的。

[ACMAIN_CHM]

引用

如果直接查询，只需要4秒。

直接查询是如何进行的？执行的哪个语句？

[siegebaoniu]

自己琢磨了下，找到问题了（虽然还是不懂postgre怎么调优） 后两个子查询用到了 WHERE (T.Date_Offset BETWEEN TIMEADD('YEAR',-2,v_StartDate) AND TIMEADD('YEAR',-2,v_EndDate)) 而TIMEADD是我自定义的函数，估计是系统搞不懂这是啥东西，可能没用索引，或者产生了其他的开销或不好的执行计划，就非常慢。 改成-interval '2 year'就飞快。

[siegebaoniu]

引用 1 楼 ACMAIN_CHM 的回复:

引用

如果直接查询，只需要4秒。

直接查询是如何进行的？执行的哪个语句？

直接查询就是执行return query后的东西，把变量用参数替换。