Re: SQL performance issue after migration from Oracle to Aurora postgres

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