Re: apply_scanjoin_target_to_paths and partitionwise join

On Wed, Jul 24, 2024 at 9:42 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Wed, May 22, 2024 at 3:57 PM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > I will create patches for the back-branches once the patch for master is in a committable state.
>
> AFAIU, this patch prevents apply_scanjoin_target_to_paths() from
> discarding old paths of partitioned joinrels. Therefore, we can
> retain non-partitionwise join paths if the cheapest path happens to be
> among them.

Right. Thanks for the summary.

>
> One concern from me is that if the cheapest path of a joinrel is a
> partitionwise join path, following this approach could lead to
> undesirable cross-platform plan variations, as detailed in the
> original comment.

I read through the email thread [3] referenced in the commit
(1d338584062b3e53b738f987ecb0d2b67745232a) which added that comment.
The change is mentioned in [4] first. Please notice that this change
is unrelated to the bug that started the thread. [5], [6] talk about
the costs of projection path above Append vs project path below
Append. But I don't see any example of any cross-platform plan
variations. I also do not see an example in that thread where such a
plan variation results in bad performance. If the costs of
partitionwise and non-partitionwise join paths are so close to each
other that platform specific arithmetic can swing it one way or the
other, possibly their performance is going to be comparable. Without
an example query it's hard to assess this possibility or address the
concern, especially when we have examples of the behaviour otherwise.

>
> Is there a specific query that demonstrates benefits from this change?
> I'm curious about scenarios where a partitionwise join runs slower
> than a non-partitionwise join.

[1] provides a testcase where a nonpartitionwise join is better than
partitionwise join. This testcase is derived from a bug reported by an
EDB customer. [2] is another bug report on psql-bugs.

[1] https://www.postgresql.org/message-id/CAKZiRmyaFFvxyEYGG_hu0F-EVEcqcnveH23MULhW6UY_jwykGw%40mail.gmail.com
[2] https://www.postgresql.org/message-id/flat/786.1565541557%40sss.pgh.pa.us#9d50e1b375201f29bbf17072d75569e3
[3] https://www.postgresql.org/message-id/flat/15669-02fb3296cca26203%40postgresql.org
[4] https://www.postgresql.org/message-id/20477.1551819776%40sss.pgh.pa.us
[5] https://www.postgresql.org/message-id/15350.1551973953%40sss.pgh.pa.us
[6] https://www.postgresql.org/message-id/24357.1551984010%40sss.pgh.pa.us

--
Best Wishes,
Ashutosh Bapat