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

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