Re: Optimize MCV stats for sortable types and utilize sorted-order properties - Mailing list pgsql-hackers

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