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

"James Pang (chaolpan)" <chaolpan@cisco.com> writes:
>     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

Why in the world are the oracle ones marked volatile?  That's what's
preventing them from being used in index quals.

            regards, tom lane