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

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

From
Luan Huynh
Date:
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
 

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

From
Samed YILDIRIM
Date:
Hi Luan,
 
Sort operation is performed on local postgres server, not on remote. Because of that, local postgresql server gets all rows matched by filter and then sort them. If your code always sort the results, you can just create a view on remote postgres with order by clause and then create a foreign table pointing to the view. By using a view like that, sorting is performed on remote server.
 
On remote Postgres
create view user_info_vw as select id,info from user_info order by id;
 
On local Postgres
create foreign table user_info (id bigint, info jsonb) server luan_server options (schema_name 'public', table_name 'user_info_vw');
 
 
Before view
postgres=# explain analyze SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' order by id limit 10;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153.28..153.29 rows=6 width=40) (actual time=7512.755..7512.759 rows=10 loops=1)
   ->  Sort  (cost=153.28..153.29 rows=6 width=40) (actual time=7512.754..7512.757 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info  (cost=100.00..153.20 rows=6 width=40) (actual time=0.962..7351.989 rows=1187840 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 786432
 Planning time: 0.089 ms
 Execution time: 7513.322 ms
(9 rows)
 
 
After view
explain analyze SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' LIMIT 10;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=100.00..153.20 rows=6 width=40) (actual time=0.678..0.684 rows=10 loops=1)
   ->  Foreign Scan on user_info  (cost=100.00..153.20 rows=6 width=40) (actual time=0.677..0.681 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
         Rows Removed by Filter: 4
 Planning time: 0.060 ms
 Execution time: 1.167 ms
(6 rows)
 
postgres=# SELECT id, info
postgres-# FROM user_info
postgres-# WHERE info ->> 'key1'= '1' LIMIT 10;
 id |            info            
----+----------------------------
  1 | {"key1": 1, "key2": 0.678}
  2 | {"key1": 1, "key2": 0.678}
  3 | {"key1": 1, "key2": 1.0}
  4 | {"key1": 1, "key2": 0.986}
  7 | {"key1": 1, "key2": 0.678}
  8 | {"key1": 1, "key2": 1.0}
  9 | {"key1": 1, "key2": 0.986}
 12 | {"key1": 1, "key2": 0.678}
 13 | {"key1": 1, "key2": 1.0}
 14 | {"key1": 1, "key2": 0.986}
(10 rows)
 
Best regards.


İyi çalışmalar.
Samed YILDIRIM



18.07.2017, 10:06, "Luan Huynh" <nnhluan@gmail.com>:
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
 

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

From
Luan Huynh
Date:
Thank your help, Samed.
That idea is great.

After apply your idea,  for testing, I try to add some columns in the remote table, then try to filter them. As a result, the query will slow again (due to order by).


On Tue, Jul 18, 2017 at 7:38 PM, Samed YILDIRIM <samed@reddoc.net> wrote:
Hi Luan,
 
Sort operation is performed on local postgres server, not on remote. Because of that, local postgresql server gets all rows matched by filter and then sort them. If your code always sort the results, you can just create a view on remote postgres with order by clause and then create a foreign table pointing to the view. By using a view like that, sorting is performed on remote server.
 
On remote Postgres
create view user_info_vw as select id,info from user_info order by id;
 
On local Postgres
create foreign table user_info (id bigint, info jsonb) server luan_server options (schema_name 'public', table_name 'user_info_vw');
 
 
Before view
postgres=# explain analyze SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' order by id limit 10;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=153.28..153.29 rows=6 width=40) (actual time=7512.755..7512.759 rows=10 loops=1)
   ->  Sort  (cost=153.28..153.29 rows=6 width=40) (actual time=7512.754..7512.757 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info  (cost=100.00..153.20 rows=6 width=40) (actual time=0.962..7351.989 rows=1187840 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 786432
 Planning time: 0.089 ms
 Execution time: 7513.322 ms
(9 rows)
 
 
After view
explain analyze SELECT id, info
FROM user_info
WHERE info ->> 'key1'= '1' LIMIT 10;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=100.00..153.20 rows=6 width=40) (actual time=0.678..0.684 rows=10 loops=1)
   ->  Foreign Scan on user_info  (cost=100.00..153.20 rows=6 width=40) (actual time=0.677..0.681 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
         Rows Removed by Filter: 4
 Planning time: 0.060 ms
 Execution time: 1.167 ms
(6 rows)
 
postgres=# SELECT id, info
postgres-# FROM user_info
postgres-# WHERE info ->> 'key1'= '1' LIMIT 10;
 id |            info            
----+----------------------------
  1 | {"key1": 1, "key2": 0.678}
  2 | {"key1": 1, "key2": 0.678}
  3 | {"key1": 1, "key2": 1.0}
  4 | {"key1": 1, "key2": 0.986}
  7 | {"key1": 1, "key2": 0.678}
  8 | {"key1": 1, "key2": 1.0}
  9 | {"key1": 1, "key2": 0.986}
 12 | {"key1": 1, "key2": 0.678}
 13 | {"key1": 1, "key2": 1.0}
 14 | {"key1": 1, "key2": 0.986}
(10 rows)
 
Best regards.


İyi çalışmalar.
Samed YILDIRIM



18.07.2017, 10:06, "Luan Huynh" <nnhluan@gmail.com>:
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