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