From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Lori Corbani <Lori.Corbani@jax.org>,
 Thomas Munro <thomas.munro@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs
 postgres 18 is returning different/duplicate results
In-reply-to: 
 <CAMbWs49J=KnxRkxLevtJ7nXSF01Tym3H0EVwV+Lmw2CkoRspOw@mail.gmail.com>
References: <19094-6ed410eb5b256abd@postgresql.org>
 <1970841.1761402665@sss.pgh.pa.us>
 <CY4PR06MB3575BFEBB314BB94654A7CC991FCA@CY4PR06MB3575.namprd06.prod.outlook.com>
 <CY4PR06MB3575C513D645156C738ABBDC91FCA@CY4PR06MB3575.namprd06.prod.outlook.com>
 <2441795.1761593103@sss.pgh.pa.us> <2447089.1761595976@sss.pgh.pa.us>
 <CAMbWs49MBMkc_HhCW2v_cNSPH8=tCKDKd0AgEX6OEp-xOkbFfw@mail.gmail.com>
 <2485781.1761617055@sss.pgh.pa.us>
 <CAMbWs49J=KnxRkxLevtJ7nXSF01Tym3H0EVwV+Lmw2CkoRspOw@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Tue, 28 Oct 2025 17:46:52 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2626295.1761692854.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 28 Oct 2025 19:07:34 -0400
Message-ID: <2626296.1761692854@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2626296.1761692854%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> On Tue, Oct 28, 2025 at 11:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Right.  I agree that #3 is the most attractive stopgap answer.
>> We can look into #2 later, but it doesn't sound like something
>> to back-patch.

> Agreed.  Here's a patch that follows along the lines of option #3.

Code changes look good, and I confirm that I can't reproduce the
failure anymore with this patch.

I'm not convinced that the new regression test case is worth the
cycles, at least not in this form.  The main thing that's annoying me
about it is creating/populating/analyzing its own large one-use table;
that approach soon leads to regression suites that take forever.
You could answer that objection by making use of some existing
regression table, for instance this seems to work as well:

explain select * from tenk1 t1
where exists(select 1 from tenk1 t2 where tenthous = t1.tenthous);

However, I feel like it may still not be a great test, because it only
shows that the planner *didn't* pick PRSJ, not that it *couldn't*.
The cost differential between PRSJ with these settings and the Hash
Semi Join plan that you get after applying the patch is not very
great; so it's easy to imagine future changes that'd mean we'd not
prefer PRSJ here anyway.  But I'm not sure what we could do about
that, so operationally this may be as good a test as we can get
anyway.

Another thought is that rather than having to remember to reset all
those planner options, you could make the test a bit shorter and
more maintainable by writing something like

begin;
set local parallel_setup_cost=0;
set local ...

explain ...

rollback;

			regards, tom lane