如何让 PostgreSQL FDW 将 LIMIT 下推到（单个）后端服务器？

我已经使用下表设置了一个 PostgreSQL FDW 服务器，由user_id四个以上的服务器分片：

CREATE TABLE my_big_table
(
    user_id bigint NOT NULL,
    serial bigint NOT NULL,         -- external, incrementing only
    some_object_id bigint NOT NULL,
    timestamp_ns bigint NOT NULL,
    object_type smallint NOT NULL,
    other_type smallint NOT NULL,
    data bytea
) PARTITION BY HASH (user_id) ;

CREATE SERVER shardA
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
 .
 .
 .
CREATE SERVER shardD
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');

create foreign table my_big_table_mod4_s0 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 0) server shardA
    OPTIONS (table_name 'my_big_table_mod4_s0');
 .
 .
 .
create foreign table my_big_table_mod4_s3 partition of my_big_table
    FOR VALUES WITH (MODULUS 4, REMAINDER 3) server shardD
    OPTIONS (table_name 'my_big_table_mod4_s3');

在后端服务器上，我设置了一个包含多个索引的表，其数据(user_id, serial)跨多个分区聚集。不过，我不认为这些细节与我的实际问题非常相关。

针对我的集群的常见查询采用以下模式：

SELECT * from my_big_table
WHERE
  user_id = 12345     -- only 1 user, always! --> single foreign server.
ORDER BY serial DESC  -- get 'newest' 90% of the time, 10% ASC
LIMIT 1000;           -- max limit 1000, sometimes less

对于 < 1000 条记录的用户：一切正常，没问题。

对于具有 > 100.000 条记录的用户，我看到了导致性能不佳的问题：explain显示 LIMIT 和排序发生在 FDW 上，而不是下推。为什么？

 Limit  (cost=927393.08..927395.58 rows=1000 width=32)
   Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
   ->  Sort  (cost=927393.08..931177.06 rows=1513592 width=32)
         Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, (length(my_big_table_mod4_s0.data))
         Sort Key: my_big_table_mod4_s0.serial DESC
         ->  Foreign Scan on public.my_big_table_mod4_s0  (cost=5318.35..844404.46 rows=1513592 width=32)
               Output: my_big_table_mod4_s0.serial, my_big_table_mod4_s0.some_object_id, my_big_table_mod4_s0.timestamp_ns, my_big_table_mod4_s0.object_type, my_big_table_mod4_s0.other_type, length(my_big_table_mod4_s0.data)
               Remote SQL: SELECT serial, some_object_id, timestamp_ns, object_type, other_type, data FROM public.my_big_table_mod4_s0 WHERE ((user_id = 4560084))
 JIT:
   Functions: 3
   Options: Inlining true, Optimization true, Expressions true, Deforming true

综上所述：

• 已选择单个后端服务器：OK！（解决了这个）
• Remote SQL: SELECT [...]表示没有 ORDER BY，没有 LIMIT。问题。

在后端服务器上执行直接显示：

 Limit  (cost=1.74..821.42 rows=1000 width=32)
   Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, (length(my_big_table_mod4_s0_part123.data))
   ->  Append  (cost=1.74..1240669.45 rows=1513592 width=32)
         ->  Index Scan Backward using my_big_table_mod4_s0_part123_pkey on public.my_big_table_mod4_s0_part123  (cost=0.43..290535.67 rows=355620 width=32)
               Output: my_big_table_mod4_s0_part123.serial, my_big_table_mod4_s0_part123.some_object_id, my_big_table_mod4_s0_part123.timestamp_ns, my_big_table_mod4_s0_part123.object_type, my_big_table_mod4_s0_part123.other_type, length(my_big_table_mod4_s0_part123.data)
               Index Cond: (my_big_table_mod4_s0_part123.user_id = 4560084)
         ->  Index Scan Backward using [... other partitions ...]

我尝试了什么：

• 由于 FDW 仍在开发中，我尝试使用更新的版本：FDW 和后端服务器都使用 11.4 和 12-beta2。没有观察到差异。
• 针对外部表运行 ANALYZE（在 FDW 实例上）。花费大量时间；看起来它正在扫描远程表的全表？查询计划没有区别。
• 更改fetch_size远程 SERVER 对象上的值。没有不同。
• use_remote_estimate=true在远程 SERVER 对象上设置。没有不同。
• fdw_tuple_cost=100在远程 SERVER 对象上设置。排序现在发生在远程服务器上，但 LIMIT 仍然没有被按下。

• 在网上寻找其他人看到这个，只显示这个相关的帖子：Missed LIMIT Clause pushdown in FDW API

  但是这个线程提到在 9.7 中修复这个问题等等，但我使用的是 11.4 和 12-beta2。还是我误解了这一点？

  并发布：外部表的奇怪成本估算显示了调整 FDW 的一个很好的例子，但不幸的是，它没有涵盖我的 LIMITs 问题。

• 快速浏览一下 PostgreSQL 源代码，我注意到这个语句，可能与 FDW 相关，也可能不相关（来源）。

  我们不能将包含 LIMIT/OFFSET 的子选择推送给工作人员，因为不能保证行顺序是完全确定的，并且应用 LIMIT/OFFSET 会导致顶层结果不一致。（在某些情况下，如果结果是有序的，我们可以放宽这个限制。但目前似乎不值得为此付出额外的努力。）

• 再看一下源代码，我发现了这个有希望的提交（d50d172e51）：

  这为 postgres_fdw 提供了处理 SELECT 命令的能力，以便它 1) 跳过 LockRows 步骤（如果有）（注意这是安全的，因为它执行早期锁定）和 2) 降低 LIMIT 和/或 OFFSET 限制（如果有) 到远端。这不处理 INSERT/UPDATE/DELETE 情况。

  它为我的案例添加了一个单元测试案例！

  -- and both ORDER BY and LIMIT can be shipped
  EXPLAIN (VERBOSE, COSTS OFF)
    SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
                                                                          QUERY PLAN                                                                         
  ------------------------------------------------------------------------------------------------------------------------------------------------------------
  Foreign Scan on public.ft1 t1
    Output: c1, c2, c3, c4, c5, c6, c7, c8
    Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2)) ORDER BY c2 ASC NULLS LAST LIMIT 1::bigint
  

  这应该是我已经运行的 12-beta2 的一部分...

^{我注意到我应该在这里按 DESC 顺序使用索引，但现在这不那么相关了。}