From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
To: vijaykumarjain.github@gmail.com
Cc: AviW@gilat.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Same query 10000x More Time
Date: Thu, 06 Jan 2022 18:39:08 +0900 (JST)
Message-ID: <20220106.183908.758244498024205160.horikyota.ntt@gmail.com> (raw)
In-Reply-To: <CAM+6J96xK1Wu18=MSLkekA_QKhCxpEtk8yPw2uknVd9+kz0WZQ@mail.gmail.com>
References: <DB9PR07MB7180C5E73400BB58F649843FCB4C9@DB9PR07MB7180.eurprd07.prod.outlook.com>
<CAM+6J96xK1Wu18=MSLkekA_QKhCxpEtk8yPw2uknVd9+kz0WZQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: horikyota.ntt@gmail.com, vijaykumarjain.github@gmail.com, AviW@gilat.com
Subject: Re: Same query 10000x More Time
In-Reply-To: <20220106.183908.758244498024205160.horikyota.ntt@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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