PostgreSQL自定义函数引发分区表全扫描性能缓慢问题求助

这个问题的核心在于PostgreSQL的分区剪枝（Partition Pruning）机制需要在查询规划阶段就能确定筛选条件的常量边界值，而你的自定义函数f_getsysdate()目前的实现方式让规划器无法提前计算出Shift_Date >= f_getsysdate() - 30的具体范围，导致只能扫描所有分区。下面是具体的分析和解决方案：

问题根源 #

你的f_getsysdate()是用PL/pgSQL编写的，虽然标记了STABLE（同一事务内返回值不变），但PL/pgSQL函数默认不会被查询规划器内联展开。规划器看不到函数内部的current_timestamp::timestamp(0)逻辑，无法在规划阶段计算出f_getsysdate() - 30的具体日期值，也就无法判断哪些分区的shift_date范围符合条件，只能被迫扫描所有分区。

另外，current_timestamp本身是STABLE级别的函数，这意味着它在事务内稳定，但规划器不会把它当作规划阶段的常量——除非函数能被内联，让规划器直接看到这个表达式。

解决方案 #

1. 将PL/pgSQL函数改为SQL函数（推荐） #

SQL语言编写的函数更容易被规划器内联，这样规划器就能直接解析到current_timestamp的逻辑，进而计算出筛选的日期范围，触发分区剪枝。修改后的函数如下：

CREATE OR REPLACE FUNCTION public.f_getsysdate()
RETURNS timestamp without time zone
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
SELECT current_timestamp::timestamp(0);
$$;
ALTER FUNCTION public.f_getsysdate() OWNER TO tms;

修改后重新执行你的查询，规划器应该能识别出需要扫描的分区（最近30天对应的分区），而不是全扫所有分区。

2. 提前计算筛选日期（临时 workaround） #

如果暂时无法修改函数，可以在查询中提前计算出起始日期，让规划器拿到明确的常量值：

方式一：使用CTE预计算日期

WITH date_bound AS (
    SELECT f_getsysdate() - interval '30' day AS start_shift_date
)
SELECT MAX(Vehicleentry_Code) 
FROM tbl_VehicleEntry, date_bound
WHERE Shift_Date >= date_bound.start_shift_date;

方式二：使用会话变量存储日期

-- 先计算并存储起始日期
SET temp.start_date = (SELECT f_getsysdate() - interval '30' day);

-- 再执行查询
SELECT MAX(Vehicleentry_Code) 
FROM tbl_VehicleEntry 
WHERE Shift_Date >= current_setting('temp.start_date')::timestamp without time zone;

这两种方式都能让规划器在规划阶段拿到明确的日期值，从而进行分区剪枝。

3. 验证分区约束的正确性 #

确保所有分区的CHECK约束都是准确且无重叠的，比如2017年的月度分区约束应该类似：

CHECK (shift_date >= '2017-01-01'::date AND shift_date < '2017-02-01'::date)

错误或重叠的约束会干扰规划器的分区剪枝逻辑，导致不必要的分区扫描。

验证效果 #

修改后执行EXPLAIN ANALYZE查询，查看执行计划中是否只扫描了符合日期范围的分区（比如最近30天对应的月度/年度分区），而不是所有分区。如果执行计划中出现Seq Scan on tbl_vehicleentry_xxxx只有符合条件的分区，说明分区剪枝生效了。

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