Re: apply_scanjoin_target_to_paths and partitionwise join

On Thu, Oct 30, 2025 at 3:29 AM Robert Haas <robertmhaas@gmail.com> wrote:
> That said, after some investigation, I believe your conclusion to be
> correct. What seems to be happening with Q3 is that the higher-cost
> path (27.47, one partitionwise join) is added before the absolute
> cheapest path (27.27, two partitionwise joins) and that's not enough
> difference for compare_path_costs_fuzzily with STD_FUZZ_FACTOR to
> prefer the second one over the first. If I raise STD_FUZZ_FACTOR from
> 0.5 to 5, to make the costs of the two paths more different, then this
> behavior vanishes. So I agree that this seems to have nothing to do
> with your work; it appears that your test cases just got swept up in
> it incidentally.
>
> Do you have any thoughts on how we might adjust these test cases?

For Q1, the plan change does not appear to compromise its original
purpose.  The test case is meant to verify that unique-ification works
correctly with partitionwise joins, so as long as the join to t3 is
performed in a partitionwise manner, we're fine.

For Q2, I suspect that the cost estimation issue in mergejoin may be
affecting the planner's choice of plan.  When I set enable_mergejoin
to off, I was able to get a partitionwise join plan again.  Therefore,
I think we can modify the test case to manually disable mergejoin for
this query.

For Q3, you can get a plan with full partitionwise joins again by
removing either the clause "t1.c = 0" or "t1.b = 0", and doing so
doesn't change the query's output.  You can also get a fully
partitionwise join plan by removing both clauses, though in that case
the query output becomes too large to include in a test case.

- Richard