From: Laurenz Albe <laurenz.albe@cybertec.at>
To: aditya desai <admad123@gmail.com>
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Oracle_FDW table performance issue
Date: Mon, 11 Jul 2022 17:26:30 +0200
Message-ID: <2167710abc29807eb5ba56e0ac8f3f23dad337a9.camel@cybertec.at> (raw)
In-Reply-To: <CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
References: <CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
On Mon, 2022-07-11 at 17:38 +0530, aditya desai wrote:
> I have one Oracle fdw table which is giving performance issue when joined
> local temp table gives performance issue.
>
> select * from oracle_fdw_table where transaction_id in ( select transaction_id from temp_table)
> ---- 54 seconds. Seeing HASH SEMI JOIN in EXPLAIN PLAN. temp_table has only 74 records.
>
> select * from from oracle_fdw_table where transaction_id in ( 1,2,3,.....,75)--- 23ms.
>
> Could you please help me understand this drastic behaviour change?
The first query joins a local table with a remote Oracle table. The only way for
such a join to avoid fetching the whole Oracle table would be to have the foreign scan
on the inner side of a nested loop join. But that would incur many round trips to Oracle
and is therefore perhaps not a great plan either.
In the second case, the whole IN list is shipped to the remote side.
In short, the queries are quite different, and I don't think it is possible to get
the first query to perform as well as the second.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, admad123@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Oracle_FDW table performance issue
In-Reply-To: <2167710abc29807eb5ba56e0ac8f3f23dad337a9.camel@cybertec.at>
* 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