Re: ORDER BY too slow in Foreign Table using postgres_fdw

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(at)reddoc(dot)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(at)gmail(dot)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
> <https://dba.stackexchange.com/questions/179744/order-by-too-slow-in-foreign-table-using-postgres-fdw>
> ).
>
> *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
>
>
>