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

On Sat, 12 Sep 2026 09:54:56 +0800, ZizhuanLiu X-MAN <44973863(at)qq(dot)com> wrote:
> Original
> >From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
> >Date: 2026-09-11 04:20
> >To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Damil Shahzad <shahzaddamil(at)gmail(dot)com>
> >Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match
> >On 9/10/26 16:11, ZizhuanLiu X-MAN wrote:
> >
> >Thanks everyone for your time reviewing and for the valuable feedback.
> >I agree with the overall suggestions. Attached is the v5 patch.
> >LGTM, but you should apply pgindent to this.
> >
> >--
> >Best regards,
> >Ilia Evdokimov,
> >Tantor Labs LLC,
> >https://tantorlabs.com/
>
>
> Hi, Ilia
>
> Thanks for the reminder.
>
> I've run pgindent on the new v6 patch. Comparing against v5, there are only formatting changes:
> wrapped comments and added blank lines between variable declarations and following code.
>
>
> regards,
> --
> ZizhuanLiu (X-MAN) 
> 44973863(at)qq(dot)com

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