PostgreSQL函数添加COUNT子查询过滤后性能骤降求助

原始性能良好的函数 #

CREATE OR REPLACE FUNCTION my_function(param_a boolean, param_b boolean )
 RETURNS TABLE(blablabla)
 LANGUAGE sql
 IMMUTABLE
AS $function$
with data as (
select id,amount,account_nr from transfer
)
select * from 
data d
where  param_a or 0.00 <> (select sum(d2.amount)
                        from data d2
                        where d2.id = d.id)
$function$;

原始执行计划 #

(cost=0.25..10.25 rows=1000 width=560)  
(actual time=1162.528..1162.561 rows=306 loops=1)  
Buffers: shared hit=1099180  
Planning time: 2.928 ms  
Execution time: 1162.630 ms

添加COUNT过滤条件后的函数（性能骤降） #

CREATE OR REPLACE FUNCTION my_function(param_a boolean, param_b boolean )
 RETURNS TABLE(blablabla)
 LANGUAGE sql
 IMMUTABLE
AS $function$
with data as (
select id,amount,account_nr from transfer
)
select * from 
data d
where (param_b or 1 < (select count(d2.account_nr) 
                        from data d2
                        where d2.id = d.id
                        group by d2.account_nr))
and (param_a or 0.00 <> (select sum(d2.amount)
                        from data d2
                        where d2.id = d.id))
$function$;

修改后执行计划 #

(cost=0.25..10.25 rows=1000 width=560)  
(actual time=271191.341..271191.383 rows=306 loops=1)  
Buffers: shared hit=1099180  
Planning time: 2.955 ms 
Execution time: 271191.463 ms

性能骤降原因分析 #

新增的COUNT子查询是性能恶化的核心：

• 该子查询需要对data中的每一条记录（按id匹配）执行一次分组统计，相当于在嵌套循环中反复执行聚合操作，时间复杂度随数据量呈指数级上升。
• 原函数的SUM子查询虽然也是相关子查询，但逻辑相对简单；而COUNT+GROUP BY的组合需要额外的分组、统计步骤，若transfer表没有针对id+account_nr的复合索引，数据库会频繁做全表扫描或索引扫描匹配数据。

优化方案 #

1. 预计算聚合结果（CTE中提前统计） #

把需要的SUM和COUNT聚合结果提前在CTE中计算完成，避免每条记录重复执行子查询：

CREATE OR REPLACE FUNCTION my_function(param_a boolean, param_b boolean )
 RETURNS TABLE(blablabla)
 LANGUAGE sql
 IMMUTABLE
AS $function$
with data as (
    select id, amount, account_nr from transfer
),
agg_data as (
    select 
        id,
        sum(amount) as total_amount,
        count(distinct account_nr) as account_count  -- 若需统计所有行数（含重复），改用count(account_nr)
    from data
    group by id
)
select d.* from data d
join agg_data ad on d.id = ad.id
where 
    (param_a or ad.total_amount <> 0.00)
    and (param_b or ad.account_count > 1)
$function$;

2. 优化索引 #

为transfer表创建复合索引，覆盖聚合查询所需字段，避免回表扫描：

CREATE INDEX idx_transfer_id_account_amount ON transfer(id, account_nr, amount);

3. 用JOIN替代相关子查询 #

相关子查询本身效率较低，改用JOIN关联预聚合结果，能让PostgreSQL优化器生成更高效的执行计划（如哈希连接、合并连接），避免嵌套循环带来的性能损耗。

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