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

Collecting Statistics in Cascading Mode

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.

  • 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)
  • Delete the partitioned table.
    gaussdb=# DROP TABLE t1_range_int;