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
