public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: Richard Guo <[email protected]>
To: Tender Wang <[email protected]>
Cc: Tomasz Rybak <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: David Rowley <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
Date: Thu, 18 Jul 2024 11:00:00 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMbWs48sBaMRtu+WfiAaVOuFU6tWr_++y=Ythkvj-ufgxTiH-Q@mail.gmail.com>
References: <CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com>
<CAMbWs49LbQF_Z0iKPRPnTHfsRECT7M-4rF6ft5vpW1ARSpBkPA@mail.gmail.com>
<CA+TgmobPBCvTHnBVn+a-=MS7pO_PUvtiLJkYo=BOrUjOxTss8g@mail.gmail.com>
<CAMbWs49agQ1THL0XYv_4aeZXN2R1cRksevja05n-MFOzZtNsuA@mail.gmail.com>
<CAHewXN=-XQwFY0n5EMUTzvK4G0aHWkNzdOPCfwnmQw3kDed2zQ@mail.gmail.com>
<[email protected]>
<CAHewXN=rP49ySfue9FA437jW4Me+FLBY-58zqTy+A_jxGGW=Xg@mail.gmail.com>
<[email protected]>
<CAHewXNmWbA9Oe6JCEc66dJrVyyBDv1uSx6z_zdz-BQRE0Jr9pQ@mail.gmail.com>
<CAMbWs48TaubitHF+JXqysbSHLCzTLOU9PD3zpLGFUtizsonAMA@mail.gmail.com>
<CAHewXNkTh0RS7FMmVjMmPbMjG1=mUQ6uchxG3BuKsVH1=tNjag@mail.gmail.com>
<CAMbWs48jJHxQfb0F0G1c6O=qgzNxRbJ-uE8FwpH=adAVmvnxSg@mail.gmail.com>
<CAMbWs48sBaMRtu+WfiAaVOuFU6tWr_++y=Ythkvj-ufgxTiH-Q@mail.gmail.com>
Hello Richard,
12.07.2024 05:29, Richard Guo wrote:
> On Sat, Jul 6, 2024 at 5:32 PM Richard Guo <[email protected]> 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)
Attachments:
[text/plain] plan.expected.txt (2.3K, ../[email protected]/2-plan.expected.txt)
download | inline:
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)
[text/plain] plan.unexpected.txt (2.3K, ../[email protected]/3-plan.unexpected.txt)
download | inline:
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)
view thread (23+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox