Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()

On Thu, Jul 18, 2024 at 4:11 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Thu, Jul 18, 2024 at 4:00 PM Alexander Lakhin <exclusion@gmail.com> wrote:
> > Please look at a recent buildfarm failure [1], which shows some
> > instability of that test addition:
> >   -- the joinrel is not parallel-safe due to the OFFSET clause in the subquery
> >   explain (costs off)
> >       select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
> > -                QUERY PLAN
> > --------------------------------------------
> > +                   QUERY PLAN
> > +-------------------------------------------------
> >    Nested Loop
> >      Join Filter: (t1.two > t2.two)
> > -   ->  Gather
> > -         Workers Planned: 4
> > -         ->  Parallel Seq Scan on tenk1 t1
> > +   ->  Seq Scan on tenk2 t2
> >      ->  Materialize
> > -         ->  Seq Scan on tenk2 t2
> > +         ->  Gather
> > +               Workers Planned: 4
> > +               ->  Parallel Seq Scan on tenk1 t1
> >   (7 rows)
>
> Thank you for the report and investigation.  Will have a look.

The problemed plan is a non-parallel nestloop join.  It's just chance
which join order the planner will pick, and slight variations in
underlying statistics could result in a different displayed plan.
>From the two verbose plans, we can see slight variations in the
statistics for the parallel seqscan of tenk1.

->  Parallel Seq Scan on public.tenk1 t1  (cost=0.00..370.00 rows=2500
width=244)

VS.

->  Parallel Seq Scan on public.tenk1 t1  (cost=0.00..369.99 rows=2499
width=244)

I have no idea why the underlying statistics changed, but it seems
that this slight change is sufficent to result in a different plan.

According to the discussion in [1], I think what we wanted to test
with this query is that parallel nestloop join is not generated if the
inner path is not parallel-safe.  Therefore, I modified this test case
to use a lateral join, rendering the inner path not parallel-safe
while also enforcing the join order.  Please see attached.

[1] https://postgr.es/m/5641923793cef7706395a34e62538b75d05e498b.camel@post.pl

Thanks
Richard