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