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...
There was more times discussion about rewriting OR conditions to UNION - but nobody did this work what I know. Usually Postgres process OR conditions well due bitmap scans, but it doesn't work well in some special cases. To this time this issue was fixed by manual query rewriting.
Regards
Pavel
On Mon, Dec 9, 2019 at 1:49 PM Michael Lewis <mlewis@entrata.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.