Hi ZizhuanLiu,
Thanks for sending v2. I tested it using your test_mcv setup and the case_insensitive example I tried before. On the case you are targeting, deterministic column and non deterministic expression in the query, it looks good to me. Without the patch, c1 = 'a-1' COLLATE "case_insensitive" estimated 2 rows but actually returned 4. With v2 it estimated 4 and returned 4. Same kind of fix on my other table, a = 'b' COLLATE "case_insensitive" went from estimated 10 to estimated 19, and actual was 19. The normal equality cases I checked still looked the same as before, things like c1 = 'a-1', a = 'B', and a = 'b'. For the other cases in your spreadsheet, v2 seemed to keep the old first match behavior, which matches what you described. I did still see c2 = 'A-1' COLLATE "default" estimate 1 vs actual 2, but I think that is the out of scope case you already noted. Overall I think v2 is a much better direction than v1. I don't have extra test cases to add beyond what is already in your spreadsheet. Thanks, Damil Shahzad On Thu, 6 Aug 2026 at 12:25, ZizhuanLiu X-MAN <[email protected]> wrote: > Original > >From: ZizhuanLiu X-MAN <[email protected]> > >Date: 2026-08-05 18:44 > >To: Ilia Evdokimov <[email protected]>, Damil Shahzad < > [email protected]>, Tom Lane <[email protected]> > >Cc: pgsql-hackers <[email protected]> > >Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV > entries instead of stopping at first match > >>From: Tom Lane <[email protected]> > >>Date: 2026-07-30 21:39 > >>To: ZizhuanLiu X-MAN <[email protected]> > >>Cc: pgsql-hackers <[email protected]> > > > > > >>Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV > entries instead of stopping at first match > > > >>I think you need a far stronger > >>argument for changing the existing tradeoff than "I believe". > >Although I have made efforts to implement it, there is still > >no satisfactory and acceptable solution available at present. > > > >>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). > >So I agree with Tom’s reasoning. > > > >regards, > >-- > >ZizhuanLiu (X-MAN) > >[email protected] > > Hi, Ilia > After further consideration, based on the definition of the > AttStatsSlot > data structure and the functional logic of get_attstatsslot(), > sslot.nvalues > and sslot.nnumbers are two members that are not guaranteed to be > symmetric or equal. Therefore, for the logic related to sumcommon, > I suggest taking a conservative approach and leaving it untouched for this > patch. > > > Original > >From: ZizhuanLiu X-MAN <[email protected]> > >Date: 2026-08-05 18:31 > >To: Ilia Evdokimov <[email protected]>, Damil Shahzad < > [email protected]>, Tom Lane <[email protected]> > >Cc: pgsql-hackers <[email protected]> > >Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV > entries instead of stopping at first match > >Hi all, > > Upon further careful analysis,I have found that that full MCV > scanning is > >only justified and beneficial under the following condition: > >the column has a deterministic collation, and the expression uses a > >non-deterministic collation. > > Hi Damil, all, > The patch currently implements only this scenario, and addresses only > the test cases listed in the attached spreadsheet: > ```SQL > explain analyze select * from test_mcv where c1 = 'a-0' collate > ""case_insensitive""; --Some adjustments are needed, yet this idea is > feasible. > explain analyze select * from test_mcv where c1 = 'a-1' collate > ""case_insensitive""; --Some adjustments are needed, yet this idea is > feasible. > explain analyze select * from test_mcv where c1 = 'a-2' collate > ""case_insensitive""; --Some adjustments are needed, yet this idea is > feasible. > > I welcome additional comprehensive test cases and suggestions for > improvements. > We will add and refine regression tests once testing and feedback have > stabilized. > > regards, > -- > ZizhuanLiu (X-MAN) > [email protected] > >
