PostgreSQL从9.6升级至14.0后外部表查询速度骤降如何排查优化

• 第一步先比对执行计划：在本地14实例上，对创建物化视图的对应查询执行EXPLAIN ANALYZE VERBOSE 你的查询SQL，重点核对三个特征：
  1. 是否出现本地嵌套循环（Nested Loop）驱动外部表扫描的情况，单次循环触发一次远端查询
  2. 过滤条件、聚合、排序、关联操作是否没有下推到远端，而是拉取全量数据到本地执行
  3. 外部表的预估扫描行数和实际返回行数是否差2个数量级以上
     9.6到14版本postgres_fdw的成本估算逻辑、下推规则变动极大，9.6版本不支持外部表本地统计，优化器用固定成本值估算，反而大概率选全表拉取的最优计划；14版本依赖本地存储的外部表统计信息算成本，如果统计信息缺失，会把大表行数估成几百到几千行，直接选错嵌套循环的执行计划，这是大版本升级后FDW性能暴跌的最常见原因。
• 检查postgres_fdw扩展版本：大版本升级后必须在每个业务库执行ALTER EXTENSION postgres_fdw UPDATE;，把扩展的动态库、函数定义升级到和数据库实例匹配的14版本。如果没做这步，你配置的async_capable等新参数完全不生效，甚至会出现旧版扩展逻辑和新版优化器不兼容的问题，触发异常执行路径。
• 抓远端实际执行的SQL：在远端库临时设置log_statement = 'all'，触发本地查询后看远端日志，如果看到单条全表查询，说明下推正常，瓶颈在网络或者数据拉取效率；如果看到成千上万条重复的带WHERE条件的小查询，100%是执行计划走了嵌套循环逐批拉取。
• 检查外部表本地统计信息：执行SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname = 'mylocalschema';，看外部表的实时行数统计是不是和远端实际值差很多，9.6升级上来的实例，所有外部表初始统计值都是空的。

• 先补全外部表统计：对所有涉及查询的外部表执行ANALYZE mylocalschema.表名;，如果是亿级以上大表，可以先把当前会话的default_statistics_target设为1000再跑ANALYZE，让优化器拿到更准确的数据分布统计，90%的升级后性能劣化问题做完这步就能恢复。
• 调整FDW服务端配置参数：
  现有配置里加use_remote_estimate 'true'，让优化器生成执行计划时直接发EXPLAIN请求到远端拿真实的成本估算，比本地存的统计信息准确度高很多，大表场景下执行计划选错的概率会大幅降低。
  你现在设的fetch_size '100000'是合理的，单表全量拉取场景可以适当调到200000，减少网络往返次数，注意不要设太大导致本地内存占用过高。
  你配置的async_capable 'true'只在同时查询同一个远端实例上的多个外部表时才生效，单表全量拉取场景这个参数不会带来性能提升，不用在这个参数上浪费排查时间。
  确认pushdown_sorts、pushdown_aggregation两个参数是开启状态，14版本默认是开的，但是如果是从9.6升级上来的旧服务器配置，可能存在参数残留把这俩选项关了，导致排序、聚合没法下推。
• 针对全量物化视图场景做专项优化：如果是整表拉取做物化视图，不需要复杂下推逻辑的话，可以绕开FDW的执行层开销，直接用COPY管道做数据同步，性能比FDW高30%~50%，TB级大表提升更明显。
• 排查分区表相关参数：如果你的外部表是分区表，记得把enable_partitionwise_join、enable_partitionwise_aggregate设为on，14版本这俩参数默认是关闭的，关闭状态下FDW没法把分区查询下推到对应远端分区，会拉取所有分区的数据到本地做处理，性能会差几个数量级。
• 强制修正执行计划：如果统计信息补全后还是选不到正确的远端扫描计划，可以安装pg_hint_plan扩展，通过hint强制查询走RemoteScan，把所有计算逻辑下推到远端，避免本地二次处理。

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