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

Original
>From: Greg Burd <greg(at)burd(dot)me>
>Date: 2026-10-26 21:31
>To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
>Cc: 44973863 <44973863(at)qq(dot)com>, ilya.evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, shahzaddamil <shahzaddamil(at)gmail(dot)com>
>Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match
>......
>......
>Hey ZizhuanLiu,
>
>I happened to be testing something else in the area adjacent to your
>patch and noticed the overlap. I was able to reproduce the undercount
>while working on a differential selectivity checking tool.
>
>Built master at the patch's merge-base (66042bd3ebd) and master+patch.
>The trigger is a nondeterministic collation that differs from the
>statistics collation, so one constant is equal to several MCV entries.
>
>A column in the default collation, values that are case variants of one
>word (cafe, CAFE, Cafe, cAfe, caFe, cafE, CAfe, CAFe), each about 1500
>rows, so all eight become MCVs. Query under a case-insensitive ICU
>collation:
>
> SELECT * FROM t WHERE s COLLATE ci = 'cafe'; -- matches all 8 variants
>
> actual rows: 12000
> master estimate: 1500 (stops at the first matching MCV)
> master+patch: 12000 (sums all eight)
>
>So master is 8x low here, and the error grows with the number of
>collation-equal MCVs. With the patch the estimate is exact, nice work.
>
>I checked that the full-MCV-scan path only engages when the comparison
>collation is nondeterministic and differs from the stats collation,
>matching the scan_entire_mcv guard in the patch; deterministic-collation
>estimates are unchanged. LGTM.
>
>+1, best.
>
>-greg

Hi greg,

Thanks for independently reproducing the issue and testing the patch against master.
The eight-variant example clearly demonstrates the underestimation and confirms that
summing all matching MCV frequencies fixes it.

Thanks also for checking the scan_entire_mcv guard and confirming that deterministic-collation
estimates remain unchanged.

I appreciate your testing and feedback.

regards,
--
ZizhuanLiu (X-MAN) 
44973863(at)qq(dot)com