如何让postgres_fdw与小限制推查询到外部服务器?

Question

我使用postgres_fdw连接到另一个PostgreSQL服务器。国外的数据包装器目前配置了一个大的fetch_size和一个极大的fdw_startup_cost。这对我的大多数查询都很有效。我这样做部分是基于https://awide.io/strange-cost-estimation-for-foreign-tables/。

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'
);

本地服务器是PostgreSQL 14.3。PostgreSQL 12.8.

然而，我遇到的一个查询涉及两个表的左连接，当我有一个小限制时，执行时间很长，但是当我有一个大限制时，执行时间几乎是瞬间的。下面是查询，为了简洁起见，省略了列名。

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，查询运行得非常快，在60毫秒内执行，如EXPLAIN ANALYZE VERBOSE输出所示。

Foreign Scan  (cost=100000000000000000.00..100000000220463968.00 rows=10000 width=167) (actual time=59.088..59.691 rows=10000 loops=1)
Output: …
Relations: (docpadmin.invoice_detail id) LEFT JOIN (docpadmin.supplier_catalog sc)
Remote SQL: SELECT … FROM (docpadmin.invoice_detail r1 LEFT JOIN docpadmin.supplier_catalog r2 ON (((r1."VENDOR_SERVICE_ID" = r2."VENDOR_SERVICE_ID")))) LIMIT 10000::bigint
Query Identifier: -9026301604523228886
Planning Time: 0.201 ms
Execution Time: 62.951 ms

但是，如果我减少选择为LIMIT 100，查询需要近5秒的时间来运行。

Limit  (cost=100000000000000000.00..100000000002320672.00 rows=100 width=167) (actual time=4822.803..4822.826 rows=100 loops=1)
Output: …
->  Foreign Scan  (cost=100000000000000000.00..100007212950048160.00 rows=310813344 width=167) (actual time=4822.801..4822.815 rows=100 loops=1)
Output: …
Relations: (docpadmin.invoice_detail id) LEFT JOIN (docpadmin.supplier_catalog sc)
Remote SQL: SELECT … FROM (docpadmin.invoice_detail r1 LEFT JOIN docpadmin.supplier_catalog r2 ON (((r1."VENDOR_SERVICE_ID" = r2."VENDOR_SERVICE_ID"))))
Query Identifier: -9026301604523228886
Planning Time: 0.191 ms
Execution Time: 5006.872 ms

如果我理解正确的话，PostgreSQL没有将LIMIT推到外部服务器，而是在本地选择100行。我如何阻止它这样做，让外部服务器做LIMIT 10000的工作?

Answer 1

我最终将fdw_tuple_cost增加到100000000000000000(一个任意大的数字)，使查询计划在服务器之间传输数据变得非常昂贵。

fwd_tuple_cost

该选项可以为外部服务器指定，它是一个浮点值，用作该服务器上外部表扫描的每个元组的额外成本。这表示服务器之间数据传输的额外开销。您可以增加或减少这个数字，以反映远程服务器的网络延迟的增加或减少。默认值为0.01。

设置好后，本地服务器不再拉过来额外的数据并从中进行选择。它将所有限制发送到外部服务器，这是我更喜欢的。