Re: Postgresql equal join on function with columns not use index - Mailing list pgsql-performance

"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