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

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 <44973863(at)qq(dot)com> wrote:

> Original
> >From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
> >Date: 2026-08-05 18:44
> >To: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, Damil Shahzad <
> shahzaddamil(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
> >Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV
> entries instead of stopping at first match
> >>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >>Date: 2026-07-30 21:39
> >>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
> >>Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
> >
> >
> >>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)
> >44973863(at)qq(dot)com
>
> 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 <44973863(at)qq(dot)com>
> >Date: 2026-08-05 18:31
> >To: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, Damil Shahzad <
> shahzaddamil(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
> >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)
> 44973863(at)qq(dot)com
>
>