连续5次调用后，函数调用性能下降

Question

我有这个plpgsql函数(在UBUNTU 18.04上运行PG 14.5与8GB Ram)，返回200行刚刚好:

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)

现在我重复这个查询5次，每次查询都很快返回结果。但在第六次尝试时:

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设置为2GB。如果我只执行函数内部调用的查询，我看不出问题，所以我没有在这里显示它。是什么原因造成的呢?

Answer 1

这是已知的问题。PL/pgSQL中嵌入SQL的前5次执行使用针对当前参数优化的自定义计划。在此之后，一些启发式选择是生成自定义计划还是使用一个通用计划。在您的例子中，函数中某些查询的泛型计划不能很好地工作。

最简单的解决方案就是为你的函数强制定制计划，如:

CREATE OR REPLACE FUNCTION f_client_getlistasshown(...)
RETURNS ...
AS $$
...
LANGUAGE plpgsql
SET plan_cache_mode TO force_custom_plan;