我使用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
的工作?
我最终将fdw_tuple_cost
增加到100000000000000000
(一个任意大的数字),使查询计划在服务器之间传输数据变得非常昂贵。
fwd_tuple_cost
该选项可以为外部服务器指定,它是一个浮点值,用作该服务器上外部表扫描的每个元组的额外成本。这表示服务器之间数据传输的额外开销。您可以增加或减少这个数字,以反映远程服务器的网络延迟的增加或减少。默认值为0.01。
设置好后,本地服务器不再拉过来额外的数据并从中进行选择。它将所有限制发送到外部服务器,这是我更喜欢的。