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

Hi Richard

On Mon, Aug 17, 2026 at 12:58 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:

The cpu_tuple_cost change looks right: RIGHT_SEMI/RIGHT_ANTI emit
inner-side rows, while hashjointuples is based on the tuples processed
by the hash join and can be much larger. The path LGTM

Thanks for the review.

Regarding back-patching, this fix can change plans, and plan changes
in a minor release are a problem for users. But maybe we can still
push it to v19?

- Richard

Hi,

On Mon, 17 Aug 2026 at 08:57, Richard Guo <guofenglinux@gmail.com> wrote:

While working on the UniqueKeys patch, I was chasing an unexpected
plan diff in the regression tests, and that led me to a costing bug
for right-semi and right-anti hash joins.

final_cost_hashjoin() charges a per-returned-row cost (cpu_tuple_cost)
on hashjointuples, which is always taken from the outer side. But
JOIN_RIGHT_SEMI and JOIN_RIGHT_ANTI emit inner rows rather than outer
ones, so for them that count is too large by roughly the ratio of the
outer side to the inner one. Those jointypes exist to hash the
smaller input and scan the larger one, so the overestimate is worst in
exactly the cases where they are the right choice.

Here is an example:

create table s (id int primary key, a int);
create table r (b int, c int);
insert into s select g, g from generate_series(1, 100) g;
insert into r select (g % 500000) + 1, g
from generate_series(1, 2000000) g;
vacuum analyze s, r;

set max_parallel_workers_per_gather = 0;
set work_mem = '64MB';

explain select s.a from s where exists
(select 1 from r where r.b = s.id);

On master this unique-ifies the RHS and hashes the result:

Hash Join (cost=45210.32..45213.69 rows=100 width=4)
Hash Cond: (s.id = r.b)
-> Seq Scan on s (cost=0.00..2.00 rows=100 width=8)
-> Hash (cost=38899.03..38899.03 rows=504903 width=4)
-> HashAggregate (cost=33850.00..38899.03 rows=504903 width=4)
Group Key: r.b
-> Seq Scan on r (cost=0.00..28850.00 rows=2000000
width=4)
(7 rows)

Execution Time: 1152.471 ms

The hash right semi join is considered but costs 56353.25, because
hashjointuples comes out as 2000000 (the entire RHS) for a join whose
own row estimate is 100. Dropping that error brings it to 36354.25,
and it wins:

Hash Right Semi Join (cost=3.25..36354.25 rows=100 width=4)
Hash Cond: (r.b = s.id)
-> Seq Scan on r (cost=0.00..28850.00 rows=2000000 width=4)
-> Hash (cost=2.00..2.00 rows=100 width=8)
-> Seq Scan on s (cost=0.00..2.00 rows=100 width=8)
(5 rows)

Execution Time: 395.206 ms

And this runs about 3x faster than master.

Thanks for the clear example.

Attached fix charges cpu_tuple_cost on the path's own row estimate for

these two jointypes. The qpquals are still evaluated once per tuple
that gets through the hashjoin, so those stay on hashjointuples.

Nestloop and mergejoin need no equivalent change: neither supports
JOIN_RIGHT_SEMI, nestloop doesn't support JOIN_RIGHT_ANTI either, and
final_cost_mergejoin() takes its count from approx_tuple_count(),
which multiplies the two input sizes and so does not depend on which
side is outer.

Note that there is a plan diff for an existing query in
select_parallel.sql. There the fix raises the estimate rather than
lowering it: approx_tuple_count() gives 50 while path->rows is 5000.
That row estimate is itself too high, but it is already wrong before
final_cost_hashjoin() sees it, and every other consumer believes it.
I verified that both plans run in the same time here, within noise, so
this patch just updates the expected output for it.

Any thoughts?

Thanks for the patch! The motivation and the right-semi case make
sense to me. I wonder about the right-anti case when an additional filter
removes some unmatched rows.

I tried a case with 1000 unmatched hash tuples where the filter allowed
only one through. The executor examined all 1000 and reported "Rows
Removed by Filter: 999", while path->rows was one. The patch reduced the
cost by 9.99, exactly 999 * cpu_tuple_cost.

Could using path->rows therefore undercharge the rows that were examined
but filtered out? Would the unmatched-row count before that filter be a
better multiplier for cpu_tuple_cost, or am I misunderstanding its intended
meaning here?

Regards,
Ayush

On Sun, Aug 16, 2026 at 8:27 PM Richard Guo <guofenglinux@gmail.com> wrote:

