From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | aditya desai <admad123(at)gmail(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Oracle_FDW table performance issue |
Date: | 2022-07-11 15:26:30 |
Message-ID: | 2167710abc29807eb5ba56e0ac8f3f23dad337a9.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | aditya desai | 2022-07-11 17:37:21 | Re: Oracle_FDW table performance issue |
Previous Message | aditya desai | 2022-07-11 12:22:40 | Re: Oracle_FDW table performance issue |