Re: Is there a way to identify a plan generated by GECO?

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jerry Brenner <jbrenner(at)guidewire(dot)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 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
>
>