Re: Fix CPU cost of right-semi and right-anti hash joins

On Thu, Aug 20, 2026 at 10:53 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Fri, Aug 21, 2026 at 6:53 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:
> > For example, in one test I measured:
> >
> > hash-clause candidate pairs: 1,000,000
> > joinqual evaluations: 1,000,000
> > v3 hashjointuples: 667
> >
> > So 667 can be a reasonable estimate of the unmatched inner rows while being a
> > very different population from the one on which the joinqual is actually evaluated.
>
> Nice catch.
>
> The way I see it now, a hash join has three kinds of quals:
> hashclauses, joinquals that are not used for hashing, and otherquals
> (pushed-down clauses). So there are four tuple counts: the bucket
> entries scanned, the tuples that pass the hashclauses, the tuples that
> pass all the joinquals, and the tuples that are emitted. Ideally each
> qual class would be charged on its own count.
>
> hashjointuples is the third count for all of the semi/anti join types.
> That's exactly right for otherquals and cpu_tuple_cost, but wrong for
> joinquals. For plain joins it is the second count, so there the
> charge is right for joinquals and pessimistic for otherquals. That is
> also what the comment below means:
>
> * ... (This is pessimistic since
> * not all of the quals may get evaluated at each tuple.)
>
> Note that this joinqual issue is not specific to JOIN_RIGHT_SEMI and
> JOIN_RIGHT_ANTI. JOIN_SEMI charges joinquals on the matched outer
> rows, which is only a lower bound on the evaluation count. JOIN_ANTI
> charges them on the unmatched rows, which is not even a lower bound,
> since each matched row must have taken at least one evaluation to be
> excluded.
>
> In the attached v4 I fixed the JOIN_RIGHT_ANTI case. It seems to be
> the only one that is easy to fix, as it evaluates its joinquals once
> per hashclause-passing tuple, with no short-circuit at all, so the
> matching-pair count is the right number. JOIN_SEMI, JOIN_ANTI, and
> JOIN_RIGHT_SEMI all short-circuit at the first match, so their
> evaluation counts depend on how the matches are distributed among the
> candidates, which we have no statistics for. It seems to me that we
> have to live with that imprecision for now.
>
> - Richard

This looks right to me. Splitting cpu_tuple_cost from the qual cost for
RIGHT_ANTI matches what I saw in the executor: every hash-clause-passing
candidate reaches ExecQual(joinqual), so the pair count is the right
population there.

One small testing point: the new hj_small/hj_large cases have no non-hash
joinqual, so qp_qual_cost.per_tuple is zero and they don’t exercise the new
v4 branch. It might be worth adding a small RIGHT_ANTI case with a hash
condition plus a Join Filter, ideally one where the candidate-pair count is
much larger than the unmatched-inner count.

Also the test comment saying cpu_tuple_cost and qp_qual_cost are both charged
on inner-side rows is no longer quite true for RIGHT_ANTI in v4.

Thanks,
Haibo