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

Hi,
    Looks like it's the function "regexp_replace" volatile and restrict=false make the difference,  we have our
applicationrole with default search_path=oracle,$user,public,pg_catalog.     
     =#    select oid,proname,pronamespace::regnamespace,prosecdef,proisstrict,provolatile from pg_proc where
proname='regexp_replace'order by oid; 
  oid  |    proname     | pronamespace | prosecdef | proisstrict | provolatile
-------+----------------+--------------+-----------+-------------+-------------
  2284 | regexp_replace | pg_catalog   | f         | t           | i
  2285 | regexp_replace | pg_catalog   | f         | t           | i
 17095 | regexp_replace | oracle       | f         | f           | v
 17096 | regexp_replace | oracle       | f         | f           | v
 17097 | regexp_replace | oracle       | f         | f           | v
 17098 | regexp_replace | oracle       | f         | f           | v

--with default it use orafce, oracle.regexp_replace function,
Select  a.phonenumber,... from tableA a, tableB b where a.phonenumber=oracle. regexp_replace(b.PHONENUMBER,'[^0-9]','')
, 
  --index on a.phonenumber not used

Switch to pg_catalog.regexp_replace(b.PHONENUMBER,'[^0-9]',''),
  Index on a.phonenumber got used.

Thanks,

James Pang

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, June 12, 2023 9:19 PM
To: James Pang (chaolpan) <chaolpan@cisco.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql equal join on function with columns not use index

"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 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
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