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

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