From: Florin Irion <irionr@gmail.com>
To: song yanli <songyanlili@outlook.com>
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: robertmhaas@gmail.com <robertmhaas@gmail.com>
Subject: Re: pg_plan_advice: add NO_ scan and join method tags
Date: Tue, 28 Jul 2026 11:53:14 +0200
Message-ID: <9804d83b-b371-48ed-874e-e506466e0db9@gmail.com> (raw)
In-Reply-To: <PUVP216MB14281543DEC8C289842AB6D101BFCB2@PUVP216MB142815.KORP216.PROD.OUTLOOK.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: irionr@gmail.com, songyanlili@outlook.com, jonathan.abdiel@gmail.com, pgsql-hackers@lists.postgresql.org, robertmhaas@gmail.com
Subject: Re: pg_plan_advice: add NO_ scan and join method tags
In-Reply-To: <9804d83b-b371-48ed-874e-e506466e0db9@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox