Message-ID: <9804d83b-b371-48ed-874e-e506466e0db9@gmail.com>
Date: Tue, 28 Jul 2026 11:53:14 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_plan_advice: add NO_ scan and join method tags
To: song yanli <songyanlili@outlook.com>,
 "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>
Cc: "pgsql-hackers@lists.postgresql.org"
 <pgsql-hackers@lists.postgresql.org>,
 "robertmhaas@gmail.com" <robertmhaas@gmail.com>
References: <123bbb65-ae76-4112-b33d-864aca745b7b@gmail.com>
 <87qzl5pxuz.fsf@gmail.com> <9ba4c7da-06ee-4a5a-9479-9b1076cfc675@gmail.com>
 <PUVP216MB14281543DEC8C289842AB6D101BFCB2@PUVP216MB142815.KORP216.PROD.OUTLOOK.COM>
Content-Language: en-US
From: Florin Irion <irionr@gmail.com>
In-Reply-To: 
 <PUVP216MB14281543DEC8C289842AB6D101BFCB2@PUVP216MB142815.KORP216.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9804d83b-b371-48ed-874e-e506466e0db9%40gmail.com>
Precedence: bulk

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