Original
>From: ZizhuanLiu X-MAN <[email protected]>
>Date: 2026-09-22 12:07
>To: Ilia Evdokimov <[email protected]>, pgsql-hackers
><[email protected]>
>Cc: tgl <[email protected]>, tomas <[email protected]>, dean.a.rasheed
><[email protected]>, guofenglinux <[email protected]>
>Subject: Re: Optimize MCV stats for sortable types and utilize sorted-order
>properties
>
>Original
>>From: Ilia Evdokimov <[email protected]>
>>Date: Sep 21, 2026 21:03
>>To: ZizhuanLiu X-MAN <[email protected]>, pgsql-hackers
>><[email protected]>
>>Cc: tgl <[email protected]>, tomas <[email protected]>, dean.a.rasheed
>><[email protected]>, guofenglinux <[email protected]>
>>Subject: Re: Optimize MCV stats for sortable types and utilize sorted-order
>>properties
>>......
>>In practice, users rarely bump default_statistics_target to extreme
>>values like 10 000. At 100-200, scanning a compact array of Datums fits
>>entirely in L1 cache.
>......
>For data types with a < operator, the original compute_scalar_stats()
>generates MCV statistics with
>numbers[] sorted in descending order of frequency, while the new version
>generates MCV statistics
>with values[] sorted in ascending order of value. The change to this function
>is relatively small:
>it mainly adds a palloc_array(ScalarMCVItem, num_mcv) allocation, without
>introducing other
>significant computation. These two forms will not coexist for sortable columns.
>
>(TODO: this part is not completed yet. I will continue working on it later:
>the new version will replace
>the old statistics for this type of column.) Therefore, there should be no
>conflict, and the number of
>statistics slots will not exceed the STATISTIC_NUM_SLOTS limit.
After investigation, statistics for columns/index expressions/extended
expressions stored in
pg_catalog.pg_statistic are overwritten as a whole.
Therefore the note below:*"TODO: this part is not completed yet. I will
continue working on
it later: the new version will replace the old statistics for this type of
column."* is no longer a
concern and can be dropped from consideration.
I have attached the verification SQL statements together with their results at
the end:
xman7=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+-------
public | t_analyze_mcv | table | xman
(1 row)
xman7=# select oid from pg_catalog.pg_class where relname = 't_analyze_mcv';
oid
-------
16385
(1 row)
xman7=# select * from pg_catalog.pg_statistic where starelid = 16385\gx
-[ RECORD 1 ]--------------------------------------------------------
starelid | 16385
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | 11
stakind1 | 8 =#define STATISTIC_KIND_MCV_VALUE_SORTED 8
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 96
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stacoll1 | 0
stacoll2 | 0
stacoll3 | 0
stacoll4 | 0
stacoll5 | 0
stanumbers1 | {0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.1}
stanumbers2 | {0.20327759}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {0,1,2,3,4,5,6,7,8,9,12}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
xman7=# update pg_catalog.pg_statistic set stakind1= 1 where starelid = 16385;
UPDATE 1
xman7=# select * from pg_catalog.pg_statistic where starelid = 16385\gx
-[ RECORD 1 ]--------------------------------------------------------
starelid | 16385
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | 11
stakind1 | 1 =====#define STATISTIC_KIND_MCV 1
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 96
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stacoll1 | 0
stacoll2 | 0
stacoll3 | 0
stacoll4 | 0
stacoll5 | 0
stanumbers1 | {0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.1}
stanumbers2 | {0.20327759}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {0,1,2,3,4,5,6,7,8,9,12}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
xman7=# select
attname,null_frac,n_distinct,most_common_vals,most_common_freqs,correlation
from pg_catalog.pg_stats where tablename = 't_analyze_mcv'\gx
-[ RECORD 1 ]-----+--------------------------------------------------------
attname | id
null_frac | 0
n_distinct | 11
most_common_vals | {0,1,2,3,4,5,6,7,8,9,12}
most_common_freqs | {0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.1}
correlation | 0.20327759
xman7=#
xman7=# analyze t_analyze_mcv;
ANALYZE
xman7=# select * from pg_catalog.pg_statistic where starelid = 16385\gx
-[ RECORD 1 ]--------------------------------------------------------
starelid | 16385
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | 11
stakind1 | 8 =#define STATISTIC_KIND_MCV_VALUE_SORTED 8
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 96
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stacoll1 | 0
stacoll2 | 0
stacoll3 | 0
stacoll4 | 0
stacoll5 | 0
stanumbers1 | {0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.1}
stanumbers2 | {0.20327759}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {0,1,2,3,4,5,6,7,8,9,12}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
xman7=# select
attname,null_frac,n_distinct,most_common_vals,most_common_freqs,correlation
from pg_catalog.pg_stats where tablename = 't_analyze_mcv'\gx
-[ RECORD 1 ]-----+--------------------------------------------------------
attname | id
null_frac | 0
n_distinct | 11
most_common_vals | {0,1,2,3,4,5,6,7,8,9,12}
most_common_freqs | {0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.09,0.1}
correlation | 0.20327759
xman7=#
regards,
--
ZizhuanLiu (X-MAN)
[email protected]
