[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] ORDER BY too slow in Foreign Table using postgres_fdw
From: Luan Huynh <nnhluan () gmail ! com>
Date: 2017-07-19 2:32:08
Message-ID: CAAP3KKFKeq3NUPuYhDSyqaaLcsKMLDn=Qx5_XQSuzWNXa5JD2g () mail ! gmail ! com
[Download RAW message or body]
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
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr">Thank your help, <span \
style="font-size:12.8px">Samed.</span><div><span style="font-size:12.8px">That idea \
is great.</span></div><div><span style="font-size:12.8px"><br></span></div><div><span \
style="font-size:12.8px">After apply your idea, <b>for testing</b>, I try to add \
some columns in the remote table, then try to filter them. As a result, </span><span \
style="font-size:12.8px">the query will slow again (due to order \
by).</span></div><div><br></div></div><div class="gmail_extra"><br><div \
class="gmail_quote">On Tue, Jul 18, 2017 at 7:38 PM, Samed YILDIRIM <span \
dir="ltr"><<a href="mailto:samed@reddoc.net" \
target="_blank">samed@reddoc.net</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><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> \
<wbr> QUERY PLAN \
<wbr> <wbr> \
</div><div>------------------------------<wbr>------------------------------<wbr>----- \
-------------------------<wbr>------------------------------<wbr>---------</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><span class=""><div> \
Sort Key: id</div><div> Sort Method: top-N heapsort Memory: \
26kB</div></span><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><span class=""><div> Filter: ((info \
->> 'key1'::text) = '1'::text)</div></span><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> \
<wbr> QUERY PLAN \
<wbr> \
</div><div>------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>-------------------------</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><span class=""><div> \
Filter: ((info ->> 'key1'::text) = '1'::text)</div></span><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>----+-------------------------<wbr>---</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" <<a href="mailto:nnhluan@gmail.com" \
target="_blank">nnhluan@gmail.com</a>>:</div><div class="HOEnZb"><div \
class="h5"><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" \
target="_blank">here's 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>.<wbr>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>.<wbr>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></div></div></blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic