使用 postgres_fdw 在外部表中 ORDER BY 太慢

PostgreSQL v9.6，postgres_fdw

國外表

CREATE FOREIGN TABLE user_info (
 id bigint ,
 info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' );
-- user_info_raw is a large table (100 million records, 200 GB)

info 列樣本數據

{"key1": 1, "key2": 0.678}
{"key1": 1, "key2": 1.0}
{"key1": 1, "key2": 0.986} 
{"key1": 2, "key2": 0.75}
{"key1": 2, "key2": 0.639} 

外表查詢 （更新）

SELECT id, info 
FROM user_info
WHERE info ->> 'key1'= '1' -- OR using jsonb_extract_path_text(info, 'key1')  = '1'
ORDER BY id 
LIMIT 10; 

Limit  (cost=10750829.63..10750829.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)
  ->  Sort  (cost=10750829.63..10751772.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Foreign Scan on user_info (cost=100.00..10742677.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=68281020 loops=1)
              Filter: ((info ->> 'key1'::text) = '1'::text)
              Rows Removed by Filter: 7170443
Planning time: 4.097 ms
Execution time: 550059.597 ms

查詢 user_info_raw

EXPLAIN ANALYSE
SELECT id, info 
FROM user_info_raw
WHERE info ->> 'key1'= '1'
ORDER BY id 
LIMIT 10;

Limit  (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)
  ->  Index Scan using idx_user_info_raw_info on user_info_raw  (cost=0.57..68882850.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)
        Filter: ((info ->> 'key1'::text) = '1'::text)
Planning time: 0.192 ms
Execution time: 0.102 ms 

select pg_size_pretty(pg_table_size('user_info_raw'));
pg_size_pretty 
----------------
223 GB

在user_info_raw（遠端伺服器）上執行查詢需要10 毫秒。

但是，使用外部表需要很多時間。user_info當我刪除ORDER BY id時，查詢執行得非常快。

我認為我對外表的查詢 應該發送到遠端伺服器執行，但不是，我不知道為什麼，可能是由於postgres_fdw 文件的摘錄

預設情況下，只有使用內置運算符和函式的WHERE 子句 才會被考慮在遠端伺服器上執行。獲取行後，在本地檢查涉及**非內置函式的子句。**如果這些函式在遠端伺服器上可用並且可以依賴它們產生與本地相同的結果，則可以通過發送這樣的 WHERE 子句進行遠端執行來提高性能

如何解決外部表中與 ORDER BY 相關的問題？

---

更新

添加use_remote_estimate並server沒有foreign table幫助。

排序操作在本地 postgres 伺服器上執行，而不是在遠端. 如果您的程式碼總是對結果進行排序，您可以使用order by子句**在遠端 postgres 上創建一個視圖，然後創建一個指向該視圖的外部表。

在遠端伺服器上，添加一個視圖

CREATE VIEW vw_user_info_raw
SELECT id, info 
FROM user_info_raw
ORDER BY id 

在本地伺服器上，創建對該視圖的外部表引用

CREATE FOREIGN TABLE user_info (
 id bigint ,
 info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'vw_user_info_raw' );

請參閱 Postgres 管理員論壇

引用自：https://dba.stackexchange.com/questions/179744