On Thu, Jul 18, 2024 at 4:11 PM Richard Guo <guofengli...@gmail.com> wrote: > On Thu, Jul 18, 2024 at 4:00 PM Alexander Lakhin <exclus...@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.ca...@post.pl Thanks Richard
v1-0001-Fix-unstable-test-in-select_parallel.sql.patch
Description: Binary data
