PostgreSQL函数连续调用5次后第6次性能骤降问题排查

环境与问题现象 #

在Ubuntu 18.04系统（8GB内存）上运行PostgreSQL 14.5，存在一个plpgsql函数f_client_getlistasshown，单次调用返回200行数据。

首次及连续前5次调用的执行计划（带analyze,buffers参数）：

db=# explain (analyze,buffers) Select * from f_client_getlistasshown('{"limit":"200","startdate":"2014-01-01","enddate":"2100-01-01","showRequiresActionFromTaxadvisor":false}');

--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on f_client_getlistasshown  (cost=0.25..10.25 rows=1000 width=400) (actual time=69.515..69.529 rows=200 loops=1)
   Buffers: shared hit=8939 dirtied=1
 Planning Time: 0.066 ms
 Execution Time: 70.282 ms
(4 rows)

上述调用均快速返回结果，但第6次调用时，执行计划变为：

Function Scan on f_client_getlistasshown  (cost=0.25..10.25 rows=1000 width=400) (actual time=8790.305..8790.319 rows=200 loops=1)
   Buffers: shared hit=2147651
 Planning Time: 0.034 ms
 Execution Time: 8790.351 ms

可见执行时间骤增，shared_buffers命中数异常偏高。当前shared_buffers设置为2GB，单独执行函数内部的查询无此问题。

可能的原因分析 #

• 操作系统内存压力导致缓存页换出：系统总内存8GB，shared_buffers配置为2GB，若此时有其他进程占用大量内存，操作系统会将PostgreSQL的缓存页换出到swap分区。第6次调用时需要重新加载这些被换出的页，导致执行时间暴增。此时shared hit数值异常高，是因为函数内部查询需要遍历大量缓存页（即使从swap重新加载，也会被统计为shared_buffers命中）。
• shared_buffers缓存被其他操作驱逐：第6次调用前，若有大查询或批量数据操作占用大量shared_buffers，PostgreSQL的LRU缓存替换策略会将该函数依赖的缓存页全部驱逐，导致本次调用需要重新从磁盘读取所有所需数据页，进而拉长执行时间。
• plpgsql函数内部执行计划变更：虽然单独执行内部查询正常，但函数执行时的上下文（如会话参数、事务状态、统计信息临时波动）可能导致内部查询生成低效执行计划（例如从索引扫描变为全表扫描）。若函数使用动态SQL且未做参数化处理，或执行计划缓存失效，都可能引发这类问题。
• 函数隐式资源占用或锁等待：函数内部若涉及临时表、物化视图刷新，或需要获取特定锁，前5次调用可能因缓存预热、锁已获取而快速执行；第6次调用时可能遇到锁等待、临时表重建等情况，导致执行时间骤增。

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