MIME-Version: 1.0
References: <3FF63E99-AB4F-41A9-BC78-AAB28823FBD0@Outlook.com>
 <6db6d2ec-7529-4add-9a95-178fc318311d@vondra.me>
 <313ACE5A-CBF1-43B3-9181-10D3E8ADF424@Outlook.com>
 <5abd6054-413c-4f48-9172-d8b31062b266@vondra.me>
 <cb313155-24c4-4838-a46b-44968993a6e2@vondra.me>
 <938E2286-9B0D-4F8D-A916-8E0E35D55034@Outlook.com>
 <e82d4302-2450-4915-93a5-7df75f69c385@vondra.me>
 <CANWCAZY529EPHyo1kLnEzjFBq-UaDPc3KErK=ApqDZZ1Oc-XHg@mail.gmail.com>
In-Reply-To: 
 <CANWCAZY529EPHyo1kLnEzjFBq-UaDPc3KErK=ApqDZZ1Oc-XHg@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 11 Dec 2025 07:12:29 +0100
Message-ID: 
 <CAFj8pRCO5ocbr-wFWx5QsKdfkW-=XuQ6zkW5FES7ERQZQHtpwQ@mail.gmail.com>
Subject: Re: Add a greedy join search algorithm to handle large join problems
To: John Naylor <johncnaylorls@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, Chengpeng Yan <chengpeng_yan@outlook.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e8dd9b0645a709a4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRCO5ocbr-wFWx5QsKdfkW-%3DXuQ6zkW5FES7ERQZQHtpwQ%40mail.gmail.com>
Precedence: bulk

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

=C4=8Dt 11. 12. 2025 v 3:53 odes=C3=ADlatel John Naylor <johncnaylorls@gmai=
l.com>
napsal:

> On Wed, Dec 10, 2025 at 5:20=E2=80=AFPM Tomas Vondra <tomas@vondra.me> wr=
ote:
> > I did however notice an interesting thing - running EXPLAIN on the 99
> > queries (for 3 scales and 0/4 workers, so 6x 99) took this much time:
> >
> > master:       8s
> > master/geqo: 20s
> > master/goo:   5s
>
> > It's nice that "goo" seems to be faster than "geqo" - assuming the plan=
s
> > are comparable or better. But it surprised me switching to geqo makes i=
t
> > slower than master. That goes against my intuition that geqo is meant t=
o
> > be cheaper/faster join order planning. But maybe I'm missing something.
>
> Yeah, that was surprising. It seems that geqo has a large overhead, so
> it takes a larger join problem for the asymptotic behavior to win over
> exhaustive search.
>

If I understand correctly to design - geqo should be slower for any queries
with smaller complexity. The question is how many queries in the tested
model are really complex.


>
> --
> John Naylor
> Amazon Web Services
>
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">=C4=8Dt 11. 12.=
 2025 v=C2=A03:53 odes=C3=ADlatel John Naylor &lt;<a href=3D"mailto:johncna=
ylorls@gmail.com">johncnaylorls@gmail.com</a>&gt; napsal:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex">On Wed, Dec 10, 2025 at 5:20=E2=
=80=AFPM Tomas Vondra &lt;<a href=3D"mailto:tomas@vondra.me" target=3D"_bla=
nk">tomas@vondra.me</a>&gt; wrote:<br>
&gt; I did however notice an interesting thing - running EXPLAIN on the 99<=
br>
&gt; queries (for 3 scales and 0/4 workers, so 6x 99) took this much time:<=
br>
&gt;<br>
&gt; master:=C2=A0 =C2=A0 =C2=A0 =C2=A08s<br>
&gt; master/geqo: 20s<br>
&gt; master/goo:=C2=A0 =C2=A05s<br>
<br>
&gt; It&#39;s nice that &quot;goo&quot; seems to be faster than &quot;geqo&=
quot; - assuming the plans<br>
&gt; are comparable or better. But it surprised me switching to geqo makes =
it<br>
&gt; slower than master. That goes against my intuition that geqo is meant =
to<br>
&gt; be cheaper/faster join order planning. But maybe I&#39;m missing somet=
hing.<br>
<br>
Yeah, that was surprising. It seems that geqo has a large overhead, so<br>
it takes a larger join problem for the asymptotic behavior to win over<br>
exhaustive search.<br></blockquote><div><br></div><div>If I understand corr=
ectly to design - geqo should be slower for any queries with smaller comple=
xity. The question is how many queries in the tested model are really compl=
ex.</div><div><br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex">
<br>
--<br>
John Naylor<br>
Amazon Web Services<br>
<br>
<br>
</blockquote></div></div>

--000000000000e8dd9b0645a709a4--