基于postgres_fdw的外部表建表慢查询优化问询

问题根源分析 #

从执行分析结果可以明确核心耗时点：

• 前三个db1外部表JOIN后返回了3033849行数据，随后通过嵌套循环对db2_foreigntable1发起了3033849次远程扫描，但每次扫描都返回0行，这部分无效远程调用占用了绝大多数执行时间（总执行时间超12分钟）。
• 优化器选择的嵌套循环策略在此场景下完全不适用，大量远程交互的开销直接拖垮了查询性能。

具体优化步骤 #

1. 调整JOIN顺序，优先过滤小数据集

将db2的过滤条件提前执行，先获取符合st='A'的少量tr值，再关联db1的表，从根源减少后续JOIN的数据量：

select distinct a."scid", a."tt", a."p"
into tableA
from (
    -- 先从db2获取过滤后的小数据集
    select tr from db2_foreigntable1 where st = 'A'
) d
join db1_foreigntable3 c on c."tr" = d."tr"
join db1_foreigntable1 a on a."scid" = c."scid"
join db1_foreigntable2 b on a."scid" = b."obid"
WHERE b."vid" = 'ck98098089';

2. 强制禁用嵌套循环，改用HASH JOIN/MERGE JOIN

跨库JOIN场景下，嵌套循环易引发大量远程调用，可临时禁用嵌套循环引导优化器选择更高效的JOIN方式：

-- 临时禁用嵌套循环
set enable_nestloop = off;

-- 执行目标查询
select distinct a."scid", a."tt", a."p"
into tableA
from db1_foreigntable1 a
join db1_foreigntable2 b on  a."scid" = b."obid"
join db1_foreigntable3 c on a."scid" = c."scid"
join db2_foreigntable1 d on c."tr" = d."tr" and d."st" = 'A'
WHERE b."vid" = 'ck98098089';

-- 恢复默认配置
set enable_nestloop = on;

PostgreSQL 12+也可使用查询提示直接指定JOIN方式：

select distinct a."scid", a."tt", a."p"
into tableA
from db1_foreigntable1 a
join db1_foreigntable2 b on  a."scid" = b."obid"
join db1_foreigntable3 c on a."scid" = c."scid"
join /*+ HashJoin(d) */ db2_foreigntable1 d on c."tr" = d."tr" and d."st" = 'A'
WHERE b."vid" = 'ck98098089';

3. 为远程表添加针对性索引

在远程库创建索引，减少远程扫描的数据量，提升过滤和JOIN效率：

• 在db2数据库执行：

  create index idx_db2_ft1_st_tr on db2_foreigntable1(st, tr);
  

• 在db1数据库执行：

  create index idx_db1_ft2_vid_obid on db1_foreigntable2(vid, obid);
  create index idx_db1_ft3_scid_tr on db1_foreigntable3(scid, tr);
  

4. 优化postgres_fdw配置与统计信息

• 更新远程库统计信息，确保优化器能获取准确的行数估算：

  -- 在db1和db2数据库分别执行
  analyze db1_foreigntable1;
  analyze db1_foreigntable2;
  analyze db1_foreigntable3;
  analyze db2_foreigntable1;
  

• 开启异步远程扫描（PostgreSQL 11+支持），减少等待时间：

  alter server db2_server options (add async_capable 'true');
  

• 根据网络情况调整fetch_size：若网络延迟高可适当调大，若内存压力大则调小，测试找到最优值。

5. 拆分查询，分步处理

先将db1的JOIN结果拉到本地临时表，再关联db2的表，减少远程调用次数：

-- 第一步：获取db1的中间结果到本地临时表
create temp table temp_db1_data as
select distinct a."scid", a."tt", a."p", c."tr"
from db1_foreigntable1 a
join db1_foreigntable2 b on  a."scid" = b."obid"
join db1_foreigntable3 c on a."scid" = c."scid"
WHERE b."vid" = 'ck98098089';

-- 第二步：关联db2表生成最终结果
select distinct "scid", "tt", "p"
into tableA
from temp_db1_data t
join db2_foreigntable1 d on t."tr" = d."tr" and d."st" = 'A';

-- 清理临时表
drop temp table temp_db1_data;

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