Hi,
Looks like it's the function "regexp_replace" volatile and restrict=false
make the difference, we have our application role 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 <[email protected]>
Sent: Monday, June 12, 2023 9:19 PM
To: James Pang (chaolpan) <[email protected]>
Cc: [email protected]
Subject: Re: Postgresql equal join on function with columns not use index
"James Pang (chaolpan)" <[email protected]> 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
