如何讓 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 順序使用索引，但現在這不那麼相關了。
從版本 12 開始，這確實按預期工作，送出d50d172e51，但僅適用於非分區（非分片）表。
直接針對外部表名 (
my_big_table_mod4_s0) 執行查詢，LIMIT 被正確下推。報告為錯誤 ；我看不出為什麼這不適用於涉及的分區（分區修剪）的技術原因。
更新：事實證明，考慮到規劃器和分區修剪與 FDW 相結合的複雜性，這並不是一個真正的錯誤，而是更多的功能請求。上述送出的原作者表示可以為 PostgreSQL 13 完成這方面的工作。:-)
經驗教訓：FDW 並不是真正適用於所有類型查詢的高效查詢路由器（目前）。
對我來說，目前在 FDW 上使用分區（分片）表的解決方法是在 plpgsql 中創建一個函式，以根據聲明性分區佈局（
mod(user_id, 4)在我的情況下）確定外部表名稱。（我相信在這裡完全包含它已經超出了範圍。）