pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSame query 10000x More Time
7+ messages / 3 participants
[nested] [flat]
* Same query 10000x More Time
@ 2022-01-06 07:43 Avi Weinberg <AviW@gilat.com>
0 siblings, 1 reply; 7+ messages in thread
From: Avi Weinberg @ 2022-01-06 07:43 UTC (permalink / raw)
To: pgsql-performance
Hi
I have postgres_fdw table called tbl_link. The source table is 2.5 GB in size with 122 lines (some lines has 70MB bytea column, but not the ones I select in the example)
I noticed that when I put the specific ids in the list "where id in (140,144,148)" it works fast (few ms), but when I put the same list as select "where id in (select 140 as id union select 144 union select 148)" it takes 50 seconds. This select union is just for the example, I obviously have a different select (which by itself takes few ms but cause the whole insert query to take 10000x more time)
Why is that? How can I still use regular select and still get reasonable response time?
Thanks
FAST:
select lnk.*
into local_1
from tbl_link lnk
where id in (140,144,148)
"Foreign Scan on tbl_link lnk (cost=100.00..111.61 rows=3 width=700) (actual time=4.161..4.167 rows=3 loops=1)"
"Planning Time: 0.213 ms"
"Execution Time: 16.251 ms"
SLOW:
select lnk.*
into local_1
from tbl_link lnk
where id in (select 140 as id union select 144 union select 148)
"Hash Join (cost=100.18..113.88 rows=3 width=700) (actual time=45398.721..46812.100 rows=3 loops=1)"
" Hash Cond: (lnk.id = (140))"
" -> Foreign Scan on tbl_link lnk (cost=100.00..113.39 rows=113 width=700) (actual time=45398.680..46812.026 rows=112 loops=1)"
" -> Hash (cost=0.14..0.14 rows=3 width=4) (actual time=0.023..0.026 rows=3 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> HashAggregate (cost=0.08..0.11 rows=3 width=4) (actual time=0.017..0.021 rows=3 loops=1)"
" Group Key: (140)"
" Batches: 1 Memory Usage: 24kB"
" -> Append (cost=0.00..0.07 rows=3 width=4) (actual time=0.005..0.009 rows=3 loops=1)"
" -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)"
" -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)"
" -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)"
"Planning Time: 0.541 ms"
"Execution Time: 46827.945 ms"
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Same query 10000x More Time
@ 2022-01-06 08:20 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Avi Weinberg <AviW@gilat.com>
0 siblings, 1 reply; 7+ messages in thread
From: Vijaykumar Jain @ 2022-01-06 08:20 UTC (permalink / raw)
To: Avi Weinberg <AviW@gilat.com>; +Cc: pgsql-performance
On Thu, 6 Jan 2022 at 13:13, Avi Weinberg <AviW@gilat.com> wrote:
> Hi
>
>
>
> I have postgres_fdw table called tbl_link. The source table is 2.5 GB in
> size with 122 lines (some lines has 70MB bytea column, but not the ones I
> select in the example)
>
> I noticed that when I put the specific ids in the list "where id in
> (140,144,148)" it works fast (few ms), but when I put the same list as
> select "where id in (select 140 as id union select 144 union select 148)"
> it takes 50 seconds. This select union is just for the example, I
> obviously have a different select (which by itself takes few ms but cause
> the whole insert query to take 10000x more time)
>
>
>
> Why is that? How can I still use regular select and still get reasonable
> response time?
>
>
>
> Thanks
>
>
>
couple of things:
PostgreSQL: Documentation: 14: F.35. postgres_fdw
<https://www.postgresql.org/docs/current/postgres-fdw.html;
<https://www.postgresql.org/docs/current/postgres-fdw.html>when you set
your foreign server what are your
use_remote_estimate
fetch_size
params for the foreign server.
you need to know there are certain restrictions on what gets pushed down to
the remote server
i generally use postgres/postgres_fdw.sql at master · postgres/postgres
(github.com)
<https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/sql/postgres_fdw.sql;
as
a reference
if you predicates are not pushed down, it will bring all the rows from the
foreign server to your local server (and fetch_size value and network io
will add to delay)
and given you used select * , it will be a lot of io, so maybe restrict
only to columns needed after being filtered would help.
you can try by running
explain (verbose,analyze) query and then also enabling log_statement =
'all' / log_min_duration_statement = 0
on the foreign server to see the actual plan for the foreign scan.
That might help in trouble shooting.
as always, i have little production exposure. If i am wrong, i can be
corrected.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Same query 10000x More Time
@ 2022-01-06 09:39 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kyotaro Horiguchi @ 2022-01-06 09:39 UTC (permalink / raw)
To: vijaykumarjain.github@gmail.com; +Cc: AviW@gilat.com; pgsql-performance
At Thu, 6 Jan 2022 13:50:55 +0530, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote in
> On Thu, 6 Jan 2022 at 13:13, Avi Weinberg <AviW@gilat.com> wrote:
>
> > Hi
> >
> >
> >
> > I have postgres_fdw table called tbl_link. The source table is 2.5 GB in
> > size with 122 lines (some lines has 70MB bytea column, but not the ones I
> > select in the example)
> >
> > I noticed that when I put the specific ids in the list "where id in
> > (140,144,148)" it works fast (few ms), but when I put the same list as
> > select "where id in (select 140 as id union select 144 union select 148)"
> > it takes 50 seconds. This select union is just for the example, I
> > obviously have a different select (which by itself takes few ms but cause
> > the whole insert query to take 10000x more time)
> >
> >
> >
> > Why is that? How can I still use regular select and still get reasonable
> > response time?
> >
> >
> >
> > Thanks
> >
> >
> >
>
> couple of things:
> PostgreSQL: Documentation: 14: F.35. postgres_fdw
> <https://www.postgresql.org/docs/current/postgres-fdw.html;
> <https://www.postgresql.org/docs/current/postgres-fdw.html>when you set
> your foreign server what are your
> use_remote_estimate
> fetch_size
> params for the foreign server.
>
> you need to know there are certain restrictions on what gets pushed down to
> the remote server
> i generally use postgres/postgres_fdw.sql at master · postgres/postgres
> (github.com)
> <https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/sql/postgres_fdw.sql;
> as
> a reference
> if you predicates are not pushed down, it will bring all the rows from the
> foreign server to your local server (and fetch_size value and network io
> will add to delay)
> and given you used select * , it will be a lot of io, so maybe restrict
> only to columns needed after being filtered would help.
>
>
> you can try by running
> explain (verbose,analyze) query and then also enabling log_statement =
> 'all' / log_min_duration_statement = 0
> on the foreign server to see the actual plan for the foreign scan.
>
> That might help in trouble shooting.
>
>
> as always, i have little production exposure. If i am wrong, i can be
> corrected.
In this specific case, the FAST query doesn't contain a join and its
predicate can be pushed down to remote. On the other hand the SLOW
one contains a join. The planner considers remote join only when the
both hands of a join are on the same foreign server. Tthis is not the
case since the inner subquery is not even a foreign scan. The planner
doesn't consider the possibility that a subquery is executable
anywhere.
As the result, the local inevitably draw all rows from remote table to
join with the result of the subquery on-local, which should be quite
slow.
It could be improved, but I don't think we are going to consider that
case because the SLOW query seems like a kind of bad query, which can
be improved by rewriting to the FAST one.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Same query 10000x More Time
@ 2022-01-06 10:20 Avi Weinberg <AviW@gilat.com>
parent: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Avi Weinberg @ 2022-01-06 10:20 UTC (permalink / raw)
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; vijaykumarjain.github@gmail.com <vijaykumarjain.github@gmail.com>; +Cc: pgsql-performance
Hi Kyotaro Horiguchi and Vijaykumar Jain,
Thanks for your quick reply!
I understand that the fact the slow query has a join caused this problem. However, why can't Postgres evaluate the table of the "IN" clause (select 140 as id union select 144 union select 148) and based on its size decide what is more optimal.
Push the local table to the linked server to perform the join on the linked server
Pull the linked server table to local to perform the join on the local.
In my case the table size of the local is million times smaller than the table size of the remote.
select lnk.*
into local_1
from tbl_link lnk
where id in (select 140 as id union select 144 union select 148)
-----Original Message-----
From: Kyotaro Horiguchi [mailto:horikyota.ntt@gmail.com]
Sent: Thursday, January 6, 2022 11:39 AM
To: vijaykumarjain.github@gmail.com
Cc: Avi Weinberg <AviW@gilat.com>; pgsql-performance@postgresql.org
Subject: Re: Same query 10000x More Time
At Thu, 6 Jan 2022 13:50:55 +0530, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote in
> On Thu, 6 Jan 2022 at 13:13, Avi Weinberg <AviW@gilat.com> wrote:
>
> > Hi
> >
> >
> >
> > I have postgres_fdw table called tbl_link. The source table is 2.5
> > GB in size with 122 lines (some lines has 70MB bytea column, but not
> > the ones I select in the example)
> >
> > I noticed that when I put the specific ids in the list "where id in
> > (140,144,148)" it works fast (few ms), but when I put the same list
> > as select "where id in (select 140 as id union select 144 union select 148)"
> > it takes 50 seconds. This select union is just for the example, I
> > obviously have a different select (which by itself takes few ms but
> > cause the whole insert query to take 10000x more time)
> >
> >
> >
> > Why is that? How can I still use regular select and still get
> > reasonable response time?
> >
> >
> >
> > Thanks
> >
> >
> >
>
> couple of things:
> PostgreSQL: Documentation: 14: F.35. postgres_fdw
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww
> .postgresql.org%2Fdocs%2Fcurrent%2Fpostgres-fdw.html&data=04%7C01%
> 7Caviw%40gilat.com%7Cc8585d2ddbeb4a09e3e208d9d0f8684c%7C7300b1a3573a40
> 1092a61c65cd85e927%7C0%7C0%7C637770587595033327%7CUnknown%7CTWFpbGZsb3
> d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7
> C3000&sdata=bVBCIOkXrVkkI%2BDH44QmAZmm%2FJLz%2FWYp5Wda%2FrJRfDA%3D
> &reserved=0>
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww
> .postgresql.org%2Fdocs%2Fcurrent%2Fpostgres-fdw.html&data=04%7C01%
> 7Caviw%40gilat.com%7Cc8585d2ddbeb4a09e3e208d9d0f8684c%7C7300b1a3573a40
> 1092a61c65cd85e927%7C0%7C0%7C637770587595033327%7CUnknown%7CTWFpbGZsb3
> d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=bVBCIOkXrVkkI%2BDH44QmAZmm%2FJLz%2FWYp5Wda%2FrJRfDA%3D&reserved=0>when you set your foreign server what are your use_remote_estimate fetch_size params for the foreign server.
>
> you need to know there are certain restrictions on what gets pushed
> down to the remote server i generally use postgres/postgres_fdw.sql at
> master * postgres/postgres
> (github.com)
> <https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgit
> hub.com%2Fpostgres%2Fpostgres%2Fblob%2Fmaster%2Fcontrib%2Fpostgres_fdw
> %2Fsql%2Fpostgres_fdw.sql&data=04%7C01%7Caviw%40gilat.com%7Cc8585d
> 2ddbeb4a09e3e208d9d0f8684c%7C7300b1a3573a401092a61c65cd85e927%7C0%7C0%
> 7C637770587595033327%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQI
> joiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=TzqeuCMrThZ
> RUkq9m%2F97N8bRgm9wu3VFjTnoZpt%2BA7w%3D&reserved=0>
> as
> a reference
> if you predicates are not pushed down, it will bring all the rows from
> the foreign server to your local server (and fetch_size value and
> network io will add to delay) and given you used select * , it will be
> a lot of io, so maybe restrict only to columns needed after being
> filtered would help.
>
>
> you can try by running
> explain (verbose,analyze) query and then also enabling log_statement
> = 'all' / log_min_duration_statement = 0 on the foreign server to see
> the actual plan for the foreign scan.
>
> That might help in trouble shooting.
>
>
> as always, i have little production exposure. If i am wrong, i can be
> corrected.
In this specific case, the FAST query doesn't contain a join and its predicate can be pushed down to remote. On the other hand the SLOW one contains a join. The planner considers remote join only when the both hands of a join are on the same foreign server. Tthis is not the case since the inner subquery is not even a foreign scan. The planner doesn't consider the possibility that a subquery is executable anywhere.
As the result, the local inevitably draw all rows from remote table to join with the result of the subquery on-local, which should be quite slow.
It could be improved, but I don't think we are going to consider that case because the SLOW query seems like a kind of bad query, which can be improved by rewriting to the FAST one.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Same query 10000x More Time
@ 2022-01-06 12:52 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Avi Weinberg <AviW@gilat.com>
0 siblings, 1 reply; 7+ messages in thread
From: Vijaykumar Jain @ 2022-01-06 12:52 UTC (permalink / raw)
To: Avi Weinberg <AviW@gilat.com>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-performance
On Thu, Jan 6, 2022, 3:50 PM Avi Weinberg <AviW@gilat.com> wrote:
> Hi Kyotaro Horiguchi and Vijaykumar Jain,
>
> Thanks for your quick reply!
>
> I understand that the fact the slow query has a join caused this problem.
> However, why can't Postgres evaluate the table of the "IN" clause (select
> 140 as id union select 144 union select 148) and based on its size decide
> what is more optimal.
> Push the local table to the linked server to perform the join on the
> linked server
> Pull the linked server table to local to perform the join on the local.
>
> In my case the table size of the local is million times smaller than the
> table size of the remote.
I understand when the optimizer makes a decision it uses stats to use the
least expensive plan to get the result.
I can reply but I am pretty sure making an analogy to a local setup of big
and small table is not the same as small local table and a big remote table.
I would leave it to the experts here unless you are open to read the src
for postgres_fdw extension.
https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/postgres_fdw.c
There must be a reason if that is beyond cost calculation as to why this
happens.
Else if this is all just cost based, you can try tweaking the cost params
and see if you can get a better plan.
For exp, if you force parallel cost to 0 on the foreign server, it may use
parallel workers and do some speed up, but given my exp, fighting optimizer
is mostly asking for trouble :)
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Same query 10000x More Time
@ 2022-01-06 14:31 Avi Weinberg <AviW@gilat.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Avi Weinberg @ 2022-01-06 14:31 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-performance
Thanks for the input
postgres_fdw seems to bring the entire table even if all I use in the join is just the id from the remote table. I know it is possible to query for the missing ids and then perform the delete, but I wonder why all types of joins are so inefficient.
DELETE FROM tbl_local lcl
WHERE NOT EXISTS (
SELECT id FROM tbl_link lnk
WHERE lnk.id = lcl.id );
"Delete on tbl_local lcl (cost=114.59..122.14 rows=3 width=730) (actual time=62153.636..62153.639 rows=0 loops=1)"
" -> Hash Anti Join (cost=114.59..122.14 rows=3 width=730) (actual time=62153.633..62153.636 rows=0 loops=1)"
" Hash Cond: (lcl.id = lnk.id)"
" -> Seq Scan on tbl_local lcl (cost=0.00..7.11 rows=111 width=14) (actual time=0.022..0.062 rows=111 loops=1)"
" -> Hash (cost=113.24..113.24 rows=108 width=732) (actual time=55984.489..55984.490 rows=112 loops=1)"
" Buckets: 1024 (originally 1024) Batches: 32 (originally 1) Memory Usage: 240024kB"
" -> Foreign Scan on tbl_link lnk (cost=100.00..113.24 rows=108 width=732) (actual time=48505.926..51893.668 rows=112 loops=1)"
"Planning Time: 0.237 ms"
"Execution Time: 62184.253 ms"
From: Vijaykumar Jain [mailto:vijaykumarjain.github@gmail.com]
Sent: Thursday, January 6, 2022 2:53 PM
To: Avi Weinberg <AviW@gilat.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-performa. <pgsql-performance@postgresql.org>
Subject: Re: Same query 10000x More Time
On Thu, Jan 6, 2022, 3:50 PM Avi Weinberg <AviW@gilat.com<mailto:AviW@gilat.com>> wrote:
Hi Kyotaro Horiguchi and Vijaykumar Jain,
Thanks for your quick reply!
I understand that the fact the slow query has a join caused this problem. However, why can't Postgres evaluate the table of the "IN" clause (select 140 as id union select 144 union select 148) and based on its size decide what is more optimal.
Push the local table to the linked server to perform the join on the linked server
Pull the linked server table to local to perform the join on the local.
In my case the table size of the local is million times smaller than the table size of the remote.
I understand when the optimizer makes a decision it uses stats to use the least expensive plan to get the result.
I can reply but I am pretty sure making an analogy to a local setup of big and small table is not the same as small local table and a big remote table.
I would leave it to the experts here unless you are open to read the src for postgres_fdw extension.
https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/postgres_fdw.c<https://eur0...;
There must be a reason if that is beyond cost calculation as to why this happens.
Else if this is all just cost based, you can try tweaking the cost params and see if you can get a better plan.
For exp, if you force parallel cost to 0 on the foreign server, it may use parallel workers and do some speed up, but given my exp, fighting optimizer is mostly asking for trouble :)
IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Same query 10000x More Time
@ 2022-01-06 16:50 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Avi Weinberg <AviW@gilat.com>
0 siblings, 0 replies; 7+ messages in thread
From: Vijaykumar Jain @ 2022-01-06 16:50 UTC (permalink / raw)
To: Avi Weinberg <AviW@gilat.com>; +Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>; pgsql-performance
On Thu, 6 Jan 2022 at 20:01, Avi Weinberg <AviW@gilat.com> wrote:
> Thanks for the input
>
>
>
> postgres_fdw seems to bring the entire table even if all I use in the join
> is just the id from the remote table. I know it is possible to query for
> the missing ids and then perform the delete, but I wonder why all types of
> joins are so inefficient.
>
>
>
just for fun, please do not do this.
I tried out multiple options where we join a small local table to a huge
remote table with multiple plan skip settings.
postgres@db:~/playground$ psql
psql (14beta1)
Type "help" for help.
postgres=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=# \x
Expanded display is on.
localdb=# table pg_foreign_server;
-[ RECORD 1
]-----------------------------------------------------------------------------------------------
oid | 85462
srvname | remote_server
srvowner | 10
srvfdw | 85458
srvtype |
srvversion |
srvacl |
srvoptions |
{dbname=remotedb,use_remote_estimate=true,fdw_startup_cost=0,fdw_tuple_cost=0,fetch_size=10000}
localdb=# \x
Expanded display is off.
localdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
(1 row)
localdb=# \det remote_schema.remote_table;
List of foreign tables
Schema | Table | Server
---------------+--------------+---------------
remote_schema | remote_table | remote_server
(1 row)
localdb=# \c remotedb;
You are now connected to database "remotedb" as user "postgres".
remotedb=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | remote_table | table | postgres
(1 row)
remotedb=# select count(1) from remote_table;
count
--------
100000
(1 row)
remotedb=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=# select count(1) from t;
count
-------
10
(1 row)
*# all the set options are forcing the optmizer to skip that plan route*
localdb=# explain (analyze, verbose) select * from t join
remote_schema.remote_table r on (t.t_id = r.t_id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.23..2817.97 rows=100000 width=16) (actual
time=5.814..63.310 rows=90000 loops=1)
Output: t.t_id, t.t_col, r.rt_id, r.t_id
Inner Unique: true
Hash Cond: (r.t_id = t.t_id)
-> Foreign Scan on remote_schema.remote_table r (cost=0.00..2443.00
rows=100000 width=8) (actual time=5.797..47.329 rows=100000 loops=1)
Output: r.rt_id, r.t_id
*Remote SQL: SELECT rt_id, t_id FROM public.remote_table*
-> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.009..0.010
rows=10 loops=1)
Output: t.t_id, t.t_col
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on public.t (cost=0.00..1.10 rows=10 width=8)
(actual time=0.005..0.006 rows=10 loops=1)
Output: t.t_id, t.t_col
Planning Time: 4.464 ms
Execution Time: 65.995 ms
(14 rows)
localdb=# set enable_seqscan TO 0;
SET
localdb=# explain (analyze, verbose) select * from t join
remote_schema.remote_table r on (t.t_id = r.t_id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=12.41..2829.16 rows=100000 width=16) (actual
time=5.380..61.028 rows=90000 loops=1)
Output: t.t_id, t.t_col, r.rt_id, r.t_id
Inner Unique: true
Hash Cond: (r.t_id = t.t_id)
-> Foreign Scan on remote_schema.remote_table r (cost=0.00..2443.00
rows=100000 width=8) (actual time=5.362..45.625 rows=100000 loops=1)
Output: r.rt_id, r.t_id
*Remote SQL: SELECT rt_id, t_id FROM public.remote_table*
-> Hash (cost=12.29..12.29 rows=10 width=8) (actual time=0.011..0.011
rows=10 loops=1)
Output: t.t_id, t.t_col
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using t_pkey on public.t (cost=0.14..12.29 rows=10
width=8) (actual time=0.005..0.008 rows=10 loops=1)
Output: t.t_id, t.t_col
Planning Time: 0.696 ms
Execution Time: 63.666 ms
(14 rows)
localdb=# set enable_hashjoin TO 0;
SET
localdb=# explain (analyze, verbose) select * from t join
remote_schema.remote_table r on (t.t_id = r.t_id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.15..4821.93 rows=100000 width=16) (actual
time=5.199..75.817 rows=90000 loops=1)
Output: t.t_id, t.t_col, r.rt_id, r.t_id
Inner Unique: true
-> Foreign Scan on remote_schema.remote_table r (cost=0.00..2443.00
rows=100000 width=8) (actual time=5.186..46.152 rows=100000 loops=1)
Output: r.rt_id, r.t_id
*Remote SQL: SELECT rt_id, t_id FROM public.remote_table*
-> Result Cache (cost=0.15..0.16 rows=1 width=8) (actual
time=0.000..0.000 rows=1 loops=100000)
Output: t.t_id, t.t_col
Cache Key: r.t_id
Hits: 99990 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage:
2kB
-> Index Scan using t_pkey on public.t (cost=0.14..0.15 rows=1
width=8) (actual time=0.001..0.001 rows=1 loops=10)
Output: t.t_id, t.t_col
Index Cond: (t.t_id = r.t_id)
Planning Time: 0.692 ms
Execution Time: 78.512 ms
(15 rows)
localdb=# set enable_resultcache TO 0;
SET
localdb=# explain (analyze, verbose) select * from t join
remote_schema.remote_table r on (t.t_id = r.t_id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=479.10..5847.98 rows=100000 width=16) (actual
time=12.855..66.094 rows=90000 loops=1)
Output: t.t_id, t.t_col, r.rt_id, r.t_id
Inner Unique: true
Merge Cond: (r.t_id = t.t_id)
-> Foreign Scan on remote_schema.remote_table r (cost=0.29..4586.89
rows=100000 width=8) (actual time=6.235..55.329 rows=100000 loops=1)
Output: r.rt_id, r.t_id
*Remote SQL: SELECT rt_id, t_id FROM public.remote_table ORDER BY
t_id ASC NULLS LAST*
-> Index Scan using t_pkey on public.t (cost=0.14..12.29 rows=10
width=8) (actual time=0.006..0.024 rows=9 loops=1)
Output: t.t_id, t.t_col
Planning Time: 0.704 ms
Execution Time: 68.724 ms
(11 rows)
localdb=# set enable_mergejoin TO 0;
SET
localdb=# explain (analyze, verbose) select * from t join
remote_schema.remote_table r on (t.t_id = r.t_id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=113.94..8830.28 rows=100000 width=16) (actual
time=11.576..89.465 rows=90000 loops=1)
Output: t.t_id, t.t_col, r.rt_id, r.t_id
-> Index Scan using t_pkey on public.t (cost=0.14..12.29 rows=10
width=8) (actual time=0.009..0.034 rows=10 loops=1)
Output: t.t_id, t.t_col
-> Foreign Scan on remote_schema.remote_table r (cost=113.80..781.80
rows=10000 width=8) (actual time=7.648..8.108 rows=9000 loops=10)
Output: r.rt_id, r.t_id
*Remote SQL: SELECT rt_id, t_id FROM public.remote_table WHERE
(($1::integer = t_id))*
Planning Time: 0.667 ms
Execution Time: 92.131 ms
(9 rows)
from the logs for the last case: (it has open a new cursor everytime for
each matching id) and is still the *slowest.*
2022-01-06 22:10:48.665 IST [2318] LOG: execute <unnamed>: DECLARE c1
CURSOR FOR
SELECT rt_id, t_id FROM public.remote_table WHERE (($1::integer = t_id))
2022-01-06 22:10:48.665 IST [2318] DETAIL: parameters: $1 = '1'
2022-01-06 22:10:48.665 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.679 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.679 IST [2318] LOG: statement: CLOSE c1
2022-01-06 22:10:48.679 IST [2318] LOG: execute <unnamed>: DECLARE c1
CURSOR FOR
SELECT rt_id, t_id FROM public.remote_table WHERE (($1::integer = t_id))
2022-01-06 22:10:48.679 IST [2318] DETAIL: parameters: $1 = '2'
2022-01-06 22:10:48.679 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.686 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.687 IST [2318] LOG: statement: CLOSE c1
2022-01-06 22:10:48.687 IST [2318] LOG: execute <unnamed>: DECLARE c1
CURSOR FOR
SELECT rt_id, t_id FROM public.remote_table WHERE (($1::integer = t_id))
2022-01-06 22:10:48.687 IST [2318] DETAIL: parameters: $1 = '3'
2022-01-06 22:10:48.687 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.698 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.698 IST [2318] LOG: statement: CLOSE c1
2022-01-06 22:10:48.698 IST [2318] LOG: execute <unnamed>: DECLARE c1
CURSOR FOR
SELECT rt_id, t_id FROM public.remote_table WHERE (($1::integer = t_id))
2022-01-06 22:10:48.698 IST [2318] DETAIL: parameters: $1 = '4'
2022-01-06 22:10:48.698 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.705 IST [2318] LOG: statement: FETCH 10000 FROM c1
2022-01-06 22:10:48.705 IST [2318] LOG: statement: CLOSE c1
so i think, i just trust the optimizer, or rewrite my so as to gather the
predicate first locally and then pass them to remote, or use
materialized views to maintain
a stale copy of the remote table on my local db etc.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-01-06 16:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-06 07:43 Same query 10000x More Time Avi Weinberg <AviW@gilat.com>
2022-01-06 08:20 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2022-01-06 09:39 ` Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2022-01-06 10:20 ` Avi Weinberg <AviW@gilat.com>
2022-01-06 12:52 ` Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2022-01-06 14:31 ` Avi Weinberg <AviW@gilat.com>
2022-01-06 16:50 ` Vijaykumar Jain <vijaykumarjain.github@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