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

Hi ZizhuanLiu,

Thanks for the explanation. That makes sense now.

I see why c2 = 'A-1' COLLATE "default" can estimate 1 while returning 2.
The stats for c2 were built under case_insensitive, so 'a-1' becomes the
stored MCV entry for both spellings, and comparing with COLLATE "default"
does not line up with that.

Agreed that this is outside what v2 is trying to fix.

Regards,

Damil

On Thu, 6 Aug 2026 at 14:00, ZizhuanLiu X-MAN <44973863(at)qq(dot)com> wrote:

> 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