[ADMIN] ORDER BY too slow in Foreign Table using postgres_fdw

Поиск
Список
Период
Сортировка
От Luan Huynh
Тема [ADMIN] ORDER BY too slow in Foreign Table using postgres_fdw
Дата
Msg-id CAAP3KKGQA0M9n+TqQbaGNoj24daw3Q7Ofo3CdWJN2V_X3Yva2w@mail.gmail.com
обсуждение исходный текст
Ответы Re: [ADMIN] ORDER BY too slow in Foreign Table using postgres_fdw  (Samed YILDIRIM <samed@reddoc.net>)
Список pgsql-admin
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
 

В списке pgsql-admin по дате отправления:

Предыдущее
От: Prashanth Reddy
Дата:
Сообщение: [ADMIN] Steps to place standby database in read write
Следующее
От: Samed YILDIRIM
Дата:
Сообщение: Re: [ADMIN] ORDER BY too slow in Foreign Table using postgres_fdw