Re: pg_plan_advice

On Fri, Mar 27, 2026 at 2:00 AM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
> I could not reproduce recent failures from skink and morepork so far, but
> I found that changing some GUCs can trigger similar warnings, e.g.:

Thanks for the reports.

> echo "geqo_threshold = 8" >/tmp/extra.config

Failures here are expected. When planning is done via GEQO, not all
join orders are explored, and pg_plan_advice can only constrain the
join order from among the options considered by the planner. So, with
GEQO + test_plan_advice, any given test is going to pass if the second
round of planning considers the join order chosen by the first, and
fail otherwise.

This could be improved at some point in the future. For example,
somebody could add hooks to GEQO so that pg_plan_advice can cause it
to generate only candidates which are consistent with the supplied
advice. In practice, I'm not sure this is going to be a good use of
time. I suspect the energy would be better invested in improving GEQO
or coming up with a more useful replacement. The gap that exists here
doesn't mean that you can't use pg_plan_advice with GEQO; it only
means that you are going to have a bad time using them together if you
provide *complete* (or nearly-complete) plan advice.

> echo "join_collapse_limit = 1000" >/tmp/extra.config

The cause here actually seems to be GEQO once again. Raising the
join_collapse_limit causes some join problems to get bigger, which has
the result that they then use GEQO. At least for me, if I also bump up
geqo_threshold, the failures go away.

> and an assertion failure with:
> enable_parallel_append = off
> enable_partitionwise_aggregate = on
> cpu_tuple_cost = 1000
>
> TRAP: failed Assert("relids != NULL"), File: "pgpa_scan.c", Line: 248, PID: 1956762

Obviously, this one's a bug. I think the attached should fix it.

--
Robert Haas
EDB: http://www.enterprisedb.com