"James Pang (chaolpan)" <chaol...@cisco.com> writes: > We migrate from Oracle to Postgresql14.8, one SQL has regression in > Postgres run in 5800 milliseconds in Postgresql v14.8, but the same SQL got > done in several hundred milliseconds in Oracle database. > With multiple table JOINs, if the join condition is > tablea.column1=tableb.column1, optimizer will use the index to filter data in > nest loops, but if tablea.column1=regexp_replace(tableb.column1....), > Optimizer will not be able to use the index on tablea.column1, then it do a > table scan and nestloop to produce a lot rows then use > tablea.column1=regexp_replace(tableb.column1....) as a filter. As a > workaround we create a view then use tablea.column1=view.column1 that works. > Is it expected ? details as below.
It's impossible to comment on this usefully with such a fragmentary description of the problem. Please send a complete, self-contained test case if you want anybody to look at it carefully. https://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane
