PostgreSQL外部表嵌套WHERE查询引发网络负载过高问题

我本地有个PostgreSQL数据库，需从远程PostgreSQL数据库获取大量数据，远程表在本地以外部表形式存在。遇到的核心问题是：查询该外部表时，若WHERE条件使用嵌套SELECT，会先将整个远程表通过网络传输到本地后再执行过滤；但将嵌套SELECT结果硬编码为ID列表时，仅会传输符合条件的数据。

示例对比 #

示例1（硬编码ID列表） #

SELECT * FROM bi.booking WHERE book_id IN (1,2,3, ... ,9999999)

耗时约2-3秒。

示例2（嵌套SELECT） #

SELECT * FROM bi.booking WHERE book_id IN (SELECT DISTINCT(book_id) FROM bi.temp_new_booking_history)

耗时超20分钟，原因是整个bi.booking表先被传输到本地。

我的目标是仅传输本地表中匹配ID子集的数据，目前尝试的方法均未成功：

• 使用string_agg构建列表，IN子句报错：operator does not exist: bigint = text
• 使用array_agg构建数组，IN子句报错：operator does not exist: bigint = bigint[]

更新 - 2022.12.14 14:12 #

补充几种查询的EXPLAIN结果：

原始查询 #

查询语句：

SELECT * FROM bi.booking WHERE book_id IN (SELECT DISTINCT(book_id) FROM bi.temp_new_booking_history)

EXPLAIN结果：

"Nested Loop  (cost=177.00..39787.58 rows=625697 width=373)"
"  ->  HashAggregate  (cost=76.58..80.23 rows=366 width=8)"
"        Group Key: temp_new_booking_history.book_id"
"        ->  Seq Scan on temp_new_booking_history  (cost=0.00..75.66 rows=366 width=8)"
"  ->  Foreign Scan on booking  (cost=100.43..108.47 rows=1 width=1538)"

硬编码ID列表的查询 #

查询语句：

EXPLAIN SELECT * FROM keahotelsl26.booking WHERE book_id IN (1,2,3,4,...,366);

EXPLAIN结果：

"Foreign Scan on booking  (cost=100.42..3047.70 rows=366 width=1538)"

Laurenz Albe建议的查询 #

查询语句：

EXPLAIN SELECT b.* FROM bi.booking AS b WHERE EXISTS (SELECT FROM bi.temp_new_booking_history AS h WHERE b.book_id = h.book_id)

EXPLAIN结果：

"Nested Loop  (cost=177.00..39783.92 rows=312848 width=373)"
"  ->  HashAggregate  (cost=76.58..80.23 rows=366 width=8)"
"        Group Key: h.book_id"
"        ->  Seq Scan on temp_new_booking_history h  (cost=0.00..75.66 rows=366 width=8)"
"  ->  Foreign Scan on booking b  (cost=100.43..108.47 rows=1 width=1538)"

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