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>
 <CAFj8pRCO5ocbr-wFWx5QsKdfkW-=XuQ6zkW5FES7ERQZQHtpwQ@mail.gmail.com>
 <982de4a4-71b6-4d1d-afe2-35b1c5d43529@vondra.me>
 <CAFj8pRASJuRQKHOoBTnR5aRUeRKpNAmrYQcBrQb=yqeZ_8me9Q@mail.gmail.com>
 <CAEvyyTi1M6JhHb6sR+xK-kp2bezMoADSC+RY2A+DbdEn+_BLxA@mail.gmail.com>
 <8927A117-A7EA-41E8-94B3-0B4F7767DA8B@outlook.com>
 <DBCBD9E0-27EC-4F50-A568-3E99FCF2F7B7@outlook.com>
In-Reply-To: <DBCBD9E0-27EC-4F50-A568-3E99FCF2F7B7@outlook.com>
From: John Naylor <johncnaylorls@gmail.com>
Date: Tue, 5 May 2026 12:40:13 +0700
Message-ID: 
 <CANWCAZbbTazxGeMU=qdyi1kBr_Nkjv1n6vZR-hW30QbqVqkx1Q@mail.gmail.com>
Subject: Re: Add a greedy join search algorithm to handle large join problems
To: Chengpeng Yan <chengpeng_yan@outlook.com>
Cc: Tomas Vondra <tomas@vondra.me>, lakshmi <lakshmigcdac@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CANWCAZbbTazxGeMU%3Dqdyi1kBr_Nkjv1n6vZR-hW30QbqVqkx1Q%40mail.gmail.com>
Precedence: bulk

On Sun, May 3, 2026 at 7:46=E2=80=AFPM Chengpeng Yan <chengpeng_yan@outlook=
.com> wrote:
> > On Feb 14, 2026, at 13:39, Chengpeng Yan <chengpeng_yan@outlook.com> wr=
ote:
> > 1. Continue evaluating plan quality on more datasets/workloads. I=E2=80=
=99ve
> > already collected several candidate tests: some are JOB-based
> > variants, and others are synthetic workloads. Next, I plan to
> > consolidate these into a unified test set (with reproducible
> > setup/details), publish it, and run broader comparative evaluation.

> I ran the current pure-GOO variants on JOB and JOB-Complex [1], 143
> queries in total. JOB-Complex uses the same IMDB/JOB setting, but adds
> harder predicates and more challenging join patterns. The tested

Thanks for those results. As Tomas mentioned above, evaluation should
focus on cases where DP won't be used. Joins with a small number of
relations aren't going to tell us anything, especially since (I think)
GEGO will at times accidentally cover the entire seach space. Indeed,
there are quite a few queries where GOO is worse than GEQO by around
2-3x, but also small enough to be handled by DP anyway, so reporting
them is a distraction.

Less than half of the JOB-Complex queries have at least 12 "joins"
(BTW, is that number in the pdf actual joins or is it base
relations?), but the results (and summary results) include small joins
as well. Looking at the queries where one of GOO/GEQO is much better
than the other do seem to happen with large join problems.

> GOO(combined) with cost and result_size may already be a useful baseline
> or candidate generator, but the regressions above do not support making
> it the default GEQO replacement. One possible advantage over GEQO is
> that GOO may work better with pg_plan_advice, but I need to understand
> the details better before making a stronger claim.

Given that all heuristic join enumeration methods can produce
spectacularly bad plans, the ability to influence the plan is more
crucial with large join problems with small ones. Features should be
orthogonal in general, and in this case, integrating well with plan
advice seems like a strong deciding factor.

--
John Naylor
Amazon Web Services