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 1vTkYk-009sK4-2B for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Dec 2025 17:34:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vTkYj-004jHS-1R for pgsql-hackers@arkaria.postgresql.org; Thu, 11 Dec 2025 17:34:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vTkYj-004jHJ-0M for pgsql-hackers@lists.postgresql.org; Thu, 11 Dec 2025 17:34:33 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vTkYh-000CfM-1n for pgsql-hackers@lists.postgresql.org; Thu, 11 Dec 2025 17:34:33 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-78a76afeff6so4058827b3.0 for ; Thu, 11 Dec 2025 09:34:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1765474469; x=1766079269; 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=JMzDZAOgr8Vfz9CNQkdyTGfGM027DVT6jCbk34Av8DA=; b=EGM/yKOcx6dTkWhN0EBh2QAwb36Yqz8sO0piEC5p13uJmGhl2FYzE5hzE1slNaeSno ePVmveLb2nmKe+yDK8M9VSjAbyCj7MQxkhSPsFHZDE7uin9Ng9DfbpiTpvkhN8pC6Kmn Bh9ihnJGh+dfHeqOZujUWlML3tZ6wJVCxm/xnfPXfF8/IMNBIBPpH5Yj+1YCVzL+rnt9 XNyasY8RkoT7pLr4hp9/8rDyoykyU6NJfZAdClxWNZZ3AuiPzbBif2gkmtD184UYzC4G sFx3Gu601ayrlZOgqeIyd8uEZUN/TU/xaPsP+6b1V3+o36CfwBVnl3XY0iVg+hNrFTmE 863g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1765474469; x=1766079269; 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=JMzDZAOgr8Vfz9CNQkdyTGfGM027DVT6jCbk34Av8DA=; b=DvZmXD/cBAhiqW+HzkL+TUi3vB/cR+XvmLcYA35op2G+YvcuNyxsf5As1bE6DU4U4p b/U4UpQMYjhSvugcJ9rc6PKRgl/pUvbYfsK//SVbIgxxMN2xX/48wk2vnB7OEZLNYVJr s58EvXnGJtNfE39pdpNBzcdR8mjJb/EeiXNQsHLvcNassIT1EPCviiZSPI0JhPK6zDcK o+gIXy3XaNjEZIEQGdOpiFTQ3X3V19Jq5kvHXc3cLhaMQT4zdwO7+y5jyOjDr9fDV9lm 5tiTVNcuLeOdnHhyxmJXQDK2XdW5epHl0Rsk4xE2iuRJnKxR7U/rDPJTZVL9n8mM15Yt ZbmQ== X-Forwarded-Encrypted: i=1; AJvYcCVpuSD6dshbfEmVeo+HmcTz7bl9dsxnu8miVWBXwNCmOeHxtMxhjXIPkx/81mXgC56Weq12dlINifV2wVP4@lists.postgresql.org X-Gm-Message-State: AOJu0YwbVDshxZyJBoUQU/QFBWoYKFpeJd4cqvRH/LFKe/NjHm/fHPn/ /LBDaLrLTZ/qkO6orAm6fqLZjvw6Z7tMkEj/6cQQlMx9QAXr6ZbSjHzmAut3dr/JHIrDHahMCTv kXzpPYv17nFsc70yEgQga07UGAm1+shw= X-Gm-Gg: AY/fxX69wrnJbpbcXHKgNjJR8tTYmXCu4jEYmdC3PPGDsY+LbpQSrcYPKSaNrDOVhyr lw4Ou9alH3YAyKSVUIFu08zNYNbuSOG6mZxY3geogt09qaoXRKzSmKChXb9OjIPY3BzacG9b9zY hydz9mLAoCARsnm/gIYPPd9QvI2hKNyq+EtPsC0Cw41b62q2z7Yu7UZEDTwwVakTuhBdBqY+Rtw vwvf5t+B3p7huNnxTRJu4FolrdLQ/KiLIADwpReKCsiwCX0dutBUs7EvVoq4V6X88u6C47zYAok eqzQR3vI5WpSbSKhYYTqdbGA5OZl62YJnKNeIRZEKWl+mS+GAyjt+2+8ydhlbYULLNt3n9CI0uu YD+BDgE64haVuSg== X-Google-Smtp-Source: AGHT+IEgutC+XIoK1ma9dDq3UxMsH8TCpnaPZUl8MyAc43cd7Xr4Hd3Q4eruQuci9RkrhuAaJaq9hOz8rkqIZiM8S0Y= X-Received: by 2002:a05:690e:428b:20b0:63f:bc75:6ead with SMTP id 956f58d0204a3-6446e935b74mr4733375d50.9.1765474469444; Thu, 11 Dec 2025 09:34:29 -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: <982de4a4-71b6-4d1d-afe2-35b1c5d43529@vondra.me> From: Pavel Stehule Date: Thu, 11 Dec 2025 18:33:52 +0100 X-Gm-Features: AQt7F2q6shUg-tIP4vtjlVDNMQ34W6-wVsMETgBDVIvthJT45KypNnCnopaOYIA Message-ID: Subject: Re: Add a greedy join search algorithm to handle large join problems To: Tomas Vondra Cc: John Naylor , Chengpeng Yan , "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000009c7f4a0645b08e20" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009c7f4a0645b08e20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =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 th= e > 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 th= e > > 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 th= e > > 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 > > --0000000000009c7f4a0645b08e20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=C4=8Dt 11. 12.= 2025 v=C2=A018:07 odes=C3=ADlatel Tomas Vondra <tomas@vondra.me> napsal:
On 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

--0000000000009c7f4a0645b08e20--