Re: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match

Hi,

I signed up as a reviewer for CF entry 7075 and I tested this on current
master with ICU enabled on macOS.

Without the patch, this query:

WHERE a = 'b' COLLATE "case_insensitive"

estimated 10 rows but actually returned 19 rows.

After applying the patch, the same query estimated 19 rows and returned 19
rows.

I also checked the normal equality cases and those stayed the same:

WHERE a = 'B' estimated 10 and returned 10

WHERE a = 'b' estimated 9 and returned 9

So the author's example looks real, and the patch fixes that example.

Tom's concern still seems important. Scanning the whole MCV list every time
would cost more in the common case, and it only changes the result when the
comparison operator or collation is different from the equality used to
build the statistics. Before this can move forward, I think we need a
stronger reason for that tradeoff. For example:

1. How often do multiple MCV entries match in practice?
2. Can we only do the full scan when the operator or collation differs
from the stats equality operator?

I am happy to test a revised approach if one is proposed.

Thanks,

Damil Shahzad

On Tue, 4 Aug 2026 at 16:26, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "=?utf-8?B?Wml6aHVhbkxpdSBYLU1BTg==?=" <44973863(at)qq(dot)com> writes:
> > While reviewing CF6397(https://commitfest.postgresql.org/patch/6397/),
> I noticed that
> > the function `var_eq_const()` located at `backend/utils/adt/selfuncs.c`
> consumes statistical
> > data from the `most_common_vals` and `most_common_freqs` columns in the
> system
> > catalog `pg_catalog.pg_stats`. Currently, the function terminates
> iteration immediately
> > after finding the first matching entry and adopts the selectivity of
> this single matched value.
>
> > I believe this estimation logic is inaccurate. Instead, we should
> traverse all entries in
> > `most_common_vals`, check for matches against each entry, and sum up the
> selectivities
> > of all matching items.
>
> That would double the function's runtime on average, without changing
> the results at all in most cases (it could only be different if the
> given operator has different semantics from the equality operator used
> while building the statistics list). I think you need a far stronger
> argument for changing the existing tradeoff than "I believe".
>
> regards, tom lane
>
>
>
>
>