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
