Postgres 12：重复执行存储过程后为何运行变慢？

你遇到的这个情况挺典型的：循环执行针对不同日期的存储过程，初期跑起来3-10秒就能搞定，结果连续跑1小时左右，不仅存储过程耗时涨到1分钟甚至更久，连目标表的简单查询都变慢了，而执行VACUUM ANALYZE后又能恢复。结合你补充的信息，核心根因已经明确是ReadIOPs超出分配配额，下面咱们一步步拆解：

1. 从执行计划看性能差异 #

先看快慢两种场景的执行计划核心区别：

• 快速执行时：查询依托合适的索引，缓冲区命中率极高，几乎不用碰磁盘，整个操作在内存里就高效完成了。
• 慢速执行时：磁盘读操作暴增（缓冲区的shared hit占比暴跌，shared read大幅上升），索引扫描的效率直接垮掉，自然耗时就上去了。

你的存储过程核心逻辑如下（整理后）：

-- 创建临时表并计算百分位数
create temp table t_rr as with cte as (
 SELECT period_id, classification_id,dtz,now() as create_dt,now() as update_dt,false as latest_record,ratio_1 
 FROM zpart.ratios_y2020 r 
 where classification_id is not null and universe_weight is not null and dtz='2020-03-28 00:00'::timestamptz
) 
SELECT period_id, classification_id, dtz,now() as create_dt,now() as update_dt,false as latest_record ,
 percentile_cont(array(SELECT generate_series(0, 99) :: NUMERIC / 100)) WITHIN GROUP (ORDER BY ratio_1) AS lower_bounds_1 
FROM cte r GROUP BY period_id, classification_id, dtz 
union all 
SELECT period_id, 1 as classification_id,dtz,now(),now(),false ,
 percentile_cont(array(SELECT generate_series(0, 99) :: NUMERIC / 100)) WITHIN GROUP (ORDER BY ratio_1) AS lower_bounds_1 
FROM cte r GROUP BY period_id, dtz 
union all 
SELECT period_id, 2 as classification_id,dtz,now(),now(),false ,
 percentile_cont(array(SELECT generate_series(0, 99) :: NUMERIC / 100)) WITHIN GROUP (ORDER BY ratio_1) AS lower_bounds_1 
FROM cte r where classification_id!=3 GROUP BY period_id, dtz;

-- 插入目标表并处理冲突
insert into zpart.rank_ranges_y2020(period_id, classification_id, dtz, create_dt, update_dt, latest_record, lower_bounds_1) 
SELECT period_id, classification_id, dtz, create_dt, update_dt, latest_record,lower_bounds_1 
FROM t_rr r 
on conflict (dtz,period_id,classification_id) do update set 
update_dt=now(), latest_record=EXCLUDED.latest_record,lower_bounds_1=EXCLUDED.lower_bounds_1;

-- 清理临时表
truncate t_rr;
drop table if exists t_rr;

2. 为什么VACUUM ANALYZE能临时救场？ #

长时间执行这类带更新的存储过程，数据库会积累几个问题，而VACUUM ANALYZE刚好能解决这些短期症状：

• 表与索引膨胀：INSERT ... ON CONFLICT DO UPDATE会生成大量死元组，表和索引的体积越来越大，磁盘IO的工作量直接翻倍。
• 统计信息过时：频繁的数据变更会让PostgreSQL的统计数据失效，优化器可能选到低效执行计划（比如放着索引不用去扫全表）。
• 缓存命中率暴跌：膨胀后的表和索引占更多内存，内存装不下就只能频繁读磁盘，越读越慢。

VACUUM负责清理死元组、回收磁盘空间，ANALYZE更新统计信息帮优化器选对计划，所以临时能把性能拉回来，但这只是治标。

3. 真正的元凶：ReadIOPs超出配额 #

你补充的ReadIOPs超配额才是核心原因，这也解释了为什么VACUUM只能管一时：

• 持续跑存储过程时，大量的磁盘读操作（尤其是处理膨胀的表和索引）会不断消耗IO配额。
• 配额用完后，云服务商（大概率是云环境，因为有IO配额限制）会限制磁盘IO速度，所有依赖磁盘的操作——不管是查询、写入还是VACUUM本身——都会变慢。
• 就算VACUUM清理了一些空间，后续的存储过程执行还是会继续消耗IO，很快又会把配额耗光，性能再次掉下去。

4. 实用优化建议 #

减少IO消耗的代码优化 #

• 别每次都创建临时表：可以复用临时表（比如先TRUNCATE再插入），或者直接用CTE结果插入目标表，减少不必要的磁盘写入。
• 确认分区策略合理：确保zpart.ratios_y2020的分区键是dtz，这样每次查询只会扫描目标分区，不会跨分区浪费IO。
• 优化索引：给zpart.ratios_y2020创建复合索引(dtz, classification_id, universe_weight)，精准覆盖查询条件，减少扫描的数据量。

环境层面优化 #

• 调整IO配额或存储类型：如果是云环境，升级存储的IO配额，或者换成SSD高性能存储，从根源上解决IO瓶颈。
• 自动化维护：用pg_cron定时执行VACUUM ANALYZE，定期清理死元组和更新统计信息，但这只是辅助手段，核心还是要减少IO消耗。
• 优化冲突更新：如果不是每次都需要更新，可以先判断数据是否变化再执行更新，减少死元组的产生，降低IO压力。

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