Re: apply_scanjoin_target_to_paths and partitionwise join

On Wed, Oct 29, 2025 at 8:36 AM Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Oct 29, 2025 at 5:21 AM Richard Guo <guofenglinux@gmail.com> wrote:
> > I don't think the rewrite of unique-ification requires any adjustment
> > to this patch.  I ran Q1 on v18, which does not include the
> > unique-ification changes, and here is what I observed: without
> > Ashutosh's patch, it performs a full partitionwise join; with the
> > patch, it performs one join partitionwise and the other
> > non-partitionwise.  The costs of the unpatched versus patched versions
> > on v18 are 2286.11 and 1420.40, respectively, indicating that
> > Ashutosh's patch reduces the cost by a large amount.  This matches
> > your observation exactly.  I think this suggests that we can rule out
> > the interference from the unique-ification changes.
>
> This testing methodology makes some sense to me, but it seems here you
> have tested Q1 here, which was the good case, rather than Q3, which
> was the troubling one.

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?

--
Robert Haas
EDB: http://www.enterprisedb.com