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

On Thu, Jul 18, 2024 at 4:11 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> On Thu, Jul 18, 2024 at 4:00 PM Alexander Lakhin <exclusion(at)gmail(dot)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