On Tue, Jan 13, 2026 at 1:48 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
> The first thing found with the new architecture is this:
>
> -- note that f is not a partitioned table
> SET pg_plan_advice.advice = 'join_order(f/e (f d))';
> EXPLAIN (COSTS OFF, PLAN_ADVICE)
> SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id;
> ERROR: cannot determine RTI for advice target
>
> Test, and a quick guess at expected output, attached.
Thanks. There are two separate bugs here. One is that
pgpa_walker_get_rti() is completely wrong-headed in thinking that only
system-generated advice should reach that function, and therefore that
it doesn't need to deal with 0 return values from
pgpa_compute_rti_from_identifier(). I've deleted pgpa_walker_get_rti()
and made the code that called it instead call
pgpa_compute_rti_from_identifier() and deal with 0 return values. That
revealed a second bug, which is that it thought that the
join_order(f/e (f d)) advice was fully matched, despite f/e not
existing in the query. That turns out to be because
pgpa_join_order_permits_join() was doing entry->flags |=
PGPA_TE_MATCH_FULL even when processing a sublist -- so the fact that
it found (f d) in the query made it think that it had matched the
entire join order specification, when in reality it had only matched
the entirety of a sublist. With that fixed, plan_advice.advice =
'join_order(f/d1 (d1 d2))' produces this:
+ Nested Loop
+ Disabled: true
+ Join Filter: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
+ -> Nested Loop
+ -> Seq Scan on jo_dim1 d1
+ Filter: (val1 = 1)
+ -> Materialize
+ -> Seq Scan on jo_dim2 d2
+ Filter: (val2 = 1)
+ -> Seq Scan on jo_fact f
+ Supplied Plan Advice:
+ JOIN_ORDER(f/d1 (d1 d2)) /* partially matched */
+ Generated Plan Advice:
+ JOIN_ORDER(d1 d2 f)
+ NESTED_LOOP_PLAIN(f)
+ NESTED_LOOP_MATERIALIZE(d2)
+ SEQ_SCAN(d1 d2 f)
+ NO_GATHER(f d1 d2)
This is because we don't have a global view of whether the join order
is valid. The planner works up from the bottom of the plan tree and
sees that joining f to d1 or d2 first contradicts the (d1 d2) portion
of the JOIN_ORDER advice, so the first join that gets done is the
d1-d2 join, which is not disabled. Joining the result to f also
contradicts the JOIN_ORDER advice, but there's no alternative to
consider so the planner picks the disabled path as the only option.
I was hoping to get a new version of the patch set with fixes for
these issues out today, but I've run out of day, so I'll have to come
back to that, hopefully tomorrow.
--
Robert Haas
EDB: http://www.enterprisedb.com
