Collecting Statistics in Cascading Mode

Updated on 2024-06-07 GMT+08:00

View PDF

When ANALYZE | ANALYSE is used to analyze a partitioned table, the system automatically collects all partition-level statistics that comply with semantics in the partitioned table based on the specified or default PARTITION_MODE. For details about PARTITION_MODE, see the PARTITION_MODE parameter in "SQL Reference > SQL Syntax > ANALYZE | ANALYSE" in Developer Guide.

CAUTION:

When statistics about replication, and hash bucket partitioned tables are collected in cascading mode and PARTITION_MODE is set to ALL, the behavior is converted to the ALL COMPLETE mode.
Cascading collection of partition-level statistics does not support the scenario where the value of default_statistics_target is a negative number.

Examples

Create a partitioned table and insert data.

gaussdb=# CREATE TABLE t1_range_int
(
    c1 INT,
    c2 INT,
    c3 INT,
    c4 INT
)
PARTITION BY RANGE(c1)
(
    PARTITION range_p00 VALUES LESS THAN(10),
    PARTITION range_p01 VALUES LESS THAN(20),
    PARTITION range_p02 VALUES LESS THAN(30),
    PARTITION range_p03 VALUES LESS THAN(40),
    PARTITION range_p04 VALUES LESS THAN(50)
);
gaussdb=# INSERT INTO t1_range_int SELECT v,v,v,v FROM generate_series(0, 49) AS v;

Collect statistics in cascading mode.

gaussdb=# ANALYZE t1_range_int WITH ALL;

View partition-level statistics.

gaussdb=# SELECT relname, parttype, relpages, reltuples FROM pg_partition WHERE parentid=(SELECT oid FROM pg_class WHERE relname='t1_range_int') ORDER BY relname;
   relname    | parttype | relpages | reltuples 
--------------+----------+----------+-----------
 range_p00    | p        |        4 |         9
 range_p01    | p        |        7 |        17
 range_p02    | p        |        6 |        13
 range_p03    | p        |        2 |         5
 range_p04    | p        |        4 |         9
 t1_range_int | r        |        0 |         0
(6 rows)

gaussdb=# SELECT schemaname,tablename,partitionname,subpartitionname,attname,inherited,null_frac,avg_width,n_distinct,n_dndistinct,most_common_vals,most_common_freqs,histogram_bounds FROM pg_stats WHERE tablename='t1_range_int' ORDER BY tablename, partitionname, attname;
 schemaname |  tablename   | partitionname | subpartitionname | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs |                                                               histogram_bounds               
                                                 
------------+--------------+---------------+------------------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+----------------------------------------------------------------------------------------------
-------------------------------------------------
 public     | t1_range_int | range_p00     |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
 public     | t1_range_int | range_p00     |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
 public     | t1_range_int | range_p00     |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
 public     | t1_range_int | range_p00     |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
 public     | t1_range_int | range_p01     |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
 public     | t1_range_int | range_p01     |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
 public     | t1_range_int | range_p01     |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
 public     | t1_range_int | range_p01     |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
 public     | t1_range_int | range_p02     |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
 public     | t1_range_int | range_p02     |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
 public     | t1_range_int | range_p02     |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
 public     | t1_range_int | range_p02     |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
 public     | t1_range_int | range_p03     |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
 public     | t1_range_int | range_p03     |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
 public     | t1_range_int | range_p03     |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
 public     | t1_range_int | range_p03     |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
 public     | t1_range_int | range_p04     |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int | range_p04     |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int | range_p04     |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int | range_p04     |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int |               |                  | c1      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,
34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int |               |                  | c2      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,
34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int |               |                  | c3      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,
34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int |               |                  | c4      | f         |         0 |         4 |         -1 |           -1 |                  |                   | {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,
34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49}
(24 rows)

Generate partition-level statistics of data in multiple columns.

gaussdb=# ALTER TABLE t1_range_int ADD STATISTICS ((c2, c3));
gaussdb=# ANALYZE t1_range_int WITH ALL;

View partition-level statistics of data in multiple columns.

gaussdb=# SELECT schemaname,tablename,partitionname,subpartitionname,attname,inherited,null_frac,avg_width,n_distinct,n_dndistinct,most_common_vals,most_common_freqs,histogram_bounds FROM pg_ext_stats WHERE tablename='t1_range_int' ORDER BY tablename,partitionname,attname;
 schemaname |  tablename   | partitionname | subpartitionname | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds 
------------+--------------+---------------+------------------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+------------------
 public     | t1_range_int | range_p00     |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
 public     | t1_range_int | range_p01     |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
 public     | t1_range_int | range_p02     |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
 public     | t1_range_int | range_p03     |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
 public     | t1_range_int | range_p04     |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
 public     | t1_range_int |               |                  | 2 3     | f         |         0 |         8 |         -1 |           -1 |                  |                   | 
(6 rows)

Create an expression index and generate partition-level statistics.

gaussdb=# CREATE INDEX t1_range_int_index ON t1_range_int(text(c1)) LOCAL;
gaussdb=# ANALYZE t1_range_int WITH ALL;

View the partition-level statistics of the expression index.

gaussdb=# SELECT schemaname,tablename,partitionname,subpartitionname,attname,inherited,null_frac,avg_width,n_distinct,n_dndistinct,most_common_vals,most_common_freqs,histogram_bounds FROM pg_stats WHERE tablename='t1_range_int_index' ORDER BY tablename,partitionname,attname;
 schemaname |     tablename      |   partitionname    | subpartitionname | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs |                                                               histogram_bounds    

------------+--------------------+--------------------+------------------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------------------------------------------------------------------
------------------------------------------------------------
 public     | t1_range_int_index | range_p00_text_idx |                  | text    | f         |         0 |         5 |         -1 |            0 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
 public     | t1_range_int_index | range_p01_text_idx |                  | text    | f         |         0 |         6 |         -1 |            0 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
 public     | t1_range_int_index | range_p02_text_idx |                  | text    | f         |         0 |         6 |         -1 |            0 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
 public     | t1_range_int_index | range_p03_text_idx |                  | text    | f         |         0 |         6 |         -1 |            0 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
 public     | t1_range_int_index | range_p04_text_idx |                  | text    | f         |         0 |         6 |         -1 |            0 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
 public     | t1_range_int_index |                    |                  | text    | f         |         0 |         5 |         -1 |            0 |                  |                   | {0,1,10,11,12,13,14,15,16,17,18,19,2,20,21,22,23,24,25,26,27,28,29,3,30,31,32,33,3
4,35,36,37,38,39,4,40,41,42,43,44,45,46,47,48,49,5,6,7,8,9}
(6 rows)