While working on the UniqueKeys patch, I was chasing an unexpected
plan diff in the regression tests, and that led me to a costing bug
for right-semi and right-anti hash joins.

final_cost_hashjoin() charges a per-returned-row cost (cpu_tuple_cost)
on hashjointuples, which is always taken from the outer side. But
JOIN_RIGHT_SEMI and JOIN_RIGHT_ANTI emit inner rows rather than outer
ones, so for them that count is too large by roughly the ratio of the
outer side to the inner one. Those jointypes exist to hash the
smaller input and scan the larger one, so the overestimate is worst in
exactly the cases where they are the right choice.

Here is an example:

create table s (id int primary key, a int);
create table r (b int, c int);
insert into s select g, g from generate_series(1, 100) g;
insert into r select (g % 500000) + 1, g
from generate_series(1, 2000000) g;
vacuum analyze s, r;

set max_parallel_workers_per_gather = 0;
set work_mem = '64MB';

explain select s.a from s where exists
(select 1 from r where r.b = s.id);

On master this unique-ifies the RHS and hashes the result:

Hash Join (cost=45210.32..45213.69 rows=100 width=4)
Hash Cond: (s.id = r.b)
-> Seq Scan on s (cost=0.00..2.00 rows=100 width=8)
-> Hash (cost=38899.03..38899.03 rows=504903 width=4)
-> HashAggregate (cost=33850.00..38899.03 rows=504903 width=4)
Group Key: r.b
-> Seq Scan on r (cost=0.00..28850.00 rows=2000000 width=4)
(7 rows)

Execution Time: 1152.471 ms

The hash right semi join is considered but costs 56353.25, because
hashjointuples comes out as 2000000 (the entire RHS) for a join whose
own row estimate is 100. Dropping that error brings it to 36354.25,
and it wins:

Hash Right Semi Join (cost=3.25..36354.25 rows=100 width=4)
Hash Cond: (r.b = s.id)
-> Seq Scan on r (cost=0.00..28850.00 rows=2000000 width=4)
-> Hash (cost=2.00..2.00 rows=100 width=8)
-> Seq Scan on s (cost=0.00..2.00 rows=100 width=8)
(5 rows)

Execution Time: 395.206 ms

And this runs about 3x faster than master.

Attached fix charges cpu_tuple_cost on the path's own row estimate for
these two jointypes. The qpquals are still evaluated once per tuple
that gets through the hashjoin, so those stay on hashjointuples.

Nestloop and mergejoin need no equivalent change: neither supports
JOIN_RIGHT_SEMI, nestloop doesn't support JOIN_RIGHT_ANTI either, and
final_cost_mergejoin() takes its count from approx_tuple_count(),
which multiplies the two input sizes and so does not depend on which
side is outer.

Note that there is a plan diff for an existing query in
select_parallel.sql. There the fix raises the estimate rather than
lowering it: approx_tuple_count() gives 50 while path->rows is 5000.
That row estimate is itself too high, but it is already wrong before
final_cost_hashjoin() sees it, and every other consumer believes it.
I verified that both plans run in the same time here, within noise, so
this patch just updates the expected output for it.

Any thoughts?

- Richard

Hi Richard,

Thanks for looking into this. I spent some more time on your reproducer and
instrumented both final_cost_hashjoin() and the hash join executor.

I agree with the underlying problem you found: the attractive right-semi plan
is badly overcosted on master. But I think the source of the bad cost may be
a little earlier than the final cpu_tuple_cost multiplier.

In the SEMI/ANTI/inner_unique branch of final_cost_hashjoin(), we currently
derive hashjointuples using the equivalent of:

outer_path_rows * extra->semifactors.outer_match_frac

For an ordinary SEMI/ANTI path those two quantities describe the same
orientation.

For JOIN_RIGHT_SEMI and JOIN_RIGHT_ANTI, however, the physical outer and inner
paths have been swapped. outer_path_rows therefore belongs to the physically
swapped probe side, while outer_match_frac was computed by
compute_semi_anti_join_factors() for the canonical semi/anti orientation.

So we can end up multiplying the row count of one relation by a match fraction
describing the other relation.

On your reproducer I measured approximately:

actual hash-clause candidates 400
approx_tuple_count() ~400
current hashjointuples 2,000,000
final output rows 100

So in this case the existing Branch-A estimate is off by roughly 5000x.

I tried fixing that estimate directly instead of changing the population used
by cpu_tuple_cost:

