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

On Fri, Jul 15, 2022 at 4:03 PM Richard Guo <guofenglinux@gmail.com> wrote:

>
> On Thu, Jul 14, 2022 at 10:02 PM Antonin Houska <ah@cybertec.at> wrote:
>
>> I'd prefer a test that demonstrates that the Gather node at the top of the
>> "subproblem plan" is useful purely from the *cost* perspective, rather
>> than
>> due to executor limitation.
>
>
> This patch provides an additional path (Gather atop of subproblem) which
> was not available before. But your concern makes sense that we need to
> show this new path is valuable from competing on cost with other paths.
>
> How about we change to Nested Loop at the topmost? Something like:
>

Maybe a better example is that we use a small table 'c' to avoid the
Gather node above scanning 'c', so that the path of parallel nestloop is
possible to be generated.

set join_collapse_limit to 2;

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

Thanks
Richard