MIME-Version: 1.0
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>
 <2626296.1761692854@sss.pgh.pa.us>
In-Reply-To: <2626296.1761692854@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 29 Oct 2025 10:46:35 +0900
Message-ID: 
 <CAMbWs48BmV_eT2+AM6J7RA_09XgUjf3HG3VkY8-uvTUTML++XA@mail.gmail.com>
Subject: Re: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs
 postgres 18 is returning different/duplicate results
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Lori Corbani <Lori.Corbani@jax.org>,
 Thomas Munro <thomas.munro@gmail.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAMbWs48BmV_eT2%2BAM6J7RA_09XgUjf3HG3VkY8-uvTUTML%2B%2BXA%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 29, 2025 at 8:07=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Code changes look good, and I confirm that I can't reproduce the
> failure anymore with this patch.

Thanks for the review and confirmation.

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

Fair point.

> 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 =3D 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.

To make the right-semi join look more appealing, I wonder if we could
apply a filter to t1 to make its output smaller than t2, so that the
planner is more likely to choose t1 as the inner side for building the
hash table.

explain select * from tenk1 t1
where exists(select 1 from tenk1 t2 where fivethous =3D t1.fivethous)
and t1.fivethous < 5;

(I'm using fivethous instead of tenthous to avoid interference from
index scan.)

However, this doesn't seem to move the needle any further.  The costs
of PRSJ (unpatched) and PSJ (patched) are 755.67 and 777.54.  The cost
difference is still not very great.

> 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=3D0;
> set local ...
>
> explain ...
>
> rollback;

Brilliant!  Thanks for the suggestion.

- Richard