if (path->jpath.jointype == JOIN_RIGHT_SEMI ||
path->jpath.jointype == JOIN_RIGHT_ANTI)
hashjointuples =
approx_tuple_count(root, &path->jpath, hashclauses);
else if (path->jpath.jointype == JOIN_ANTI)
hashjointuples = outer_path_rows - outer_matched_rows;
else
hashjointuples = outer_matched_rows;

approx_tuple_count() is already used by the other branch of
final_cost_hashjoin() to estimate the hash-clause candidate-pair population.
Its pair-count formula is symmetric under swapping the two input row counts,
so it does not have the orientation mismatch above.

With this change I can leave the existing CPU costing formula unchanged:

(cpu_tuple_cost + qp_qual_cost.per_tuple) * hashjointuples

and your original example still switches to the desired Hash Right Semi Join.
On my setup master took about 169 ms with the HashAggregate-based plan, while
the patched right-semi plan took about 47 ms.

I see the same candidate-count problem for JOIN_RIGHT_ANTI; with the
corresponding
unique-build-side case the old estimate was again around 2,000,000 while both
approx_tuple_count() and the measured number of hash-clause candidates were
around 400.

One reason I prefer fixing hashjointuples at this point rather than changing
cpu_tuple_cost to use path->rows is the existing select_parallel.sql case you
mentioned.

There:

approx_tuple_count() = 50
path->rows = 5000

but that right-semi candidate has inner_unique = false, so it is already using
the general approx_tuple_count() branch. Those two values are estimating
different things: approx_tuple_count() is estimating the hash-clause
candidate-pair population, while path->rows is the final semijoin output
estimate.

With this alternative fix that test therefore remains unchanged from master;
there is no select_parallel.out change.

I also tried to stress the other direction. For the existing
inner_unique = false
branch, where hashjointuples is already obtained from approx_tuple_count(), I
could reproduce cases with an additional non-hash join qualification where
moving the cpu_tuple_cost contribution to path->rows makes a right-oriented
plan look cheaper and selects a measurably slower plan.
The clearer right-anti case was about 58 ms versus 48-50 ms for the ordinary
orientation. This patch leaves those Branch-B cases untouched.

Conversely, I tested the new Branch-A estimate with a deliberately extreme
right-semi case where the executor saw about 2,000,000 hash-clause candidates
but only 5 joinqual evaluations after the match-bit short circuit. The new
estimate is substantially higher there, as expected, but it did not change
the chosen plan and runtimes were indistinguishable from master and your v1
in my tests.

I’ve attached a standalone patch implementing this approach. It does not
depend on your patch. The regression tests cover both right-semi and
right-anti, including a sparse unique-build-side case intended to exercise
the orientation mismatch directly. I also checked the inner_unique = false
paths remain unchanged and ran the full regression schedule with assertions
enabled; 243/245 tests passed, with the two failures (int8 and numeric)
reproducing identically on an unpatched build in the same environment.

So my current thinking is that the overcosting you found is real, but the
narrower fix may be to correct the right-semi/right-anti candidate estimate
rather than reinterpret cpu_tuple_cost in terms of final output rows.

Does this match your understanding of what outer_match_frac is intended to
describe here?

Thanks,
Haibo

On Tue, Aug 18, 2026 at 6:22 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:

I tried a case with 1000 unmatched hash tuples where the filter allowed
only one through. The executor examined all 1000 and reported "Rows
Removed by Filter: 999", while path->rows was one. The patch reduced the
cost by 9.99, exactly 999 * cpu_tuple_cost.

Could using path->rows therefore undercharge the rows that were examined
but filtered out?

Yeah, you're right. path->rows is the row count after all the quals
have been applied, while the per-tuple charges here are meant to cover
the rows that come out of the hash-clause matching, before any other
quals filter them.

I think a more accurate way is to compute hashjointuples from the
inner side directly:

right-semi: inner_path_rows * outer_match_frac
right-anti: inner_path_rows * (1 - outer_match_frac)

This is mirroring what JOIN_SEMI and JOIN_ANTI already do with the
outer side. Both cpu_tuple_cost and the quals not used for hashing
are charged on that count, so the 1000 rows your executor examined are
all charged, and the filter's selectivity affects only the join's
output row estimate.

Attached v3 patch does that.

- Richard

On Wed, Aug 19, 2026 at 4:24 PM Haibo Yan <tristan.yim@gmail.com> wrote:

