Message-ID: <2167710abc29807eb5ba56e0ac8f3f23dad337a9.camel@cybertec.at>
Subject: Re: Oracle_FDW table performance issue
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: aditya desai <admad123@gmail.com>, Pgsql Performance
	 <pgsql-performance@lists.postgresql.org>
Date: Mon, 11 Jul 2022 17:26:30 +0200
In-Reply-To: 
 <CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
References: 
	<CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2167710abc29807eb5ba56e0ac8f3f23dad337a9.camel%40cybertec.at>
Precedence: bulk

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