PostgreSQL自定义函数（UDF）执行远慢于原生SQL的优化求助

我使用的是PostgreSQL 12.2（64位，Visual C++ build 1914编译），遇到自定义函数性能异常问题：
调用函数语句：

Select * FROM get_process_single_real('Befuellung','P24A2A009','24_NTest','Gewicht');

该调用耗时可达15秒，但将函数内的SQL代码（去除BEGIN RETURN QUERY ... End语句）直接执行仅需数百毫秒。函数代码如下：

BEGIN
    RETURN QUERY SELECT
        tb_fixed_station.station_name station_bezeichnung,
        tb_fixed_process.process_name prozess_bezeichnung,
        tb_datas_process_detail.part_identifier werkstueck_ident,
        tb_datas_process_detail.job_identifier job_ident,
        tb_datas_process_detail.created_at zeitstempel,
        tb_param_setparameter.setparameter_name wert_bezeichnung,
        tb_datas_real_detail.real_value wert
    FROM 
        tb_datas_process_detail
    JOIN 
        tb_datas_real_detail ON (tb_datas_real_detail.id_datas_process_detail = tb_datas_process_detail.id)
    JOIN
        tb_param_setparameter ON (tb_datas_real_detail.id_param_setparameter = tb_param_setparameter.id)
    JOIN
        tb_fixed_process ON (tb_datas_process_detail.id_actfixed_process = tb_fixed_process.id)
    JOIN
        tb_fixed_station ON (tb_fixed_process.id_fixed_station = tb_fixed_station.id)
    WHERE
        tb_datas_process_detail.part_identifier = part_id
        and tb_datas_process_detail.job_identifier = job_id
        and tb_fixed_process.process_name = process
        and tb_param_setparameter.setparameter_name = in_setparameter_name
    LIMIT 1;
END; 

我知道UDF通常比原生SQL慢，但10倍以上的差距明显异常。想请教：

1. 是否可以让函数每次调用时重新编译执行计划？
2. 更新统计信息是否能解决该问题？

优化思路与问题解答 #

关于重新编译执行计划 #

可以实现每次调用重新生成执行计划，最直接的方案是改用动态SQL执行：
PL/pgSQL的静态SQL会缓存执行计划并复用给不同参数的调用，当参数对应的数据分布差异较大时，就会出现低效计划。改用EXECUTE+format生成动态SQL，PostgreSQL会为每次调用重新生成适配当前参数的执行计划：

BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT
            tb_fixed_station.station_name station_bezeichnung,
            tb_fixed_process.process_name prozess_bezeichnung,
            tb_datas_process_detail.part_identifier werkstueck_ident,
            tb_datas_process_detail.job_identifier job_ident,
            tb_datas_process_detail.created_at zeitstempel,
            tb_param_setparameter.setparameter_name wert_bezeichnung,
            tb_datas_real_detail.real_value wert
        FROM 
            tb_datas_process_detail
        JOIN 
            tb_datas_real_detail ON (tb_datas_real_detail.id_datas_process_detail = tb_datas_process_detail.id)
        JOIN
            tb_param_setparameter ON (tb_datas_real_detail.id_param_setparameter = tb_param_setparameter.id)
        JOIN
            tb_fixed_process ON (tb_datas_process_detail.id_actfixed_process = tb_fixed_process.id)
        JOIN
            tb_fixed_station ON (tb_fixed_process.id_fixed_station = tb_fixed_station.id)
        WHERE
            tb_datas_process_detail.part_identifier = %L
            AND tb_datas_process_detail.job_identifier = %L
            AND tb_fixed_process.process_name = %L
            AND tb_param_setparameter.setparameter_name = %L
        LIMIT 1',
        part_id, job_id, process, in_setparameter_name
    );
END;

另外，确保函数的稳定性属性为VOLATILE（PL/pgSQL函数默认就是该属性），但静态SQL的缓存逻辑不会因此改变，所以动态SQL是更可靠的方案。

关于更新统计信息 #

更新统计信息可能在数据分布变化较大时有用，但从你的情况看，原生SQL执行速度正常，说明当前统计信息足以支撑高效的执行计划，因此这个问题大概率不是统计信息过时导致的。如果要尝试，可执行以下命令更新相关表的统计信息：

ANALYZE tb_datas_process_detail;
ANALYZE tb_datas_real_detail;
ANALYZE tb_param_setparameter;
ANALYZE tb_fixed_process;
ANALYZE tb_fixed_station;

其他优化建议 #

• 对比执行计划：分别查看函数调用和原生SQL的执行计划，定位差异：

  -- 查看原生SQL执行计划
  EXPLAIN ANALYZE SELECT
      tb_fixed_station.station_name station_bezeichnung,
      tb_fixed_process.process_name prozess_bezeichnung,
      tb_datas_process_detail.part_identifier werkstueck_ident,
      tb_datas_process_detail.job_identifier job_ident,
      tb_datas_process_detail.created_at zeitstempel,
      tb_param_setparameter.setparameter_name wert_bezeichnung,
      tb_datas_real_detail.real_value wert
  FROM 
      tb_datas_process_detail
  JOIN 
      tb_datas_real_detail ON (tb_datas_real_detail.id_datas_process_detail = tb_datas_process_detail.id)
  JOIN
      tb_param_setparameter ON (tb_datas_real_detail.id_param_setparameter = tb_param_setparameter.id)
  JOIN
      tb_fixed_process ON (tb_datas_process_detail.id_actfixed_process = tb_fixed_process.id)
  JOIN
      tb_fixed_station ON (tb_fixed_process.id_fixed_station = tb_fixed_station.id)
  WHERE
      tb_datas_process_detail.part_identifier = 'P24A2A009'
      AND tb_datas_process_detail.job_identifier = '24_NTest'
      AND tb_fixed_process.process_name = 'Befuellung'
      AND tb_param_setparameter.setparameter_name = 'Gewicht'
  LIMIT 1;
  
  -- 查看函数调用执行计划
  EXPLAIN ANALYZE SELECT * FROM get_process_single_real('Befuellung','P24A2A009','24_NTest','Gewicht');
  

  通过对比可以明确是否是索引选择、表连接顺序等执行计划差异导致的性能差距。

• 调整函数COST属性：如果函数的COST设置过低，优化器可能会错误地优先选择函数调用而非更高效的执行路径。可尝试调整COST值：

  ALTER FUNCTION get_process_single_real(text, text, text, text) COST 100;
  

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