Re: Query with correlated join having slow performance

From: saket bansal <saket(dot)tcs(at)gmail(dot)com>
To: Michael Lewis <mlewis(at)entrata(dot)com>
Cc: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Query with correlated join having slow performance
Date: 2019-12-09 20:04:54
Message-ID: CACkcRNjenhgKehxktkc8-Fw7dPOL9qqQdxB+MbEE76aYRA-jVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Thank you Michael. I re-wrote it and it does perform well. Modified query
at:

https://github.com/bansalsaket/PG_correlated_subquery_slowness/blob/master/Modified%20query%20-%20performs%20faster.txt

Our app team is checking with their vendor whether this can be modified at
source code level or not.
But question remains somewhat valid. Data volume is not huge and original
query wasn't very badly written either. Operating system level resources
are similar.
Do you know of any bugs associated with using co-related sub queries in
postgres. In Oracle, it runs in a sec, while in postgres it does not give
result at all , even after 8-9 hours.
I understand both database engines work differently, but such drastic
change in performance is a surprise!
We have lot of migrations planned from oracle to postgres, this could be a
show stopper. :(
Any suggestions...

On Mon, Dec 9, 2019 at 1:49 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> I'd suggest re-writing your query to avoid ORs whenever possible. Is this
> generated by an ORM or subject to change with filters selected in
> application or can you totally control it on DB side?
>
> It may be hugely more performant to simply rewrite this as (almost) the
> same query twice UNION ALL'd together to separate the
> productalt1_.alt_pdt_dbky OR productalt1_.orgnl_pdt_dbky conditions.
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2019-12-09 20:14:32 Re: Query with correlated join having slow performance
Previous Message saket bansal 2019-12-09 19:54:59 Re: Query with correlated join having slow performance