PostgreSQL分区表下SELECT查询调用自定义函数变慢问题咨询

问题根因 #

你猜测的问题完全正确，自定义函数确实会触发全分区扫描，核心原因如下：

• 你使用的plpgsql自定义标量函数默认稳定性级别为VOLATILE，数据库优化器无法将外层查询的clientid=2条件下推到函数内部的查询逻辑中，每次调用函数时，内部对JOBTABLE的查询都会扫描所有分区，无法触发分区剪枝。
• 你的查询会为employeetable中clientid=2的每一条员工记录调用一次GET_JOB_COUNT函数，相当于触发了N次全分区扫描（N为clientid=2的员工数），运行速度自然大幅下降。

解决方法 #

并不是分区后不能使用自定义函数，通过调整即可解决性能问题，可选方案如下：

1. 调整函数属性，支持分区剪枝
   将函数的稳定性级别改为STABLE（函数仅读取数据、不修改数据，相同输入在同一事务内返回结果一致），同时添加PARALLEL SAFE、LEAKPROOF属性（如果你的数据库版本支持），帮助优化器识别可以将参数下推到分区扫描逻辑中，示例修改：

CREATE OR REPLACE FUNCTION GET_JOB_COUNT(NUMERIC, NUMERIC) 
RETURNS NUMERIC 
LANGUAGE plpgsql
STABLE PARALLEL SAFE LEAKPROOF
AS $BODY$
DECLARE
    p_client_id             ALIAS FOR $1;
    p_employee_id       ALIAS FOR $2;
    v_is_count      NUMERIC := 0;
BEGIN  
    SELECT COUNT(JOB_ID) INTO v_is_count 
    FROM JOBTABLE 
    WHERE CLIENTID=p_client_id AND CREATEDBY=p_employee_id;
    RETURN v_is_count;
END; 
$BODY$;

2. 改用SQL函数替换plpgsql函数
   SQL函数的逻辑更容易被优化器解析和做条件下推，大部分场景下性能比plpgsql标量函数更好：

CREATE OR REPLACE FUNCTION GET_JOB_COUNT(NUMERIC, NUMERIC) 
RETURNS NUMERIC 
LANGUAGE sql
STABLE PARALLEL SAFE
AS $$
SELECT COUNT(JOB_ID) FROM JOBTABLE WHERE CLIENTID=$1 AND CREATEDBY=$2;
$$;

3. 放弃标量函数，改用JOIN统计（最推荐）
   直接用关联查询替代函数调用，优化器可以完全识别分区剪枝条件，仅扫描对应clientid的分区，性能最高：

SELECT 
    e.employeeid,
    e.employeename,
    COUNT(j.jobid) as job_count
FROM employeetable e
LEFT JOIN JOBTABLE j 
    ON j.clientid = e.clientid 
    AND j.createdby = e.employeeid
WHERE e.clientid=2
GROUP BY e.employeeid, e.employeename;

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