Re: apply_scanjoin_target_to_paths and partitionwise join

On Thu, Apr 11, 2024 at 12:07 PM Ashutosh Bapat <
ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:

> Hi All,
> Per below code and comment in apply_scanjoin_target_to_paths(), the
> function zaps all the paths of a partitioned relation.
> /*
> * If the rel is partitioned, we want to drop its existing paths and
> * generate new ones. This function would still be correct if we kept the
> * existing paths: we'd modify them to generate the correct target above
> * the partitioning Append, and then they'd compete on cost with paths
> * generating the target below the Append
> ... snip ...
> */
> if (rel_is_partitioned)
> rel->pathlist = NIL;
>
> Later the function adjusts the targets of paths in child relations and
> constructs Append paths from them. That works for simple partitioned
> relations but not for join between partitioned relations. When
> enable_partitionwise_join is true, the joinrel representing a join between
> partitioned relations may have join paths joining append paths and Append
> paths containing child join paths. Once we zap the pathlist, the only paths
> that can be computed again are the Append paths. If the optimal path,
> before applying the new target, was a join of append paths it will be lost
> forever. This will result in choosing a suboptimal Append path.
>
> We have one such query in our regression set.
>
> SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN
> plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE
> coalesce(t1.a, 0 ) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY
> t1.c, t1.a, t2.a, t3.a;
>
> For this query, the cheapest Append of Joins path has cost 24.97..25.57
> and the cheapest Join of Appends path has cost 21.29..21.81. The latter
> should be chosen even though enable_partitionwise_join is ON. But this
> function chooses the first.
>
> The solution is to zap the pathlists only for simple partitioned relations
> like the attached patch.
>
> With this patch above query does not choose non-partitionwise join path
> and partition_join test fails. That's expected. But we need to replace that
> query with some query which uses partitionwise join while maintaining the
> conditions of the test as explained in the comment above that query. I have
> tried a few variations but without success. Suggestions welcome.
>
> The problem is reproducible on PG 15. The patch is based on 15_STABLE
> branch. But the problem exists in recent branches as well.
>
>
>
After sending email I found another thread where this problem was discussed
[1]. Tom has the same explanation as mine however he favoured not doing
anything about it. Since the rationale was based on the cost and not actual
performance, it was fine at that time. At EDB we have a customer case where
partitionwise join plan is worse than non-partitionwise join plan. The
suboptimal plan is chosen because of the above code. I think we should fix
the problem.

[1]
https://www.postgresql.org/message-id/flat/786.1565541557%40sss.pgh.pa.us#9d50e1b375201f29bbf17072d75569e3

--
Best Wishes,
Ashutosh Bapat