ORDER BY too slow in Foreign Table using postgres_fdw

[Luan Huynh <nnhluan@xxxxxxxxx>]

Hi all, 

On PostgreSQL v.9.6, when using postgres_fdw, I got an issue with "ORDER BY" (here's my question on stackexchange ).

Query on Foreign Table

SELECT id, info 

FROM user_info

WHERE 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

Query on remote server

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 

Please help me to figure out the solution for that issue .

Thank you