Understood thanks!! Will try to build dynamiq query to send ids across instead of join.
On Mon, Jul 11, 2022 at 8:56 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > 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 >
