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

*From:* Pavel Stehule > *Sent:* Tuesday, June 13, 2023 11:01 PM > *To:* Tom Lane > *Cc:* James Pang (chaolpan) ; > pgsql-performance@lists.postgresql.org > *Subject:* Re: Postgresql equal join on function with columns not use > index > > > > > > > > ú

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

11:01 PM To: Tom Lane Cc: James Pang (chaolpan) ; pgsql-performance@lists.postgresql.org Subject: Re: Postgresql equal join on function with columns not use index út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule mailto:pavel.steh...@gmail.com>> napsal: út 13. 6. 2023 v 15:50 odesílat

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

út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule napsal: > > > út 13. 6. 2023 v 15:50 odesílatel Tom Lane napsal: > >> "James Pang (chaolpan)" writes: >> > Looks like it's the function "regexp_replace" volatile and >> restrict=false make the difference, we have our application role with >>

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

út 13. 6. 2023 v 15:50 odesílatel Tom Lane napsal: > "James Pang (chaolpan)" writes: > > 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. > > =#se

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

"James Pang (chaolpan)" writes: > 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,prosec

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

ch to pg_catalog.regexp_replace(b.PHONENUMBER,'[^0-9]',''), Index on a.phonenumber got used. Thanks, James Pang -Original Message- From: Tom Lane Sent: Monday, June 12, 2023 9:19 PM To: James Pang (chaolpan) Cc: pgsql-performance@lists.postgresql.org Subject:

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

"James Pang (chaolpan)" 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

Postgresql equal join on function with columns not use index

Hi, 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.