云数据库 GAUSSDB-级联收集统计信息:示例

时间:2024-11-13 10:04:57

示例

  • 创建分区表并插入数据
    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;
  • 级联收集统计信息
    gaussdb=# ANALYZE t1_range_int WITH ALL;
  • 查看分区级统计信息
    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        |        1 |        10
     range_p01    | p        |        1 |        10
     range_p02    | p        |        1 |        10
     range_p03    | p        |        1 |        10
     range_p04    | p        |        1 |        10
     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 |            0 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
     public     | t1_range_int | range_p00     |                  | c2      | f         |         0 |         4 |         -1 |            0 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
     public     | t1_range_int | range_p00     |                  | c3      | f         |         0 |         4 |         -1 |            0 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
     public     | t1_range_int | range_p00     |                  | c4      | f         |         0 |         4 |         -1 |            0 |                  |                   | {0,1,2,3,4,5,6,7,8,9}
     public     | t1_range_int | range_p01     |                  | c1      | f         |         0 |         4 |         -1 |            0 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
     public     | t1_range_int | range_p01     |                  | c2      | f         |         0 |         4 |         -1 |            0 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
     public     | t1_range_int | range_p01     |                  | c3      | f         |         0 |         4 |         -1 |            0 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
     public     | t1_range_int | range_p01     |                  | c4      | f         |         0 |         4 |         -1 |            0 |                  |                   | {10,11,12,13,14,15,16,17,18,19}
     public     | t1_range_int | range_p02     |                  | c1      | f         |         0 |         4 |         -1 |            0 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
     public     | t1_range_int | range_p02     |                  | c2      | f         |         0 |         4 |         -1 |            0 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
     public     | t1_range_int | range_p02     |                  | c3      | f         |         0 |         4 |         -1 |            0 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
     public     | t1_range_int | range_p02     |                  | c4      | f         |         0 |         4 |         -1 |            0 |                  |                   | {20,21,22,23,24,25,26,27,28,29}
     public     | t1_range_int | range_p03     |                  | c1      | f         |         0 |         4 |         -1 |            0 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
     public     | t1_range_int | range_p03     |                  | c2      | f         |         0 |         4 |         -1 |            0 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
     public     | t1_range_int | range_p03     |                  | c3      | f         |         0 |         4 |         -1 |            0 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
     public     | t1_range_int | range_p03     |                  | c4      | f         |         0 |         4 |         -1 |            0 |                  |                   | {30,31,32,33,34,35,36,37,38,39}
     public     | t1_range_int | range_p04     |                  | c1      | f         |         0 |         4 |         -1 |            0 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
     public     | t1_range_int | range_p04     |                  | c2      | f         |         0 |         4 |         -1 |            0 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
     public     | t1_range_int | range_p04     |                  | c3      | f         |         0 |         4 |         -1 |            0 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
     public     | t1_range_int | range_p04     |                  | c4      | f         |         0 |         4 |         -1 |            0 |                  |                   | {40,41,42,43,44,45,46,47,48,49}
     public     | t1_range_int |               |                  | c1      | f         |         0 |         4 |         -1 |            0 |                  |                   | {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 |            0 |                  |                   | {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 |            0 |                  |                   | {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 |            0 |                  |                   | {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)
  • 生成多列数据的分区级统计信息
    gaussdb=# ALTER TABLE t1_range_int ADD STATIS TICS  ((c2, c3));
    gaussdb=# ANALYZE t1_range_int WITH ALL;
  • 查看多列数据的分区级统计信息
    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 |            0 |                  |                   | 
     public     | t1_range_int | range_p01     |                  | 2 3     | f         |         0 |         8 |         -1 |            0 |                  |                   | 
     public     | t1_range_int | range_p02     |                  | 2 3     | f         |         0 |         8 |         -1 |            0 |                  |                   | 
     public     | t1_range_int | range_p03     |                  | 2 3     | f         |         0 |         8 |         -1 |            0 |                  |                   | 
     public     | t1_range_int | range_p04     |                  | 2 3     | f         |         0 |         8 |         -1 |            0 |                  |                   | 
     public     | t1_range_int |               |                  | 2 3     | f         |         0 |         8 |         -1 |            0 |                  |                   | 
    (6 rows)
  • 创建表达式索引并生成对应的分区级统计信息
    gaussdb=# CREATE INDEX t1_range_int_index ON t1_range_int(text(c1)) LOCAL;
    gaussdb=# ANALYZE t1_range_int WITH ALL;
  • 查看表达式索引的分区级统计信息
    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)
  • 删除分区表
    gaussdb=# DROP TABLE t1_range_int;
support.huaweicloud.com/fg-gaussdb-cent-v8/gaussdb-48-0088.html