Re: COALESCE patch

Hi,
For SEMI/ANTI joins the selectivity is existence-based - whether an
outer row has at least one match - so it cannot be written as the
inner-join pair sum. It is estimated as follows.
First, for each side a single key distribution is built from the
COALESCE branches. Each branch is weighted by the probability of
reaching it (the product of the earlier branches' null fractions), so
the merged frequency of a value is:

F(value) = P(reach branch 1) * freq(value in branch 1)
+ P(reach branch 2) * freq(value in branch 2)
+ ...

Each side also carries its null fraction and distinct count.
Then the two keys are combined once:

sel = matchfreq + uncertainfrac * uncertain

matchfreq - total frequency of outer MCV values that also appear
on the inner side;
uncertain - the outer non-NULL mass the MCV does not cover, (1 -
outer_nullfrac) - seen;
uncertainfrac - the estimated match share of that tail,
clamp(inner_ndistinct / outer_ndistinct),
or 0.5 when a distinct count is unknown.

Finally sel is capped at 1 - outer_nullfrac, since a semi-join keeps
at most the non-NULL outer rows. This is the SEMI selectivity; the
join-size code inverts it for ANTI.

Best regards,
Egor Savelev,
Tantor Labs

ср, 23 сент. 2026 г. в 07:35, Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>:
>
> While reviewing v6, I noticed try_coalesce_eq() in eqjoinsel() returns
> before the function ever looks at sjinfo->jointype, so SEMI/ANTI joins
> get the same formula meant for INNER/LEFT/FULL. I'm not sure it is true.
> Shouldn't the fat path be restricted to INNER/LEFT/FULL until SEMI/ANTI
> get their own existence-base treatment?
>
> --
> Best regards,
> Ilia Evdokimov,
> Tantor Labs LLC,
> https://tantorlabs.com/
>