PostgreSQL带参数表函数性能远逊于视图，求优化方案

针对关联查询下表函数比视图慢的问题，核心原因是默认SQL表函数不会被查询优化器内联展开，而视图会被完全合并到主查询中，优化器可生成更高效的执行计划。以下是可行的优化方案：

1. 为函数添加稳定性属性 #

PostgreSQL默认将SQL函数标记为VOLATILE，这会让优化器认为函数结果可能随时变化，从而避免内联。你的函数逻辑基于输入时间参数，在事务内结果稳定，标记为STABLE可帮助优化器判断可以内联函数逻辑：

CREATE OR REPLACE FUNCTION public.get_active_locations(at_date_time timestamp with time zone)
 RETURNS TABLE(location_id character varying)
 LANGUAGE sql
 STABLE -- 标记函数为事务内稳定
AS $function$
select location_id from locations 
where activated_date >= at_date_time 
and (deactivated_date <= at_date_time or deactivated_date is null)
$function$;

2. 显式标记函数为可内联（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持INLINEABLE属性，显式告知优化器该函数可被内联到主查询中，进一步提升优化空间：

CREATE OR REPLACE FUNCTION public.get_active_locations(at_date_time timestamp with time zone)
 RETURNS TABLE(location_id character varying)
 LANGUAGE sql
 STABLE
 INLINEABLE -- 显式标记可内联
AS $function$
select location_id from locations 
where activated_date >= at_date_time 
and (deactivated_date <= at_date_time or deactivated_date is null)
$function$;

3. 改用参数化视图 #

参数化视图兼顾视图的可优化性和函数的参数化需求，通过内部参数子句传递条件，优化器会像处理普通视图一样展开逻辑：

CREATE OR REPLACE VIEW public.get_active_locations_view AS
SELECT l.location_id
FROM locations l,
     (SELECT NULL::timestamp with time zone AS at_date_time) params
WHERE l.activated_date >= params.at_date_time
  AND (l.deactivated_date <= params.at_date_time OR l.deactivated_date IS NULL);

查询时通过过滤参数子句字段传入时间值：

SELECT * FROM get_active_locations_view WHERE at_date_time = '2024-05-01 12:00:00+00';

4. 优化底层表索引 #

无论使用函数还是视图，合适的索引都能大幅提升查询性能。针对你的筛选逻辑，可创建以下索引：

-- 覆盖筛选条件的复合索引
CREATE INDEX idx_locations_active_dates ON locations (activated_date, deactivated_date);

-- 仅包含激活状态记录的部分索引，更小更高效
CREATE INDEX idx_locations_active ON locations (location_id)
WHERE (deactivated_date IS NULL OR deactivated_date > CURRENT_TIMESTAMP);

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