On Tue, Jan 13, 2026 at 1:48 PM Jacob Champion <[email protected]> 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
