Original >From: Damil Shahzad <[email protected]> >Date: 2026-08-04 19:29 >To: Tom Lane <[email protected]> >Cc: ZizhuanLiu X-MAN <[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, Damil
Thank you very much for your testing and valuable feedback.
Original
>From: Ilia Evdokimov <[email protected]>
>Date: 2026-08-05 15:33
>To: Damil Shahzad <[email protected]>, Tom Lane <[email protected]>
>Cc: ZizhuanLiu X-MAN <[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
>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)
[email protected]
match collate between column and express.xlsx
Description: Binary data