For JOIN_RIGHT_SEMI and JOIN_RIGHT_ANTI, however, the physical outer and inner
paths have been swapped. outer_path_rows therefore belongs to the physically
swapped probe side, while outer_match_frac was computed by
compute_semi_anti_join_factors() for the canonical semi/anti orientation.

I agree with this diagnosis. The problem is hashjointuples itself,
not just the cpu_tuple_cost multiplier.

I tried fixing that estimate directly instead of changing the population used
by cpu_tuple_cost:

if (path->jpath.jointype == JOIN_RIGHT_SEMI ||
path->jpath.jointype == JOIN_RIGHT_ANTI)
hashjointuples =
approx_tuple_count(root, &path->jpath, hashclauses);
else if (path->jpath.jointype == JOIN_ANTI)
hashjointuples = outer_path_rows - outer_matched_rows;
else
hashjointuples = outer_matched_rows;

approx_tuple_count() is already used by the other branch of
final_cost_hashjoin() to estimate the hash-clause candidate-pair population.
Its pair-count formula is symmetric under swapping the two input row counts,
so it does not have the orientation mismatch above.

I don't think this is correct. approx_tuple_count() estimates the
number of matching pairs under inner-join semantics. A right-semi
join emits each matched inner row once, so the matching pair count
overestimates whenever inner rows match more than one outer row on
average, which is exactly the situation these join types are for, a
small hashed input probed by a large one.

For right-anti it's not even close to the right answer. The join
emits the unmatched inner rows, and the matching pair count says
nothing about that. If anything, the two move in opposite directions.
A mostly-unmatched inner side has pairs ~ 0 while the output is nearly
the whole inner side; a mostly-matched one can have a huge pair count
while the output is nearly empty.

The orientation-independence you point out is the right requirement,
though, and outer_match_frac already has it, as it always describes
the semijoin's LHS. So we can compute hashjointuples leveraging that,
inner_path_rows * outer_match_frac for right-semi and inner_path_rows
* (1 - outer_match_frac) for right-anti.

Where approx_tuple_count() is the right tool is when we compute
outer_matched_rows in the inner_unique branch. This is because each
outer row has at most one match there, so the matched outer rows are
exactly the matching pairs.

- Richard

Hi,

On Thu, 20 Aug 2026 at 11:50, Richard Guo <guofenglinux@gmail.com> wrote:

On Tue, Aug 18, 2026 at 6:22 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:

I tried a case with 1000 unmatched hash tuples where the filter allowed
only one through. The executor examined all 1000 and reported "Rows
Removed by Filter: 999", while path->rows was one. The patch reduced the
cost by 9.99, exactly 999 * cpu_tuple_cost.

Could using path->rows therefore undercharge the rows that were examined
but filtered out?

Yeah, you're right. path->rows is the row count after all the quals
have been applied, while the per-tuple charges here are meant to cover
the rows that come out of the hash-clause matching, before any other
quals filter them.

I think a more accurate way is to compute hashjointuples from the
inner side directly:

right-semi: inner_path_rows * outer_match_frac
right-anti: inner_path_rows * (1 - outer_match_frac)

This is mirroring what JOIN_SEMI and JOIN_ANTI already do with the
outer side. Both cpu_tuple_cost and the quals not used for hashing
are charged on that count, so the 1000 rows your executor examined are
all charged, and the filter's selectivity affects only the join's
output row estimate.

Attached v3 patch does that.

Thanks for the updated patch, it looks good to me.

Regards,
Ayush

On Wed, Aug 19, 2026 at 11:59 PM Richard Guo <guofenglinux@gmail.com> wrote:

On Wed, Aug 19, 2026 at 4:24 PM Haibo Yan <tristan.yim@gmail.com> wrote:

For JOIN_RIGHT_SEMI and JOIN_RIGHT_ANTI, however, the physical outer and

inner

paths have been swapped. outer_path_rows therefore belongs to the

physically

swapped probe side, while outer_match_frac was computed by
compute_semi_anti_join_factors() for the canonical semi/anti orientation.

I agree with this diagnosis. The problem is hashjointuples itself,
not just the cpu_tuple_cost multiplier.

I tried fixing that estimate directly instead of changing the population

used

by cpu_tuple_cost:

if (path->jpath.jointype == JOIN_RIGHT_SEMI ||
path->jpath.jointype == JOIN_RIGHT_ANTI)
hashjointuples =
approx_tuple_count(root, &path->jpath, hashclauses);
else if (path->jpath.jointype == JOIN_ANTI)
hashjointuples = outer_path_rows - outer_matched_rows;
else
hashjointuples = outer_matched_rows;

