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
Date: Mon, 12 Jun 2023 09:18:39 -0400
Message-ID: <1166864.1686575919@sss.pgh.pa.us> (raw)
In-Reply-To: <PH0PR11MB5191F00EB9B2EDB86616B442D654A@PH0PR11MB5191.namprd11.prod.outlook.com>
References: <PH0PR11MB5191F00EB9B2EDB86616B442D654A@PH0PR11MB5191.namprd11.prod.outlook.com>
"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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, chaolpan@cisco.com, pgsql-performance@lists.postgresql.org
Subject: Re: Postgresql equal join on function with columns not use index
In-Reply-To: <1166864.1686575919@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox