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]
v2-0001-Fix-var_eq_const-sum-selectivity-of-all-matching-.patch
Description: Binary data
match collate between column and express.xlsx
Description: Binary data