approx_tuple_count() is already used by the other branch of
final_cost_hashjoin() to estimate the hash-clause candidate-pair

population.

Its pair-count formula is symmetric under swapping the two input row

counts,

so it does not have the orientation mismatch above.

I don't think this is correct. approx_tuple_count() estimates the
number of matching pairs under inner-join semantics. A right-semi
join emits each matched inner row once, so the matching pair count
overestimates whenever inner rows match more than one outer row on
average, which is exactly the situation these join types are for, a
small hashed input probed by a large one.

For right-anti it's not even close to the right answer. The join
emits the unmatched inner rows, and the matching pair count says
nothing about that. If anything, the two move in opposite directions.
A mostly-unmatched inner side has pairs ~ 0 while the output is nearly
the whole inner side; a mostly-matched one can have a huge pair count
while the output is nearly empty.

The orientation-independence you point out is the right requirement,
though, and outer_match_frac already has it, as it always describes
the semijoin's LHS. So we can compute hashjointuples leveraging that,
inner_path_rows * outer_match_frac for right-semi and inner_path_rows
* (1 - outer_match_frac) for right-anti.

Where approx_tuple_count() is the right tool is when we compute
outer_matched_rows in the inner_unique branch. This is because each
outer row has at most one match there, so the matched outer rows are
exactly the matching pairs.

- Richard

Hi Richard,

I spent some more time on the qp_qual_cost side of this.

First, I think your v3 treatment of hashjointuples is the right one. My
earlier
suggestion of using approx_tuple_count() there was mixing up two different
populations: matching pairs versus matched/unmatched inner rows. So I agree
that for right-semi/right-anti the latter is what hashjointuples should
represent.

There is still a separate issue with using the same population for the
non-hash
qual cost, though.

For right-anti this is fairly clear from the executor. The right-anti
handling
happens after

ExecQual(joinqual, econtext)

so every hash-clause candidate reaching that point evaluates the joinqual.
There is no right-semi-style match-bit shortcut before it.

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.

Right-semi is much harder because its match-bit test occurs before
ExecQual(joinqual). Once an inner tuple has found its first successful
match,
later hash candidates for that tuple don’t evaluate the joinqual at all.

I tried to see whether the existing semi/hash costing information was
enough
to estimate that population, in particular match_count, inner_scan_frac,
innerbucketsize, and innermcvfreq.

I don’t think it is.
For example, I have these two cases:

R_est J H
uniform case 333 500,500 1,000,000
5/1000 keys covered 5 5 2,000,000

where R_est is the v3 matched-inner estimate, J is the measured number of
joinqual evaluations, and H is the hash-clause candidate-pair count.

So using R_est can underestimate J by about 1500x, while using H can
overestimate it by about 400,000x.

I also tried reusing the existing

inner_scan_frac = 2.0 / (match_count + 1.0)

model, including H * inner_scan_frac, but it does not survive the skewed
cases.
The underlying problem seems to be that the executor behavior depends on
how
the probe-side values are distributed across individual inner tuples, not
just
on the total candidate-pair count.

innerbucketsize and innermcvfreq don’t recover that information either.
They
are derived from the inner relation’s own hash-key statistics. In tests
where
the fraction of inner keys receiving any candidate varied from 0.005 to
0.901,
both values remained identical because the inner relation itself had the
same
uniform key distribution.

So at this point I don’t have a right-semi replacement for the qp_qual_cost
multiplier that I would be comfortable proposing. It appears to require
information about the cross-relation candidate distribution that the
existing
costing state doesn’t retain.

One correction to my earlier comments: I also rechecked the tenk1 case
where
v3 changes the plan. The joinqual-population error does not explain that
plan
change. Even replacing the current multiplier with the measured/estimated
joinqual population changes the cost by too little to account for the plan
choice. So I don’t think that case is evidence against the v3
hashjointuples change.

There is one additional complication for a general right-anti fix: the
non-hash
restriction cost computed here can include clauses that later become
Join.joinqual
as well as pushed-down clauses that become Plan.qual, and those are
evaluated on
different populations. So simply replacing the multiplier for the combined
qp_qual_cost would not be completely general either.

My current conclusion is therefore:

