优化foreign data wrapper外表配合本地子查询IN子句的查询速度

正确实现写法 #

写法1：修复数组匹配写法 #

你之前的array_agg写法错误是因为子查询返回的是包含数组的行集合，而非直接的数组值，多加一层括号将子查询结果转为标量数组即可正常运行：

SELECT * FROM foreign_table 
WHERE linked_item_id = ANY(
    (SELECT array_agg(id) FROM local_item_table WHERE my_condition)
);

写法2：强制物化CTE（推荐，兼容性更好） #

PostgreSQL 12及以上版本可以用MATERIALIZED关键字强制CTE优先执行本地查询，避免规划器把查询展开为两表关联，触发FDW条件下推：

WITH local_ids AS MATERIALIZED (
    SELECT id FROM local_item_table WHERE my_condition
)
SELECT * FROM foreign_table 
WHERE linked_item_id IN (SELECT id FROM local_ids);

写法3：临时表适配旧版本 #

如果使用的是PostgreSQL 12之前的版本，不支持CTE物化语法，可以先把本地查询结果存入临时表再查询：

-- 生成临时id表
CREATE TEMP TABLE temp_local_ids AS 
SELECT id FROM local_item_table WHERE my_condition;
-- 关联外表查询
SELECT * FROM foreign_table 
WHERE linked_item_id IN (SELECT id FROM temp_local_ids);
-- 清理临时表
DROP TABLE temp_local_ids;

慢查询原理说明 #

这是FDW的典型限制：默认情况下PostgreSQL查询规划器会将嵌套子查询/关联查询拉平为两表JOIN操作，FDW无法将本地表的过滤结果下推到远端数据源，只能拉取整个外表的全量数据到本地做匹配，导致大量IO和计算开销。
上述三种写法都是先提前计算出本地的id列表，再传给FDW做查询，FDW可以把IN (id1,id2,id3)的过滤条件直接下推到远端执行，所以速度和你分开执行两个查询的性能一致。

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