Re: pg_plan_advice: add NO_ scan and join method tags

Hi,

On 28/07/2026 08:43, song yanli wrote:
>
> Regarding the semantics of NO_ tags:
> NO_HASH_JOIN((a b)) means a hash join cannot be used when the join 
> product of a and b appears on the inner side.
>
> Consider the following scenario:
> SET pg_plan_advice.advice = 'JOIN_ORDER(t4 ((t2 t3) t1)) 
> NO_HASH_JOIN((t1 t2))';
>
> pgpa_join_method_permits_join() matches the set of inner relations 
> against the target.
> The result is ITM_TARGETS_ARE_SUBSET and restrict_method=false.
> When inner={a,b,c}, a and b are indeed together on the inner side as 
> part of a larger join product.
> Per the intended semantics, the restriction from the NO_ tag should 
> take effect: a hash join would be used with an inner side containing 
> {a,b}.
>
> But the current implementation only enforces the constraint for 
> ITM_EQUAL and skips the ITM_TARGETS_ARE_SUBSET case.
>

I tested this using  JOIN_ORDER(t4 (t3 (t1 t2)))  with and without
NO_HASH_JOIN((t1 t2)):

- Where the inner side is exactly {t1,t2} (ITM_EQUAL), the tag fires:
   Hash Join → Merge Join there.
- Where the inner side is {t1,t2,t3} (ITM_TARGETS_ARE_SUBSET), the Hash Join
   stays, and EXPLAIN (PLAN_ADVICE) reports the tag as /* matched */.

Per the docs, HASH_JOIN((a b))/NO_HASH_JOIN((a b)) both refer to the join
product of exactly a and b — not any join whose inner side happens to 
contain
them plus other relations. pgpa_join_method_permits_join() is shared 
betwen the
positive and negative tags precisely to keep "NO_ is the logical 
complement of
the positive tag" true; enforcing the NO_ form more broadly tahn the 
positive
form's own matching scope would break that symmetry. The code comment 
says it
directly: for the TARGETS_ARE_SUBSET case, "HASH_JOIN((x y)) doesn't 
restrict
how x and y can be joined" — the join event being controlled is specifically
the one where {a,b} becomes someone else's inner side, not any join that 
merely
contains them.
On your exact example, JOIN_ORDER(t4 ((t2 t3) t1)) NO_HASH_JOIN((t1 t2)):
this isn't a silent bypass. That specific order requires joining t1 to 
the already
combined (t2 t3), which splits the {t1,t2} target across sides (t2 
merges with
outsider t3 before t1 and t2 join each other), so NO_HASH_JOIN's own 
join-order
logic denies that pairing, right where JOIN_ORDER demands it. Both tags come
back marked conflicting in the advice output, and the permit wins, so 
the plan
still follows your requested order.

>
> I think this may be an issue.
>

If you think this should be changed I think it's a design change that 
should be
discussed on a separate thread, what do you think?

Cheers,
Florin
www.enterprisedb.com