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

From: Jerry Brenner <[email-masked]>
Date: Thu, 17 Jul 2025 18:09:50 -0700

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.

One more data point - each of the individual query plans is executed in a
distinct time range (during the customer's test).  (No environment changes
between these time ranges.)  The only overlap was with one of the queries
where a single execution of a generic plan occurred during the time range
that a different plan was being executed. (The generic plan was the fastest
plan out of all of the plans.)

---

From: Nikolay Samokhvalov <[email-masked]>
Date: Thu, 17 Jul 2025 18:43:56 -0700

On Thu, Jul 17, 2025 at 18:11 Jerry Brenner <[email-masked]> wrote:

> 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'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

---

From: Tom Lane <[email-masked]>
Date: Thu, 17 Jul 2025 21:57:45 -0400

> 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

---

From: Jerry Brenner <[email-masked]>
Date: Thu, 17 Jul 2025 19:30:27 -0700

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-masked]> wrote:

> set

---

From: Tom Lane <[email-masked]>
Date: Thu, 17 Jul 2025 23:10:21 -0400

> and then the plan changes, never to change back, match the expectation with

[ shrug... ]  Insufficient information.  There could be some plan
caching going on that contributes to this effect, though.

			regards, tom lane

---

From: Jerry Brenner <[email-masked]>
Date: Fri, 18 Jul 2025 06:20:03 -0700

We just jdbc and bind variables, so we are using PreparedStatements.
plan_cache_mode is set to auto

So, it sounds like there could be plan caching. (I wasn't aware of that.)
Is there any kind of running counter in a system view that tracks the
number of executions of cached plans?
We are capturing the plans via auto_explain and limited to the explain
options available with that path. Is there anything in the plan that would
tell us if the execution used a cached plan? (My manual explains does not
use a prepare.)

Thanks, Jerry