Hello Richard,
12.07.2024 05:29, Richard Guo wrote:
On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <guofengli...@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
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500840.00 rows=33333333 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty,
t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd,
t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two,
t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand,
t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Gather (cost=0.00..370.00 rows=10000 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty,
t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd,
t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..370.00 rows=2500
width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten,
t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous,
t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Materialize (cost=0.00..495.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty,
t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd,
t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000
width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten,
t2.twenty, t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous,
t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
(12 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1500089.98 rows=33316667 width=488)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty,
t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd,
t1.even, t1.stringu1, t1.stringu2, t1.string4, t2.unique1, t2.unique2, t2.two,
t2.four, t2.ten, t2.twenty, t2.hundred, t2.thousand, t2.twothousand,
t2.fivethous, t2.tenthous, t2.odd, t2.even, t2.stringu1, t2.stringu2, t2.string4
Join Filter: (t1.two > t2.two)
-> Seq Scan on public.tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
Output: t2.unique1, t2.unique2, t2.two, t2.four, t2.ten, t2.twenty,
t2.hundred, t2.thousand, t2.twothousand, t2.fivethous, t2.tenthous, t2.odd,
t2.even, t2.stringu1, t2.stringu2, t2.string4
-> Materialize (cost=0.00..419.96 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten, t1.twenty,
t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous, t1.odd,
t1.even, t1.stringu1, t1.stringu2, t1.string4
-> Gather (cost=0.00..369.99 rows=9995 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten,
t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous,
t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1 t1 (cost=0.00..369.99
rows=2499 width=244)
Output: t1.unique1, t1.unique2, t1.two, t1.four, t1.ten,
t1.twenty, t1.hundred, t1.thousand, t1.twothousand, t1.fivethous, t1.tenthous,
t1.odd, t1.even, t1.stringu1, t1.stringu2, t1.string4
(12 rows)
