Re: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results

On Wed, Oct 29, 2025 at 10:46 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> 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 = 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.

After spending a bit more time experimenting, I couldn't find a better
query that shows a large difference between the costs of the PRSJ plan
and the patched version plan. So I've updated the test case to use
the aforementioned one. I also removed the reset statements and
switched to the begin/rollback pattern for the test case.

- Richard