Re: Postgresql equal join on function with columns not use index

"James Pang (chaolpan)" <chaolpan(at)cisco(dot)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