MIME-Version: 1.0
References: <CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com>
In-Reply-To: <CAHewXNkPmtEXNfVQMou_7NqQmFABca9f4etjBtdbbm0ZKDmWvw@mail.gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 5 Sep 2023 18:50:56 +0800
Message-ID: <CAMbWs49LbQF_Z0iKPRPnTHfsRECT7M-4rF6ft5vpW1ARSpBkPA@mail.gmail.com>
Subject: Re: Should consider materializing the cheapest inner path in consider_parallel_nestloop()
To: tender wang <tndrwang@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000075003e06049a67bf"
Archived-At: <https://www.postgresql.org/message-id/CAMbWs49LbQF_Z0iKPRPnTHfsRECT7M-4rF6ft5vpW1ARSpBkPA%40mail.gmail.com>
Precedence: bulk

--00000000000075003e06049a67bf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Sep 5, 2023 at 4:52=E2=80=AFPM tender wang <tndrwang@gmail.com> wro=
te:

>    I recently run benchmark[1] on master, but I found performance problem
> as below:
> ...
>
> I debug the code and find consider_parallel_nestloop() doesn't consider
> materialized form of the cheapest inner path.
>

Yeah, this seems an omission in commit 45be99f8.  I reviewed the patch
and here are some comments.

* I think we should not consider materializing the cheapest inner path
  if we're doing JOIN_UNIQUE_INNER, because in this case we have to
  unique-ify the inner path.

* I think we can check if it'd be parallel safe before creating the
  material path, thus avoid the creation in unsafe cases.

* I don't think the test case you added works for the code changes.
  Maybe a plan likes below is better:

explain (costs off)
select * from tenk1, tenk2 where tenk1.two =3D tenk2.two;
                  QUERY PLAN
----------------------------------------------
 Gather
   Workers Planned: 4
   ->  Nested Loop
         Join Filter: (tenk1.two =3D tenk2.two)
         ->  Parallel Seq Scan on tenk1
         ->  Materialize
               ->  Seq Scan on tenk2
(7 rows)

Thanks
Richard

--00000000000075003e06049a67bf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Tue, Sep 5, 2023 at 4:52=E2=80=AFPM te=
nder wang &lt;<a href=3D"mailto:tndrwang@gmail.com">tndrwang@gmail.com</a>&=
gt; wrote:</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div>=C2=A0 =C2=A0I recently=C2=A0run benchmark[1] on master, but =
I found performance=C2=A0problem as below:</div><div>...</div><div><br></di=
v><div>I debug the code and find consider_parallel_nestloop() doesn&#39;t c=
onsider materialized form of the cheapest inner path.</div></div></blockquo=
te><div><br></div><div>Yeah, this seems an omission in commit 45be99f8.=C2=
=A0 I reviewed the patch<br>and here are some comments.<br><br>* I think we=
 should not consider materializing the cheapest inner path<br>=C2=A0 if we&=
#39;re doing JOIN_UNIQUE_INNER, because in this case we have to<br>=C2=A0 u=
nique-ify the inner path.<br><br>* I think we can check if it&#39;d be para=
llel safe before creating the<br>=C2=A0 material path, thus avoid the creat=
ion in unsafe cases.<br><br>* I don&#39;t think the test case you added wor=
ks for the code changes.<br>=C2=A0 Maybe a plan likes below is better:<br><=
br>explain (costs off)<br>select * from tenk1, tenk2 where tenk1.two =3D te=
nk2.two;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
QUERY PLAN<br>----------------------------------------------<br>=C2=A0Gathe=
r<br>=C2=A0 =C2=A0Workers Planned: 4<br>=C2=A0 =C2=A0-&gt; =C2=A0Nested Loo=
p<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Join Filter: (tenk1.two =3D tenk2.tw=
o)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Parallel Seq Scan on te=
nk1<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Materialize<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on ten=
k2<br>(7 rows)<br><br>Thanks<br>Richard<br></div></div></div>

--00000000000075003e06049a67bf--