Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqr5N-001sxm-1q for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Feb 2026 11:11:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vqr5M-00DnqC-2Z for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Feb 2026 11:11:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vqr5M-00Dnq4-1A for pgsql-hackers@lists.postgresql.org; Fri, 13 Feb 2026 11:11:45 +0000 Received: from mail-dy1-x1341.google.com ([2607:f8b0:4864:20::1341]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vqr5K-00000000Qyx-2aIB for pgsql-hackers@lists.postgresql.org; Fri, 13 Feb 2026 11:11:44 +0000 Received: by mail-dy1-x1341.google.com with SMTP id 5a478bee46e88-2b7da62b487so1456268eec.1 for ; Fri, 13 Feb 2026 03:11:43 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770981102; cv=none; d=google.com; s=arc-20240605; b=jxIoudeatNb64punK3dsTa2VFXq5+ni/4VmjCX5v8G2Ly5Q1ILJhelqhYSyVUUv+ED yP8AkibbUrAFzaZwYRJJTk1h8ZPhSgDiSZX5ZtGCyeOnyszX3TvDVeBfq1/9kq/dviF1 UtJ2y9j5K9t91IiV2EmkCx/kE2iv+P34R+Ss6eUFmir0haNl4yYwtkceXVFTeZ90j1gn WZ+ZV2epf0uxxGP9sibOavf2jJoYazzRGo6jmcRiBxZKDnaoP/ARAn6T41/6wnz+r6A6 SlborRG2wsrwN3QaM8MQy+jNM1cMuOCgxI2rDxrW5Gq6757I1oUK179IdFOgCPMSb53+ 2lIA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=GXfK8n5qJH3JyZARZWsTZ9vzKTRPj2DGOxymwB41DM4=; fh=rkzRfvRlI6v/CHclOLbByMAEZrhv7upY2L2CBquVBEU=; b=TWJ7glE5wYgLXbqi6HLyvEuJOM9gaS3JJ+aKG+do/M6O4t2qX0+xgFg8f5rZi4iifz HlN5XbKkjNa9G8DK68C8vyHkK2IK8O1Inj+cDb4JT6TQEk89kwIS0svauxkWKQ3n1SCX uj/fblgKjHv9GoQUry0oKhgfzvkdu1tL5tP+NBi9bdSyDvtyhPd8UFtVHfBa2DT1F0tj MegjR17Zukmt3oQ6fepGitoSvHB+2nb0Rx+Ux7UaeoFgzdDECrPNeAhuAZPor1mBjMqR A2upF8120O5xtaslVlz0w8tO5agO3eeqL4XxE2HtnYFJVeF3BHOy60C/BBQOYWiCxi/T /Fyg==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770981102; x=1771585902; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GXfK8n5qJH3JyZARZWsTZ9vzKTRPj2DGOxymwB41DM4=; b=cUY1pTFXNK/IbN5ViQnujaXM67wXg1jTuT+Ddp/4NkenGR8mMUnlOmFkb1uryfN+3U vvPrn3H6mXz4J9Pgm3P8FvBxk1qGmbEB+n1IZQD59vNA7IqTudVHFAVJ177aVIXjCsVw E/t7ox9e5vJkOtESdU4CF42QFPHagrkmrJwhS4dZVuDAUdWZk441UqyHZjwA5qIrNjzC ItnfRPxzI6XDD/7kcTcrwkLRXc9wBaKqODuRBbYTPo/sGtH1bpJibc0VmRiJFrcZbZdg u3g2XqR0gys1UZRB7KEbxJUvxJIiekLg30RdAzXpitEYpvltIT+anwiQXaKTDrNsqHGg IKzA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770981102; x=1771585902; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=GXfK8n5qJH3JyZARZWsTZ9vzKTRPj2DGOxymwB41DM4=; b=iqVnHScNl+tFkSikzOzQzVilRys+lrZjacRGnuZjpH0MQCz2Vkpw/9XIQ+C6QViznY 3iB2y8jGW1IfeU5v+m/cUdZYFV00lQaSl4G5AwWguHs5PIDbVbbNBQqWp2MaRvyEgcRO YHAtpeTVG+pPvKZVolpwKcYsOTGQq6D/zygJJHkTVnX4Y7yqoR3e0/4OBiGCIO7fwvHU E8qmfSC4YF9aeZNKe9psFd/jqgKET456JHd/uB98pDHplqaR5YubwGrH0QszAJkmP+th kLWsF7FdQTExAADYhbBGPsbdaIdBEwqwSFQq57ylLYxUFPt6rSJN+e5HbYxfc5r6HU5b AONw== X-Forwarded-Encrypted: i=1; AJvYcCW5DP7a8HuVbU+XPHYjt7xTOs2cOW+hGplB8pJJd0JgY8p+M/8KwWFoJjhydoSVcFRLrtNMED1kbDypojXF@lists.postgresql.org X-Gm-Message-State: AOJu0Yyw3bpSzqUz9zlHXVkiNhKqQqggwpdxacihb2mrlEUmN3N4XGzW D0tyqyjf958mWP0UVFpm4AXPLr7Yr/DM5FQPumInDF5S3fNnovJWJCx66ovaNXZIy4kL5UKds5m ytyXQ/oc6f6mrBiE0Z4TihD1jhKNLWfI= X-Gm-Gg: AZuq6aKLtRmDLK6/SLVvlWuDYZijz4O579V3/xgZJ67MNAhANXGGaw+UULgl35tZ3OF cPtls2ivVRY80hwFB/EV/ziiSzHMiLxseE2DSz9UFaPOMpzF0pnhRjcyxCE6RqOcWVTkW94yg+a Zxck2bwN9e3bCwBZ+rQaksM1P8FbIum2IZdypuvZx9lAQOTaZaukHvTcS41ATBqM5XUIzmeeasV zBp0BBeewaDzhhzY1BeoKTf172DqycS3h7p0yijQykRQh5bVIF2TTXPLhwo5LIV+DdLnHj9TtVh qJi8osY= X-Received: by 2002:a05:7300:fd11:b0:2ba:8f47:fe4f with SMTP id 5a478bee46e88-2babc4bd2c4mr546164eec.18.1770981102436; Fri, 13 Feb 2026 03:11:42 -0800 (PST) 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> <938E2286-9B0D-4F8D-A916-8E0E35D55034@Outlook.com> <982de4a4-71b6-4d1d-afe2-35b1c5d43529@vondra.me> In-Reply-To: From: lakshmi Date: Fri, 13 Feb 2026 16:44:25 +0530 X-Gm-Features: AZwV_Qg_tXs1Jqq__KK1LvTMTwvw6OMlnsOsp-j4OOH_RsYQh_g7V8e5Jc48QnA Message-ID: Subject: Re: Add a greedy join search algorithm to handle large join problems To: Pavel Stehule Cc: Tomas Vondra , John Naylor , Chengpeng Yan , "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000083c5b9064ab2ab3e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000083c5b9064ab2ab3e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable HI all, I tested the latest GOO patch (v4) on a fresh build from the current PostgreSQL master. The patch applied cleanly, the server built without issues, and regression tests passed except for the expected EXPLAIN output differences due to the new join ordering behavior. As a quick sanity check, I compared DP, GEQO, and GOO on a small multi-join query: DP planning: ~0.66 ms GEQO planning: ~2.28 ms GOO planning: ~0.38 ms Execution times were similar across all three (~1.5=E2=80=931.7 ms) with no correctness issues. Even on a small join, GEQO shows higher planning overhead, while GOO plans faster with comparable execution cost. I then evaluated scaling using synthetic 15-table and 20-table joins with EXPLAIN (ANALYZE, TIMING OFF): 15 tables DP: ~22.9 ms | ~23.4 ms GEQO: ~46.7 ms | ~20.5 ms GOO: ~1.8 ms | ~22.4 ms 20 tables DP: ~48.1 ms | ~30.5 ms GEQO: ~51.0 ms | ~26.7 ms GOO: ~3.2 ms | ~29.0 ms Planning time increases notably for DP and remains relatively high for GEQO, while GOO stays very low even at 20 joins, indicating substantially reduced planning overhead. Execution costs remain broadly comparable, with no obvious regressions from GOO in this synthetic workload. Although this uses a controlled synthetic join graph rather than JOB/TPC-H, the scaling behavior appears consistent with GOO=E2=80=99s goal of signific= antly cheaper planning than DP/GEQO while preserving similar plan quality. I plan to continue testing with more realistic workloads and will share further results if anything notable appears. Thanks for the interesting work. Regards, Lakshmi On Fri, Feb 13, 2026 at 12:33=E2=80=AFPM Pavel Stehule wrote: > > > =C4=8Dt 11. 12. 2025 v 18:07 odes=C3=ADlatel Tomas Vondra napsal: > >> On 12/11/25 07:12, Pavel Stehule wrote: >> > >> > >> > =C4=8Dt 11. 12. 2025 v 3:53 odes=C3=ADlatel John Naylor > > > napsal: >> > >> > On Wed, Dec 10, 2025 at 5:20=E2=80=AFPM Tomas Vondra > > > wrote: >> > > 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 t= he >> > plans >> > > are comparable or better. But it surprised me switching to geqo >> > makes it >> > > slower than master. That goes against my intuition that geqo is >> > meant to >> > > 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 t= he >> > tested model are really complex. >> > >> >> Depends on what you mean by "really complex". TPC-DS queries are not >> trivial, but the complexity may not be in the number of joins. >> >> Of course, setting geqo_threshold to 2 may be too aggressive. Not sure. >> > > I checked the TPC-H queries and almost all queries are simple - 5 x JOIN > -- 2x nested subselect > > >> >> >> regards >> >> -- >> Tomas Vondra >> >> --00000000000083c5b9064ab2ab3e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
HI all,
I tested the latest GOO patch (v4) on a fresh = build from the current PostgreSQL master. The patch applied cleanly, the se= rver built without issues, and regression tests passed except for the expec= ted EXPLAIN output differences due to the new join ordering behavior.
As a quick sanity check, I compared DP, GEQO, and GOO on a small multi-jo= in query:

=C2=A0 =C2=A0 =C2=A0DP planning: ~0.66 ms
=C2=A0 =C2=A0= =C2=A0GEQO planning: ~2.28 ms
=C2=A0 =C2=A0 =C2=A0GOO planning: ~0.38 m= s
Execution times were similar across all three (~1.5=E2=80=931.7 ms) wi= th no correctness issues. Even on a small join, GEQO shows higher planning = overhead, while GOO plans faster with comparable execution cost.
I then = evaluated scaling using synthetic 15-table and 20-table joins with EXPLAIN = (ANALYZE, TIMING OFF):
=C2=A0 =C2=A0 =C2=A015 tables
=C2=A0 =C2=A0 = =C2=A0DP: ~22.9 ms | ~23.4 ms
=C2=A0 =C2=A0 =C2=A0GEQO: ~46.7 ms | ~20.5= ms
=C2=A0 =C2=A0 =C2=A0GOO: ~1.8 ms | ~22.4 ms

=C2=A0 =C2=A0 =C2= =A0 20 tables
=C2=A0 =C2=A0 =C2=A0 DP: ~48.1 ms | ~30.5 ms
=C2=A0 =C2= =A0 =C2=A0GEQO: ~51.0 ms | ~26.7 ms
=C2=A0 =C2=A0 =C2=A0GOO: ~3.2 ms | ~= 29.0 ms

Planning time increases notably for DP and remains relativel= y high for GEQO, while GOO stays very low even at 20 joins, indicating subs= tantially
reduced planning overhead. Execution costs remain broadly comp= arable, with no obvious regressions from GOO in this synthetic workload.
Although this uses a controlled synthetic join graph rather than JOB/T= PC-H, the scaling behavior appears consistent with GOO=E2=80=99s goal of si= gnificantly cheaper planning than DP/GEQO while preserving similar plan qua= lity.

I plan to continue testing with more realistic workloads and w= ill share further results if anything notable appears.

Thanks for th= e interesting work.

Regards,
Lakshmi

O= n Fri, Feb 13, 2026 at 12:33=E2=80=AFPM Pavel Stehule <pavel.stehule@gmail.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">


=C4=8Dt 11. 12. 2025 v=C2=A018:07 odes=C3=ADlatel Tomas Vondra= <tomas@vondra.me> napsal:
O= n 12/11/25 07:12, Pavel Stehule wrote:
>
>
> =C4=8Dt 11. 12. 2025 v=C2=A03:53 odes=C3=ADlatel John Naylor <johncnaylorls@gmail= .com
> <mailto:johncnaylorls@gmail.com>> napsal:
>
>=C2=A0 =C2=A0 =C2=A0On Wed, Dec 10, 2025 at 5:20=E2=80=AFPM Tomas Vondr= a <tomas@vondra.me<= /a>
>=C2=A0 =C2=A0 =C2=A0<mailto:tomas@vondra.me>> wrote:
>=C2=A0 =C2=A0 =C2=A0> I did however notice an interesting thing - ru= nning EXPLAIN on the 99
>=C2=A0 =C2=A0 =C2=A0> queries (for 3 scales and 0/4 workers, so 6x 9= 9) took this much time:
>=C2=A0 =C2=A0 =C2=A0>
>=C2=A0 =C2=A0 =C2=A0> master:=C2=A0 =C2=A0 =C2=A0 =C2=A08s
>=C2=A0 =C2=A0 =C2=A0> master/geqo: 20s
>=C2=A0 =C2=A0 =C2=A0> master/goo:=C2=A0 =C2=A05s
>
>=C2=A0 =C2=A0 =C2=A0> It's nice that "goo" seems to be= faster than "geqo" - assuming the
>=C2=A0 =C2=A0 =C2=A0plans
>=C2=A0 =C2=A0 =C2=A0> are comparable or better. But it surprised me = switching to geqo
>=C2=A0 =C2=A0 =C2=A0makes it
>=C2=A0 =C2=A0 =C2=A0> slower than master. That goes against my intui= tion that geqo is
>=C2=A0 =C2=A0 =C2=A0meant to
>=C2=A0 =C2=A0 =C2=A0> be cheaper/faster join order planning. But may= be I'm missing
>=C2=A0 =C2=A0 =C2=A0something.
>
>=C2=A0 =C2=A0 =C2=A0Yeah, that was surprising. It seems that geqo has a= large overhead, so
>=C2=A0 =C2=A0 =C2=A0it takes a larger join problem for the asymptotic b= ehavior to win over
>=C2=A0 =C2=A0 =C2=A0exhaustive search.
>
>
> If I understand correctly to design - geqo should be slower for any > queries with smaller complexity. The question is how many queries in t= he
> tested model are really complex.
>

Depends on what you mean by "really complex". TPC-DS queries are = not
trivial, but the complexity may not be in the number of joins.

Of course, setting geqo_threshold to 2 may be too aggressive. Not sure.
=

I checked the TPC-H queries and almost all= queries are simple - 5 x JOIN -- 2x nested subselect
=C2=A0


regards

--
Tomas Vondra

--00000000000083c5b9064ab2ab3e--