Problem about postponing gathering partial paths for topmost scan/join rel

[Richard Guo]

Hi all,

In commit 3f90ec85 we are trying to postpone gathering partial paths for
topmost scan/join rel until we know the final targetlist, in order to
allow more accurate costing of parallel paths. We do this by the
following code snippet in standard_join_search:

+   /*
+    * Except for the topmost scan/join rel, consider gathering
+    * partial paths.  We'll do the same for the topmost scan/join rel
+    * once we know the final targetlist (see grouping_planner).
+    */
+   if (lev < levels_needed)
+           generate_gather_paths(root, rel, false);

This change may cause a problem if the joinlist contains sub-joinlist
nodes, in which case 'lev == levels_needed' does not necessarily imply
it's the topmost for the final scan/join rel. It may only be the topmost
scan/join rel for the subproblem. And then we would miss the Gather
paths for this subproblem. It can be illustrated with the query below:

create table foo(i int, j int);
insert into foo select i, i from generate_series(1,50000)i;
analyze foo;

set max_parallel_workers_per_gather to 4;
set parallel_setup_cost to 0;
set parallel_tuple_cost to 0;
set min_parallel_table_scan_size to 0;

# explain (costs off) select * from foo a join foo b on a.i = b.i full join
foo c on b.i = c.i;
                     QUERY PLAN
----------------------------------------------------
 Hash Full Join
   Hash Cond: (b.i = c.i)
   ->  Hash Join
         Hash Cond: (a.i = b.i)
         ->  Gather
               Workers Planned: 4
               ->  Parallel Seq Scan on foo a
         ->  Hash
               ->  Gather
                     Workers Planned: 4
                     ->  Parallel Seq Scan on foo b
   ->  Hash
         ->  Gather
               Workers Planned: 4
               ->  Parallel Seq Scan on foo c
(15 rows)

Please note how we do the join for rel a and b. We run Gather above the
parallel scan and then do the join above the Gather.

These two base rels are grouped in a subproblem because of the FULL
JOIN. And due to the mentioned code change, we are unable to gather
partial paths for their joinrel.

If we can somehow fix this problem, then we would be able to do better
planning by running parallel join first and then doing Gather above the
join.

   ->  Gather
         Workers Planned: 4
         ->  Parallel Hash Join
               Hash Cond: (a.i = b.i)
               ->  Parallel Seq Scan on foo a
               ->  Parallel Hash
                     ->  Parallel Seq Scan on foo b

To fix this problem, I'm thinking we can leverage 'root->all_baserels'
to tell if we are at the topmost scan/join rel, something like:

--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3041,7 +3041,7 @@ standard_join_search(PlannerInfo *root, int
levels_needed, List *initial_rels)
                         * partial paths.  We'll do the same for the
topmost scan/join rel
                         * once we know the final targetlist (see
grouping_planner).
                         */
-                       if (lev < levels_needed)
+                       if (!bms_equal(rel->relids, root->all_baserels))
                                generate_useful_gather_paths(root, rel,
false);


Any thoughts?

Thanks
Richard