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

Original
>From: Damil Shahzad <shahzaddamil(at)gmail(dot)com>
>Date: 2026-08-04 19:29
>To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Cc: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>, 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, Damil
Thank you very much for your testing and valuable feedback.

Original
>From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
>Date: 2026-08-05 15:33
>To: Damil Shahzad <shahzaddamil(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Cc: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>, 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

>A cheaper way to get some benefit here without touching that tradeoff: when there are no MCV matches, var_eq_const does a second full pass over MCV list just to compute `sumcommon` - but that branch is only reached after the first loop has already scanned every entry. So `sumcommon` can be accumulated inline in that same scan, and the separate summing loop dropped. The match case is unaffected; only the no-match path gets faster, by skipping a redundant second traversal.
>
>I attached patch with these changes. What do you think?

Hi, Ilia,
Thanks for your feedback and the patch. Your patch removes
an extra loop used for a straightforward calculation, but the resulting
performance improvement is probably minor.

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.

For every other scenario, performing a full MCV scan is either
unnecessary or inappropriate.

The relevant test queries are listed below. The attached XLS spreadsheet
contains the full analysis.

I welcome any discussion or feedback if there are deficiencies in my analysis.

```SQL
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');

CREATE TABLE test_mcv(c1 text, c2 text COLLATE "case_insensitive");
xman7=# \d+ test_mcv
Table "public.test_mcv"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------+------------------+----------+---------+----------+-------------+--------------+-------------
c1 | text | | | | extended | | |
c2 | text | case_insensitive | | | extended | | |
Access method: heap

xman7=#

INSERT INTO test_mcv values ('a-1','a-1'), ('A-1','A-1');
INSERT INTO test_mcv values ('a-1','a-1'), ('A-1','A-1');
INSERT INTO test_mcv values ('a-2','a-2'), ('A-2','A-2');
INSERT INTO test_mcv values ('a-2','a-2'), ('A-2','A-2');

INSERT INTO test_mcv values ('b-1','b-1'), ('B-1','B-1');
INSERT INTO test_mcv values ('b-1','b-1'), ('B-1','B-1');
INSERT INTO test_mcv values ('b-2','b-2'), ('B-2','B-2');
INSERT INTO test_mcv values ('b-2','b-2'), ('B-2','B-2');

ANALYZE test_mcv;

select attname,n_distinct,most_common_vals,most_common_freqs,correlation from pg_catalog.pg_stats where tablename = 'test_mcv'\gx
-[ RECORD 1 ]-----+--------------------------------------------------
attname | c1
n_distinct | -0.5
most_common_vals | {A-1,A-2,B-1,B-2,a-1,a-2,b-1,b-2}
most_common_freqs | {0.125,0.125,0.125,0.125,0.125,0.125,0.125,0.125}
correlation | 0.4
-[ RECORD 2 ]-----+--------------------------------------------------
attname | c2
n_distinct | -0.25
most_common_vals | {a-1,a-2,b-1,b-2}
most_common_freqs | {0.25,0.25,0.25,0.25}
correlation | 1

xman7=#

regards,
--
ZizhuanLiu (X-MAN) 
44973863(at)qq(dot)com