* I agree with the v3 hashjointuples fix;
* there is a separate qual-evaluation population issue;
* the right-anti executor behavior makes that mismatch particularly clear;
* right-semi also has the mismatch, but I don’t see a robust fix using
the existing planner estimates;
* I don’t think we should complicate this patch with a speculative
right-semi estimator.

Thnks,

Haibo

On Fri, Aug 21, 2026 at 6:53 AM Haibo Yan <tristan.yim@gmail.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

Hi Richard

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:

I found an older explanation from Tom Lane that seems relevant here:
/messages/by-id/21225.1371131974@sss.pgh.pa.us

He points out that costing joinrestrictinfo “returns the costs for ALL
the quals”, and that after subtracting hash_qual_cost, qp_qual_cost represents
all additional clauses beside the hash clauses.

So in this case qp_qual_cost combines two executor qual classes with
different evaluation populations: non-hash joinquals and pushed-down
otherquals. It therefore seems that changing the multiplier for the combined
qp_qual_cost cannot in general make both parts correct.

Would it make sense to split those two costs before applying the
right-anti candidate-pair multiplier?

Thanks

On Thu, Aug 20, 2026 at 10:53 PM Richard Guo <guofenglinux@gmail.com> wrote:

On Fri, Aug 21, 2026 at 6:53 AM Haibo Yan <tristan.yim@gmail.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

On Fri, Aug 21, 2026 at 4:55 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:

He points out that costing joinrestrictinfo “returns the costs for ALL the quals”, and that after subtracting hash_qual_cost, qp_qual_cost represents all additional clauses beside the hash clauses.

That's true. qp_qual_cost here is the cost of joinqual + otherqual.

So in this case qp_qual_cost combines two executor qual classes with different evaluation populations: non-hash joinquals and pushed-down otherquals. It therefore seems that changing the multiplier for the combined qp_qual_cost cannot in general make both parts correct.

Would it make sense to split those two costs before applying the right-anti candidate-pair multiplier?

I don't feel much motivated to do that. First, pushed-down quals
that stick at a right anti join are close to nonexistent. Only the
inner side's Vars are visible above the join, and the join nulls
nothing, so a clause that could be placed there normally gets pushed
down further and evaluated below the join. The comment in v4 notes
this tradeoff:

+   * ... (This overcharges
+   * any pushed-down clauses, which are evaluated just once per emitted row,
+   * but such clauses are rare at a right anti join.)

Second, nothing else in the join costing code makes this distinction.
For plain joins, hashjointuples is the count before the joinquals are
applied, so the pushed-down quals are overcharged in the same way.
That's what the existing comment "(This is pessimistic since not all
of the quals may get evaluated at each tuple.)" refers to. JOIN_SEMI
and JOIN_ANTI have the same conflation in the other direction. If we
really want to separate the two classes, it should not be limited to
right anti joins; we'd need to do it for all join types, and that's
beyond the scope of this patch.

- Richard

On Sat, Aug 22, 2026 at 2:15 AM Haibo Yan <tristan.yim@gmail.com> wrote:

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.

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.

Right. Fixed.

Attached is v5 of this patch. I plan to push it soon if there are no
objections. It can result in plan changes, so I don't plan to
back-patch it to stable branches. I once thought about pushing it to
v19, but at this late phase of the v19 cycle I'm inclined not to.
Maybe I would have done that if it were June.

Any thoughts are welcome.

- Richard

On Mon, Aug 24, 2026 at 1:35 AM Richard Guo <guofenglinux@gmail.com> wrote:

On Sat, Aug 22, 2026 at 2:15 AM Haibo Yan <tristan.yim@gmail.com> wrote:

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.

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.

Right. Fixed.

Attached is v5 of this patch. I plan to push it soon if there are no
objections. It can result in plan changes, so I don't plan to
back-patch it to stable branches. I once thought about pushing it to
v19, but at this late phase of the v19 cycle I'm inclined not to.
Maybe I would have done that if it were June.

Any thoughts are welcome.

- Richard

Thanks, v5 looks good to me. The RIGHT_ANTI qual-cost split now matches the
executor behavior I was seeing, and the added Join Filter test addresses my
previous testing concern.

I also agree with not back-patching this, and at this point in the cycle I’d
be inclined to leave v19 alone as well.

LGTM.

HI Richard

On Tue, Aug 25, 2026 at 11:01 AM wenhui qiu <qiuwenhuifx@gmail.com> wrote:

Thank you for your updating ,v5 looks good to me.The official release date for v19 is fast approaching, so it's a little late now.

Pushed.

- Richard