如何让postgres_fdw将小LIMIT的查询推送到外部服务器？

问题背景 #

使用postgres_fdw连接另一台PostgreSQL服务器，配置了较大的fetch_size与极大的fdw_startup_cost，多数查询运行正常。本地服务器为PostgreSQL 14.3，外部服务器为PostgreSQL 12.8。

外部服务器配置 #

CREATE SERVER the_foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host 'foreign_server_here',
  port '5432',
  dbname 'postgres',
  use_remote_estimate 'false',
  updatable 'false',
  truncatable 'false',
  fetch_size '100000',
  fdw_startup_cost '100000000000000000',
  fdw_tuple_cost '0.10'
);

问题现象 #

涉及两张表左连接的查询，设置大LIMIT时执行极快，小LIMIT时却异常缓慢：

SELECT id.…, sc.…
FROM invoice_detail id
LEFT JOIN supplier_catalog sc ON (id."VENDOR_SERVICE_ID" = sc."VENDOR_SERVICE_ID")
LIMIT 10000;

• 当设置LIMIT 10000时，查询耗时仅60ms，执行计划显示LIMIT已推送到外部服务器；
• 当设置LIMIT 100时，查询耗时近5秒，执行计划显示LIMIT未被推送到外部，而是在本地筛选100行。

解决方案 #

核心原因 #

设置的fdw_startup_cost过于极端，优化器认为启动远程查询的成本极高。当LIMIT很小时，优化器会判断：仅需100行的情况下，启动远程连接并执行连接的成本远高于将两张表数据拉到本地再处理的成本，因此选择了本地执行计划。

具体调整步骤 #

1. 降低fdw_startup_cost到合理范围
   无需设置到1e17的极端值，建议调整到既能让优化器优先选择远程执行复杂操作，又不会在小LIMIT时过度放大启动成本的数值，比如10000：

ALTER SERVER the_foreign_server OPTIONS (SET fdw_startup_cost '10000');

可根据实际测试结果逐步微调，找到最优值。

2. 启用远程统计信息估算
   当前use_remote_estimate设置为false，优化器无法获取远程表的真实统计数据，只能依赖本地默认值做成本估算，容易出现偏差。改为true让优化器基于远程表的真实统计信息做判断：

ALTER SERVER the_foreign_server OPTIONS (SET use_remote_estimate 'true');

3. 更新外部表的统计信息
   在外部PostgreSQL服务器上执行以下命令，确保两张表的统计信息是最新的，帮助优化器更准确地评估执行计划：

ANALYZE invoice_detail;
ANALYZE supplier_catalog;

内容的提问来源于stack exchange，提问作者King Chung Huang