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-06 15:59
>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Cc: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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 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

>c2 = 'A-1' COLLATE "default" estimate 1 vs actual 2
Yes, C2 uses the case_insensitive collation. The row count recorded for value 'a-1'
in its statistics includes rows for both 'A-1' and 'a-1', as shown below:
-[ 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

Note: this concerns the literal 'a-1', not 'A-1'. When we inserted data, 'a-1' was loaded
into column c2 first. Since c2 uses the case_insensitive collation, the earlier entry 'a-1'
becomes the representative value covering both 'a-1' and 'A-1'.

When estimating for c2 = 'A-1' COLLATE "default", based on the literals stored in c2’s most_common_vals,
the matching row count ought to be 0. However, the optimizer estimates at least 1 row. At execution time,
each value in c2 is implicitly cast to COLLATE "default" and then compared against 'A-1' COLLATE "default",
yielding an actual row count of 2. This case produces either an accurate estimate or an underestimation.
xman7=# explain analyze select * from test_mcv where c2 = 'A-1' collate "default"; --accurate estimate/underestimation that cannot be corrected
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on test_mcv (cost=0.00..1.20 rows=1 width=8) (actual time=0.140..0.187 rows=2.00 loops=1)
Filter: (c2 = 'A-1'::text)
Rows Removed by Filter: 14
Buffers: shared hit=1
Planning Time: 0.570 ms
Execution Time: 0.264 ms
(6 rows)

Conversely, for c2 = 'a-1' COLLATE "default", the literal entry in c2’s most_common_vals suggests a matching
count of 4 rows. During execution, each c2 value is implicitly cast to COLLATE "default" and compared to
`'a-1' COLLATE "default"", resulting in 2 actual rows. This case yields either an accurate estimate or an overestimation.
xman7=# explain analyze select * from test_mcv where c2 = 'a-1' collate "default"; --accurate estimate/overestimation that cannot be corrected
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on test_mcv (cost=0.00..1.20 rows=4 width=8) (actual time=0.071..0.105 rows=2.00 loops=1)
Filter: (c2 = 'a-1'::text)
Rows Removed by Filter: 14
Buffers: shared hit=1
Planning Time: 0.626 ms
Execution Time: 0.161 ms
(6 rows)

I need to make a correction here: the outcome previously labelled 'underestimation' should
instead be 'accurate estimate / underestimation'.
I have updated the corresponding notes in the attached spreadsheet.

All of the test scenarios below fall into either the accurate estimate / underestimation or accurate estimate / overestimation category.

explain analyze select * from test_mcv where c2 = 'A-1' collate "default"; --accurate estimate/underestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'A-2' collate "default"; --accurate estimate/underestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'a-1' collate "default"; --accurate estimate/overestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'a-2' collate "default"; --accurate estimate/overestimation that cannot be corrected

explain analyze select * from test_mcv where c2 = 'a-1' collate "num_ignore_punct"; --accurate estimate/overestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'A-1' collate "num_ignore_punct"; --accurate estimate/underestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'a-2' collate "num_ignore_punct"; --accurate estimate/overestimation that cannot be corrected
explain analyze select * from test_mcv where c2 = 'A-2' collate "num_ignore_punct"; --accurate estimate/underestimation that cannot be corrected

regards,

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