"James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> We migrate from Oracle to Postgresql14.8, one SQL has regression in Postgres run in 5800 milliseconds in
Postgresqlv14.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
indexto 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
rowsthen use tablea.column1=regexp_replace(tableb.column1....) as a filter. As a workaround we create a view then use
tablea.column1=view.column1that 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