Re: pg_plan_advice

On Thu, Dec 18, 2025 at 8:36 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> What must be happening here is that either pgpa_join.c (maybe with
> complicity from pgpa_walker.c) is not populating the
> pgpa_plan_walker_context's join_strategies[JSTRAT_NESTED_LOOP_PLAIN]
> member correctly, or else pgpa_output.c is not serializing it to text
> correctly. I suspect the former is a more likely but I'm not sure
> exactly what's happening.

I think I see the problem: pgpa_process_unrolled_join() returns a set
called "all_relids" but it only returns the union of the inner relid
sets, not including the outer relid set. In your example, we want to
get:

NESTED_LOOP_PLAIN((part partsupp) (supplier part partsupp))

But the join order is:

JOIN_ORDER(nation (supplier (part partsupp)))

So every table is the outer table of some unrolled join, except for
the innermost table, which is partsupp. So all the others get omitted
from the output, and we get the output you saw:

NESTED_LOOP_PLAIN(partsupp partsupp)

Proposed fix attached.

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