Re: Skipping NULL keys when uniqueifying a semijoin's RHS

On Mon, Aug 31, 2026 at 2:46 AM William Bernbaum <wbernbaum(at)dwdev(dot)com> wrote:
>
> Hi Ilmari,
>
> Thanks for the review - v2 attached.
>
> > [use foreach_node for the loop over join_info_list]
>
> Done.
>
> > [and for the loop over semi_rhs_exprs]
>
> This one I couldn't take as written, so I used foreach_ptr
> instead.
>
> Two further changes:
>
> First, I dropped this guard:
>
> /* Nothing reads the whole RHS unless it can be unique-ified */
> if (!sjinfo->semi_can_btree && !sjinfo->semi_can_hash)
> continue;
>
> The check was unreachable. compute_semijoin_info() assigns
> sjinfo->semi_rhs_exprs only after it has already returned early on
> !(all_btree || all_hash), so semi_rhs_exprs is NIL whenever both flags
> are false.
>
> Second, I added a strictness check:
>
> /* A non-strict operator can match a NULL key */
> if (!op_strict(opno))
> continue;
>
> compute_semijoin_info() requires each operator to be hashjoinable or
> mergejoinable, but nothing requires it to be strict.
>
> Thanks,
> Will

I wonder whether this work should converge with Richard Guo’s per-RelOptInfo
UniqueKey work, rather than introducing a separate notion of
deduplication eligibility.

The UniqueKey work already treats uniqueness as a planner property, much
like pathkeys do for ordering, and propagates it through joins and upper
rels. It also handles the important NULL-awareness distinction: a key
may remain useful for proving inner uniqueness even when outer joins have
made it insufficient for removing DISTINCT or GROUP BY.

https://www.postgresql.org/message-id/flat/CAMbWs4-iLcqBr_n_F5gNrzQbBMrKgkpGwqTu7boWeoYepf%3D%2B8g%40mail.gmail.com

That seems closely related to what is needed here. An ordinary inner
join may destroy an input’s uniqueness by multiplying rows, while a
semijoin preserves the LHS uniqueness properties. Eager deduplication
can also naturally be described in terms of the uniqueness properties it
produces.

So rather than having separate machinery for UniqueKeys, eager
deduplication, and semijoin eligibility, I think it would be worth
considering whether these should converge on the same planner property
framework. That may also make future costing and transformations less
ad-hoc.

Regards
Haibo