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 <t...@sss.pgh.pa.us> wrote: > Jerry Brenner <jbren...@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 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 > >
