Thread: SQL performance issue after migration from Oracle to Aurora postgres
Hi All,
We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in Oracle takes less than a millisecond however the same query in aurora is taking more than a second. We have a larger number of executions for the SQL which is causing an overall latency for the application. I am new to postgres and trying to get some ideas around how better we can optimize. I have the plan details for the SQL as below. Can someone shed some light on possible ways that can make this query to meet its original execution time?
Thanks,
Goti
On 2022-04-14 Th 05:35, Goti wrote: > Hi All, > > We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in > Oracle takes less than a millisecond however the same query in aurora > is taking more than a second. We have a larger number of executions > for the SQL which is causing an overall latency for the application. I > am new to postgres and trying to get some ideas around how better we > can optimize. I have the plan details for the SQL as below. Can > someone shed some light on possible ways that can make this query to > meet its original execution time? > > https://explain.depesz.com/s/jlVc#html > Without knowing much about your data I would suggest trying to rewrite the query to get rid of the correlated subselect, using a join instead. I note the use of both implicit and explicit joins in your FROM clause, which is something I always advise against, as it hurts clarity, but that's a matter of style rather than performance. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com