apply_scanjoin_target_to_paths and partitionwise join

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.

--
Best Wishes,
Ashutosh Bapat