pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOracle_FDW table performance issue
5+ messages / 3 participants
[nested] [flat]
* Oracle_FDW table performance issue
@ 2022-07-11 12:08 aditya desai <admad123@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: aditya desai @ 2022-07-11 12:08 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
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?
Regards,
Aditya.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oracle_FDW table performance issue
@ 2022-07-11 12:13 Justin Pryzby <pryzby@telsasoft.com>
parent: aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2022-07-11 12:13 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote:
> Hi,
> 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.
You'd have to share the plan
https://wiki.postgresql.org/wiki/Slow_Query_Questions
Do the tables have updated stats ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oracle_FDW table performance issue
@ 2022-07-11 12:22 aditya desai <admad123@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 5+ messages in thread
From: aditya desai @ 2022-07-11 12:22 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>; shashissomashekar@gmail.com
Hi Justin,
Sorry unable to send a query plan from a closed network. Here the stats are
updated on the Oracle table.
It seems like when joining the local tables it is not filtering data on
Oracle and bringing data to postgres. It is filtering when we actually pass
the values.
Regards,
Aditya.
On Mon, Jul 11, 2022 at 5:43 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote:
> > Hi,
> > 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.
>
> You'd have to share the plan
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> Do the tables have updated stats ?
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oracle_FDW table performance issue
@ 2022-07-11 15:26 Laurenz Albe <laurenz.albe@cybertec.at>
parent: aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2022-07-11 15:26 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Oracle_FDW table performance issue
@ 2022-07-11 17:37 aditya desai <admad123@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: aditya desai @ 2022-07-11 17:37 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
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.albe@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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-07-11 17:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-11 12:08 Oracle_FDW table performance issue aditya desai <admad123@gmail.com>
2022-07-11 12:13 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-11 12:22 ` aditya desai <admad123@gmail.com>
2022-07-11 15:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-07-11 17:37 ` aditya desai <admad123@gmail.com>
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