Original >From: Greg Burd <[email protected]> >Date: 2026-10-26 21:31 >To: pgsql-hackers <[email protected]> >Cc: 44973863 <[email protected]>, ilya.evdokimov ><[email protected]>, tgl <[email protected]>, pgsql-hackers ><[email protected]>, shahzaddamil <[email protected]> >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) [email protected]
