pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY too slow in Foreign Table using postgres_fdw
3+ messages / 2 participants
[nested] [flat]
* ORDER BY too slow in Foreign Table using postgres_fdw
@ 2017-07-18 07:04 Luan Huynh <nnhluan@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Luan Huynh @ 2017-07-18 07:04 UTC (permalink / raw)
To: pgsql-performance-owner@postgresql.org; pgsql-sql-owner@postgresql.org; 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
<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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY too slow in Foreign Table using postgres_fdw
@ 2017-07-18 12:38 Samed YILDIRIM <samed@reddoc.net>
parent: Luan Huynh <nnhluan@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Samed YILDIRIM @ 2017-07-18 12:38 UTC (permalink / raw)
To: Luan Huynh <nnhluan@gmail.com>; pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>; pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>; pgsql-admin
<div>Hi Luan,</div><div> </div><div>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.</div><div> </div><div><strong>On remote Postgres</strong></div><div>create view user_info_vw as select id,info from user_info order by id;</div><div> </div><div><strong>On local Postgres</strong></div><div>create foreign table user_info (id bigint, info jsonb) server luan_server options (schema_name 'public', table_name 'user_info_vw');</div><div> </div><div> </div><div><u><em>Before view</em></u></div><div><div>postgres=# explain analyze SELECT id, info</div><div>FROM user_info</div><div>WHERE info ->> 'key1'= '1' order by id limit 10;</div><div> QUERY PLAN </div><div>---------------------------------------------------------------------------------------------------------------------------------</div><div> Limit (cost=153.28..153.29 rows=6 width=40) (actual time=7512.755..7512.759 rows=10 loops=1)</div><div> -> Sort (cost=153.28..153.29 rows=6 width=40) (actual time=7512.754..7512.757 rows=10 loops=1)</div><div> Sort Key: id</div><div> Sort Method: top-N heapsort Memory: 26kB</div><div> -> Foreign Scan on user_info (cost=100.00..153.20 rows=6 width=40) (actual time=0.962..7351.989 rows=1187840 loops=1)</div><div> Filter: ((info ->> 'key1'::text) = '1'::text)</div><div> Rows Removed by Filter: 786432</div><div> Planning time: 0.089 ms</div><div><strong> Execution time: 7513.322 ms</strong></div><div>(9 rows)</div><div> </div><div> </div></div><div><u><em>After view</em></u></div><div><div>explain analyze SELECT id, info</div><div>FROM user_info</div><div>WHERE info ->> 'key1'= '1' LIMIT 10;</div><div> QUERY PLAN </div><div>-------------------------------------------------------------------------------------------------------------------</div><div> Limit (cost=100.00..153.20 rows=6 width=40) (actual time=0.678..0.684 rows=10 loops=1)</div><div> -> Foreign Scan on user_info (cost=100.00..153.20 rows=6 width=40) (actual time=0.677..0.681 rows=10 loops=1)</div><div> Filter: ((info ->> 'key1'::text) = '1'::text)</div><div> Rows Removed by Filter: 4</div><div> Planning time: 0.060 ms</div><div><strong> Execution time: 1.167 ms</strong></div><div>(6 rows)</div></div><div> </div><div><div>postgres=# SELECT id, info</div><div>postgres-# FROM user_info</div><div>postgres-# WHERE info ->> 'key1'= '1' LIMIT 10;</div><div> id | info </div><div>----+----------------------------</div><div> 1 | {"key1": 1, "key2": 0.678}</div><div> 2 | {"key1": 1, "key2": 0.678}</div><div> 3 | {"key1": 1, "key2": 1.0}</div><div> 4 | {"key1": 1, "key2": 0.986}</div><div> 7 | {"key1": 1, "key2": 0.678}</div><div> 8 | {"key1": 1, "key2": 1.0}</div><div> 9 | {"key1": 1, "key2": 0.986}</div><div> 12 | {"key1": 1, "key2": 0.678}</div><div> 13 | {"key1": 1, "key2": 1.0}</div><div> 14 | {"key1": 1, "key2": 0.986}</div><div>(10 rows)</div></div><div> </div><div>Best regards.</div><div><br /></div><div><br /></div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div><br /></div><div><br /></div><div><br /></div><div>18.07.2017, 10:06, "Luan Huynh" <nnhluan@gmail.com>:</div><blockquote type="cite"><div dir="ltr"><div>Hi all, </div><div><br /></div><div>On PostgreSQL <b>v.9.6, </b>when using <b>postgres_fdw</b>, I got an issue with "<b><i>ORDER BY</i></b>" (<a href="https://dba.stackexchange.com/questions/179744/order-by-too-slow-in-foreign-table-using-postgres-fdw... my question on stackexchange</a> ).</div><div><br /></div><div><b>Query on Foreign Table</b></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px;"><div><font face="tahoma, sans-serif">SELECT id, info </font></div><div><div><font face="tahoma, sans-serif">FROM user_info</font></div></div><div><div><font face="tahoma, sans-serif">WHERE info ->> 'key1'= '1' </font></div></div><div><div><font face="tahoma, sans-serif">ORDER BY id </font></div></div><div><div><font face="tahoma, sans-serif">LIMIT 10; </font></div></div><div><div><font face="tahoma, sans-serif"><br /></font></div></div><div><div><font face="tahoma, sans-serif">Limit (cost=<span>10750829</span>.63..<span>10750829</span>.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)</font></div></div><div><div><font face="tahoma, sans-serif"> -> Sort (cost=<span>10750829</span>.63..<span>10751772</span>.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)</font></div></div><div><div><font face="tahoma, sans-serif"> Sort Key: id</font></div></div><div><div><font face="tahoma, sans-serif"> Sort Method: top-N heapsort Memory: 26kB</font></div></div><div><div><font face="tahoma, sans-serif"> -> Foreign Scan on user_info (cost=100.00..<span>10742677</span>.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=<span>68281020</span> loops=1)</font></div></div><div><div><font face="tahoma, sans-serif"> Filter: ((info ->> 'key1'::text) = '1'::text)</font></div></div><div><div><font face="tahoma, sans-serif"> Rows Removed by Filter: <span>7170443</span></font></div></div><div><div><font face="tahoma, sans-serif"> Planning time: 4.097 ms</font></div></div><div><div><font face="tahoma, sans-serif"> <i>Execution time: 550059.597 ms</i></font></div></div></blockquote><div><br /></div><div><b>Query on remote server</b></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px;"><div><div><font face="tahoma, sans-serif">EXPLAIN ANALYSE</font></div></div><div><div><font face="tahoma, sans-serif">SELECT id, info </font></div></div><div><div><font face="tahoma, sans-serif">FROM user_info_raw</font></div></div><div><div><font face="tahoma, sans-serif">WHERE info ->> 'key1'= '1'</font></div></div><div><div><font face="tahoma, sans-serif">ORDER BY id </font></div></div><div><div><font face="tahoma, sans-serif">LIMIT 10;</font></div></div><div><div><font face="tahoma, sans-serif"><br /></font></div></div><div><div><font face="tahoma, sans-serif"> Limit (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)</font></div></div><div><div><font face="tahoma, sans-serif"> -> Index Scan using idx_user_info_raw_info on user_info_raw (cost=0.57..<span>68882850</span>.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)</font></div></div><div><div><font face="tahoma, sans-serif"> Filter: ((info ->> 'key1'::text) = '1'::text)</font></div></div><div><div><font face="tahoma, sans-serif"> Planning time: 0.192 ms</font></div></div><div><div><font face="tahoma, sans-serif"><i> Execution time: 0.102 ms </i></font></div></div></blockquote><div><br /></div><div><br /></div><div>Please help me to figure out the solution for that issue .<br /></div><div><br /></div><div>Thank you<br /></div><div> </div><div><br /></div></div>
</blockquote>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY too slow in Foreign Table using postgres_fdw
@ 2017-07-19 02:32 Luan Huynh <nnhluan@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 3+ messages in thread
From: Luan Huynh @ 2017-07-19 02:32 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org>; pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org>; pgsql-admin
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
> <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
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-07-19 02:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-18 07:04 ORDER BY too slow in Foreign Table using postgres_fdw Luan Huynh <nnhluan@gmail.com>
2017-07-18 12:38 ` Samed YILDIRIM <samed@reddoc.net>
2017-07-19 02:32 ` Luan Huynh <nnhluan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox