PostgreSQL FDW外部表执行5次相同查询后性能指数级下降问题

现象原因解释 #

1. 触发性能突变的根由：PL/pgSQL执行计划切换逻辑 #

PostgreSQL的PL/pgSQL函数内部的查询语句，默认会采用先定制计划、后通用计划的缓存策略：

• 前5次执行时，每次都针对当前的变量值生成定制化的执行计划，此时性能最优
• 第6次执行开始，PostgreSQL会评估生成通用执行计划（不绑定具体变量值，可重复使用）的成本，如果认为通用计划成本更低，就会切换为通用计划执行，之后的查询都会复用这个通用计划

2. FDW默认配置导致通用计划选择错误 #

use_remote_estimate参数默认是关闭状态，本地PostgreSQL节点不会向远端请求查询成本估算，仅凭借本地存储的少量FDW表统计信息做判断：

• 当用id = ANY(数组变量)作为查询条件时，本地节点无法感知远端表id字段上的索引，错误估算出「拉取全表到本地过滤」比「下推过滤条件到远端走索引」的成本更低，因此生成的通用计划不会下推过滤条件，而是直接把FDW全表数据拉到本地再匹配数组，导致执行耗时飙升
• 当你把条件写死为IN(常量列表)时，查询条件是固定常量，PostgreSQL生成计划时可以直接把整个过滤条件下推到远端数据库，远端直接走索引返回少量结果，因此全程性能稳定

3. 为什么ANALYZE无效 #

本地执行ANALYZE jammerdal只能收集FDW表的极少量抽样统计信息，无法获取远端表的完整统计、索引信息，因此纠正不了成本估算错误的问题。

解决方案说明 #

执行ALTER SERVER testdb OPTIONS (ADD use_remote_estimate 'true');后，本地FDW节点会将查询参数发送给远端PostgreSQL，由远端返回准确的执行成本估算，本地节点就会选择「下推过滤条件到远端走索引」的最优计划，哪怕切换为通用计划也不会出现性能暴跌的问题。

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