Re: Oracle_FDW table performance issue

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(dot)albe(at)cybertec(dot)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
>