相同SQL查询封装为函数后耗时增至5倍，问题出在哪里？

• 通用执行计划适配性差：直接执行的SQL语句中使用的是字面常量，PostgreSQL查询规划器可以基于固定的常量值做针对性优化，比如提前计算日期区间边界、估算匹配行数后选择最优索引扫描路径。封装为函数后，cid、date_start、date_end都是动态参数，默认情况下规划器会生成适配所有参数输入的通用执行计划，无法针对具体传入的参数值做定制化优化，大概率会选择效率更低的扫描路径（比如全表扫描、低效率索引扫描）。
• 标量子查询重复执行：原SQL中的子查询因为条件是常量，规划器可以提前执行并将结果作为常量代入后续计算。但函数中的子查询条件是动态参数，每次调用都需要单独执行一次查询，额外增加了IO开销。

方案1：强制生成定制执行计划 #

在函数定义中新增配置强制每次调用都基于实际参数生成专属执行计划，和直接执行SQL的优化效果一致，适配PostgreSQL 12及以上版本：

CREATE OR REPLACE FUNCTION report.cp_active_vacancy_count_during_period(cid integer, date_start date, date_end date)
RETURNS bigint
LANGUAGE sql
STABLE LEAKPROOF PARALLEL SAFE
SET plan_cache_mode = force_custom_plan
AS $function$
 SELECT (SUM(added_vacancies) + (SELECT active_vacancies FROM report.vacancy_stats WHERE date = date_start AND company_id = cid LIMIT 1) - SUM(archived_vacancies)) as result
   FROM report.vacancy_stats
  WHERE company_id = cid
    AND vacancy_stats.date >= (date_start + 1)::DATE
    AND vacancy_stats.date <= date_end
  LIMIT 1;
$function$;

方案2：改写逻辑减少查询次数 #

将标量子查询改为左连接，避免重复查询，性能表现更稳定：

CREATE OR REPLACE FUNCTION report.cp_active_vacancy_count_during_period(cid integer, date_start date, date_end date)
RETURNS bigint
LANGUAGE sql
STABLE LEAKPROOF PARALLEL SAFE
SET plan_cache_mode = force_custom_plan
AS $function$
 SELECT (SUM(s.added_vacancies) + COALESCE(t.active_vacancies, 0) - SUM(s.archived_vacancies)) as result
   FROM report.vacancy_stats s
   LEFT JOIN report.vacancy_stats t ON t.company_id = cid AND t.date = date_start
  WHERE s.company_id = cid
    AND s.date >= (date_start + 1)::DATE
    AND s.date <= date_end
  GROUP BY t.active_vacancies
  LIMIT 1;
$function$;

方案3：新增覆盖索引 #

创建联合覆盖索引，避免查询回表，进一步提升执行效率：

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_vacancy_stats_company_date ON report.vacancy_stats (company_id, date) INCLUDE (active_vacancies, added_vacancies, archived_vacancies);

低版本PostgreSQL适配方案 #

如果使用的是PostgreSQL 12以下版本，不支持plan_cache_mode参数，可以改用PL/pgSQL动态SQL实现，每次执行都会基于实际参数生成执行计划：

CREATE OR REPLACE FUNCTION report.cp_active_vacancy_count_during_period(cid integer, date_start date, date_end date)
RETURNS bigint
LANGUAGE plpgsql
STABLE LEAKPROOF PARALLEL SAFE
AS $function$
DECLARE
    res bigint;
BEGIN
    EXECUTE format('
        SELECT (SUM(added_vacancies) + (SELECT active_vacancies FROM report.vacancy_stats WHERE date = %L AND company_id = %s LIMIT 1) - SUM(archived_vacancies)) as result
        FROM report.vacancy_stats
        WHERE company_id = %s
        AND date >= (%L + 1)::DATE
        AND date <= %L
        LIMIT 1;
    ', date_start, cid, cid, date_start, date_end) INTO res;
    RETURN res;
END;
$function$;

内容的提问来源于stack exchange，提问作者Daniel Ziltener