From: Tom Lane <tgl@sss.pgh.pa.us>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Postgresql equal join on function with columns not use index
In-reply-to: 
 <PH0PR11MB5191F00EB9B2EDB86616B442D654A@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
 <PH0PR11MB5191F00EB9B2EDB86616B442D654A@PH0PR11MB5191.namprd11.prod.outlook.com>
Comments: In-reply-to "James Pang (chaolpan)" <chaolpan@cisco.com>
	message dated "Mon, 12 Jun 2023 08:09:28 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1166863.1686575919.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 12 Jun 2023 09:18:39 -0400
Message-ID: <1166864.1686575919@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1166864.1686575919%40sss.pgh.pa.us>
Precedence: bulk

"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 Postgresql v14.8,  but the same S=
QL got done in several hundred milliseconds in Oracle database.
>        With multiple table JOINs, if the join condition is  tablea.colum=
n1=3Dtableb.column1, optimizer will use the index to filter data in nest l=
oops, but if  tablea.column1=3Dregexp_replace(tableb.column1....),
> Optimizer will not be able to use the index on tablea.column1, then it d=
o a table scan and nestloop to produce a lot rows then use tablea.column1=3D=
regexp_replace(tableb.column1....) as a filter.  As a workaround we create=
 a view then use tablea.column1=3Dview.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