示例

• 创建分区表并插入数据

  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,34,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;