public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jerry Brenner <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Is there a way to identify a plan generated by GECO?
Date: Thu, 17 Jul 2025 19:30:27 -0700
Message-ID: <CACoKFYQLSkeFwytFFaKXQBo3U79zTEoe5z6t24dTGEuKcpb9-Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACoKFYRSOZFA0m33BCDHBkget30sY71hMwH9peHvTKxcV6Ha4A@mail.gmail.com>
	<[email protected]>

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 PM Tom Lane <[email protected]> wrote:

> Jerry Brenner <[email protected]> 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 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.
>
> 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 >= 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
>
>

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Is there a way to identify a plan generated by GECO?
  In-Reply-To: <CACoKFYQLSkeFwytFFaKXQBo3U79zTEoe5z6t24dTGEuKcpb9-Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox