Original >From: Damil Shahzad <[email protected]> >Date: 2026-08-06 15:59 >To: ZizhuanLiu X-MAN <[email protected]> >Cc: Ilia Evdokimov <[email protected]>, Tom Lane ><[email protected]>, pgsql-hackers <[email protected]> >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)
[email protected]
match collate between column and express.xlsx
Description: Binary data
