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

Hello Richard,

12.07.2024 05:29, Richard Guo wrote:
> On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <guofenglinux@gmail.com> wrote:
>> Here is a new rebase.
>>
>> I'm planning to push it next week, barring any objections.
> Pushed.

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)

I've managed to reproduce this plan change when running
multiple 027_stream_regress.pl instances simultaneously, with
parallel_schedule reduced to:
test: test_setup
test: create_misc
test: create_index
test: sanity_check
test: select_parallel

I've added the following to the test and got two verbose plans for
comparison (see the attachment).
  -- 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;
+\o plan.txt
+explain (verbose)
+    select * from tenk1 t1, (select * from tenk2 t2 offset 0) t2 where t1.two > t2.two;
+\o
  alter table tenk2 reset (parallel_workers);

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tamandua&dt=2024-07-17%2017%3A12%3A53

Best regards,
Alexander