MIME-Version: 1.0
References: 
 <CACoKFYRSOZFA0m33BCDHBkget30sY71hMwH9peHvTKxcV6Ha4A@mail.gmail.com>
 <1761501.1752803865@sss.pgh.pa.us>
In-Reply-To: <1761501.1752803865@sss.pgh.pa.us>
From: Jerry Brenner <jbrenner@guidewire.com>
Date: Thu, 17 Jul 2025 19:30:27 -0700
Message-ID: 
 <CACoKFYQLSkeFwytFFaKXQBo3U79zTEoe5z6t24dTGEuKcpb9-Q@mail.gmail.com>
Subject: Re: Is there a way to identify a plan generated by GECO?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f8779e063a2aeb27"
Archived-At: 
 <https://www.postgresql.org/message-id/CACoKFYQLSkeFwytFFaKXQBo3U79zTEoe5z6t24dTGEuKcpb9-Q%40mail.gmail.com>
Precedence: bulk

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

Thanks for the quick response!
I don't have any background with the randomized search.  Does the repeated
pattern with the same plan being executed multiple times in a time range
and then the plan changes, never to change back, match the expectation with
the randomization?

Thanks,
Jerry

On Thu, Jul 17, 2025 at 7:07=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jerry Brenner <jbrenner@guidewire.com> writes:
> > We are on Postgres 15.5 (Aurora)  and capturing query plans via
> > auto_explain.  We are seeing a large number of query plans for 2 querie=
s
> > that have 12 tables.  Every fast (or "fast enough") plan has a left dee=
p
> > tree and every slow plan has a bushy tree.  Is there a way to determine
> if
> > a plan was generated by GECO?
>
> > We have from_collapse_limit, join_collapse_limit and geqo_threshold all
> set
> > to 12.
>
> If there are 12 tables used in the query, then setting the collapse
> limits to 12 would allow the join search to be collapsed into a single
> problem, and then it would *always* go to GEQO because we invoke GEQO
> if the join problem involves >=3D geqo_threshold tables.  You might want
> to rethink having those settings equal to each other.
>
> > I've manually explained plans and haven't seen the problem,
>
> My guess is that GEQO usually finds one of the better plans, but
> when its randomized search is particularly unlucky it fails to.
> Try bumping geqo_threshold to more than 12, and note whether that
> results in unacceptable planning time for these queries.  If not,
> leave it at the higher value.
>
>                         regards, tom lane
>
>

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

<div dir=3D"ltr">Thanks for the quick response!<div>I don&#39;t have any ba=
ckground with=C2=A0the randomized search.=C2=A0 Does the repeated pattern w=
ith the same plan being executed multiple times in a time range and then th=
e plan changes, never to change back, match the expectation with the random=
ization?</div><div><br></div><div>Thanks,</div><div>Jerry</div></div><br><d=
iv class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gm=
ail_attr">On Thu, Jul 17, 2025 at 7:07=E2=80=AFPM Tom Lane &lt;<a href=3D"m=
ailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<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">Jerry Brenner &lt;<a href=3D"mail=
to:jbrenner@guidewire.com" target=3D"_blank">jbrenner@guidewire.com</a>&gt;=
 writes:<br>
&gt; We are on Postgres 15.5 (Aurora)=C2=A0 and capturing query plans via<b=
r>
&gt; auto_explain.=C2=A0 We are seeing a large number of query plans for 2 =
queries<br>
&gt; that have 12 tables.=C2=A0 Every fast (or &quot;fast enough&quot;) pla=
n has a left deep<br>
&gt; tree and every slow plan has a bushy tree.=C2=A0 Is there a way to det=
ermine if<br>
&gt; a plan was generated by GECO?<br>
<br>
&gt; We have from_collapse_limit, join_collapse_limit and geqo_threshold al=
l set<br>
&gt; to 12.<br>
<br>
If there are 12 tables used in the query, then setting the collapse<br>
limits to 12 would allow the join search to be collapsed into a single<br>
problem, and then it would *always* go to GEQO because we invoke GEQO<br>
if the join problem involves &gt;=3D geqo_threshold tables.=C2=A0 You might=
 want<br>
to rethink having those settings equal to each other.<br>
<br>
&gt; I&#39;ve manually explained plans and haven&#39;t seen the problem,<br=
>
<br>
My guess is that GEQO usually finds one of the better plans, but<br>
when its randomized search is particularly unlucky it fails to.<br>
Try bumping geqo_threshold to more than 12, and note whether that<br>
results in unacceptable planning time for these queries.=C2=A0 If not,<br>
leave it at the higher value.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
</blockquote></div>

--000000000000f8779e063a2aeb27--