MIME-Version: 1.0
References: 
 <CACoKFYRSOZFA0m33BCDHBkget30sY71hMwH9peHvTKxcV6Ha4A@mail.gmail.com>
In-Reply-To: 
 <CACoKFYRSOZFA0m33BCDHBkget30sY71hMwH9peHvTKxcV6Ha4A@mail.gmail.com>
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Thu, 17 Jul 2025 18:43:56 -0700
Message-ID: 
 <CAM527d_cpe_mMrbO4OVLPfZGsF7ukLYA=dPAyeiy-D-smuukAQ@mail.gmail.com>
Subject: Re: Is there a way to identify a plan generated by GECO?
To: Jerry Brenner <jbrenner@guidewire.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000002d9e5063a2a4317"
Archived-At: 
 <https://www.postgresql.org/message-id/CAM527d_cpe_mMrbO4OVLPfZGsF7ukLYA%3DdPAyeiy-D-smuukAQ%40mail.gmail.com>
Precedence: bulk

--00000000000002d9e5063a2a4317
Content-Type: text/plain; charset="UTF-8"

On Thu, Jul 17, 2025 at 18:11 Jerry Brenner <jbrenner@guidewire.com> wrote:

> 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 queries
> that have 12 tables.  Every fast (or "fast enough") plan has a left deep
> 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.  (There is a COUNT(*) above derived table - could that be
> somehow affecting this?)
>
> I've manually explained plans and haven't seen the problem, but then it
> turns up the next day (with the same parameter values) with multiple
> execution plans.
>

I'm not aware of ability to see if grow was involved, but with Aurora,
should be able can provision a thin (CoW) clone with PITR to a specific
point when you suspect the plan in question was used ("slow") -- and study
the planner behavior in detail, experimenting and adjusting planner
parameters.

Nik

>

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

<div><br></div><div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gm=
ail_attr">On Thu, Jul 17, 2025 at 18:11 Jerry Brenner &lt;<a href=3D"mailto=
:jbrenner@guidewire.com">jbrenner@guidewire.com</a>&gt; wrote:<br></div><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(=
204,204,204)"><div dir=3D"ltr">We are on Postgres 15.5 (Aurora)=C2=A0 and c=
apturing query plans via auto_explain.=C2=A0 We are seeing a large number=
=C2=A0of query plans for 2 queries that have 12 tables.=C2=A0 Every fast (o=
r &quot;fast enough&quot;) plan has a=C2=A0left deep tree and every slow pl=
an has a bushy tree.=C2=A0 Is there a way to determine if a plan was genera=
ted by GECO?<div><br></div><div>We have from_collapse_limit, join_collapse_=
limit and geqo_threshold all set to 12.=C2=A0 (There is a COUNT(*) above de=
rived table - could that be somehow affecting this?)</div><div><br></div><d=
iv>I&#39;ve manually explained plans and haven&#39;t seen the problem, but =
then it turns up the next day (with the same parameter values) with multipl=
e execution plans.</div></div></blockquote><div dir=3D"auto"><br></div><div=
 dir=3D"auto">I&#39;m not aware of ability to see if grow was involved, but=
 with Aurora, should be able can provision a thin (CoW) clone with PITR to =
a specific point when you suspect the plan in question was used (&quot;slow=
&quot;) -- and study the planner behavior in detail, experimenting and adju=
sting planner parameters.</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">Nik</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-l=
eft-color:rgb(204,204,204)"><div dir=3D"ltr"><div dir=3D"auto"></div></div>
</blockquote></div></div>

--00000000000002d9e5063a2a4317--