Re: Optimize MCV stats for sortable types and utilize sorted-order properties

Original
>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Date: 2026-09-22 12:07
>To: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>Cc: tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, tomas <tomas(at)vondra(dot)me>, dean.a.rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>
>Subject: Re: Optimize MCV stats for sortable types and utilize sorted-order properties
>
>Original
>>From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
>>Date: Sep 21, 2026 21:03
>>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>>Cc: tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, tomas <tomas(at)vondra(dot)me>, dean.a.rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>
>>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) 
44973863(at)qq(dot)com