1.查看有哪些分区表sspudb# select partrelid::regclass,* from pg_partitioned_table; partrelid | partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs --------------------------------------------------------------------------------------------------------------- sspudb_hash1 | 16703 | h | 1 | 0 | 1 | 10030 | 0 | sspudb_r1 | 16730 | r | 1 | 0 | 6 | 3128 | 0 | sspudb_list1 | 16718 | l | 1 | 16727 | 7 | 3126 | 100 | sspudb_rr1 | 16875 | r | 1 | 0 | 6 | 3128 | 0 | sspudb_rr1_p2010 | 16885 | r | 1 | 0 | 6 | 3128 | 0 | sspudb_rr1_p2011 | 16890 | r | 1 | 0 | 6 | 3128 | 0 | sspudb_rh1 | 16953 | r | 1 | 0 | 6 | 3128 | 0 | sspudb_rh1_phis | 16958 | h | 1 | 0 | 1 | 10030 | 0 | sspudb_rh1_p2010 | 16963 | h | 1 | 0 | 1 | 10030 | 0 | sspudb_rh1_p2011 | 16968 | h | 1 | 0 | 1 | 10030 | 0 | sspudb_rh1_pother | 16973 | h | 1 | 0 | 1 | 10030 | 0 | (11 rows)2.查看分区表和分区索引大小sspudb# dP List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description -------------------------------------------------------------------------------------------------------- public | sspudb_hash1 | sspu | partitioned table | | 24 kB | public | sspudb_list1 | sspu | partitioned table | | 16 kB | public | sspudb_r1 | sspu | partitioned table | | 64 kB | public | sspudb_rh1 | postgres | partitioned table | | 32 kB | public | sspudb_rr1 | postgres | partitioned table | | 24 kB | public | idx_sspudb_hash1_id | sspu | partitioned index | sspudb_hash1 | 56 kB | public | idx_sspudb_list1_region | sspu | partitioned index | sspudb_list1 | 40 kB | public | idx_sspudb_r1_joindate | sspu | partitioned index | sspudb_r1 | 160 kB | public | idx_sspudb_rh1_joindate_id | postgres | partitioned index | sspudb_rh1 | 96 kB | public | idx_sspudb_rr1_joindate | postgres | partitioned index | sspudb_rr1 | 104 kB | public | sspudb_hash1_pkey | sspu | partitioned index | sspudb_hash1 | 56 kB | public | sspudb_list1_pkey | sspu | partitioned index | sspudb_list1 | 40 kB | public | sspudb_r1_pkey | sspu | partitioned index | sspudb_r1 | 160 kB | public | sspudb_rh1_pkey | postgres | partitioned index | sspudb_rh1 | 96 kB | public | sspudb_rr1_pkey | postgres | partitioned index | sspudb_rr1 | 104 kB | (15 rows)3.d参数查看分区sspudb# d sspudb_r1; Partitioned table public.sspudb_r1 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ------------------------------------------------------------------------------------------------------------------------- id | numeric(20,0) | | not null | | main | | | name | character varying(20) | | not null | | extended | | | age | numeric(10,0) | | not null | | main | | | sex | character varying(10) | | not null | | extended | | | cardid | numeric(30,0) | | not null | | main | | | joindate | timestamp without time zone | | not null | | plain | | | region | character varying(12) | | not null | | extended | | | tel | character varying(12) | | not null | | extended | | | email | character varying(30) | | not null | | extended | | | recommend | character varying(10) | | | | extended | | | identifier | character varying(100) | | | | extended | | | Partition key: RANGE (joindate) Indexes: sspudb_r1_pkey PRIMARY KEY, btree (id, joindate) idx_sspudb_r1_joindate btree (joindate) Partitions: sspudb_r1_p2010 FOR VALUES FROM (2010-01-01 00:00:00) TO (2011-01-01 00:00:00), sspudb_r1_p2011 FOR VALUES FROM (2011-01-01 00:00:00) TO (2012-01-01 00:00:00), sspudb_r1_p2012 FOR VALUES FROM (2012-01-01 00:00:00) TO (2013-01-01 00:00:00), sspudb_r1_p2013 FOR VALUES FROM (2013-01-01 00:00:00) TO (2014-01-01 00:00:00), sspudb_r1_p2014 FOR VALUES FROM (2014-01-01 00:00:00) TO (2015-01-01 00:00:00), sspudb_r1_p2015 FOR VALUES FROM (2015-01-01 00:00:00) TO (2016-01-01 00:00:00), sspudb_r1_p2016 FOR VALUES FROM (2016-01-01 00:00:00) TO (2017-01-01 00:00:00), sspudb_r1_p2017 FOR VALUES FROM (2017-01-01 00:00:00) TO (2018-01-01 00:00:00), sspudb_r1_p2018 FOR VALUES FROM (2018-01-01 00:00:00) TO (2019-01-01 00:00:00), sspudb_r1_p2019 FOR VALUES FROM (2019-01-01 00:00:00) TO (2020-01-01 00:00:00), sspudb_r1_p2020 FOR VALUES FROM (2020-01-01 00:00:00) TO (2021-01-01 00:00:00), sspudb_r1_phis FOR VALUES FROM (MINVALUE) TO (2010-01-01 00:00:00) sspudb# dt sspudb_r1; List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description ----------------------------------------------------------------------------------------------- public | sspudb_r1 | partitioned table | sspu | permanent | | 0 bytes | (1 row)4.查看分区表数据分布SELECT tableoid::regclass,* FROM sspudb_rh1 LIMIT 4; sspudb# SELECT tableoid::regclass,* FROM sspudb_rh1 LIMIT 4; tableoid | id | name | age | sex | cardid | joindate | region | tel | email | re commend | identifier --------------------------------------------------------------------------------------------------------------------------------------------------- sspudb_rh1_p2010_2 | 1 | xsq1 | 18 | male | 622722199009121121 | 2010-10-01 10:10:10 | 北京 | 13651254654 | sspudb163.com | xsq | first_people sspudb_rh1_p2011_2 | 2 | xsq3 | 18 | male | 622722199003121121 | 2011-10-01 10:10:10 | 北京1 | 13641254654 | sspudb1163.com | xsq1 | first_people1 sspudb_rh1_pother_1 | 4 | xsq3 | 14 | male | 622722199403121121 | 2014-10-01 10:10:10 | 北京4 | 13641254654 | sspudb4163.com | xsq1 | first_people1 sspudb_rh1_pother_1 | 7 | xsq3 | 17 | male | 622722199703121121 | 2017-10-01 10:10:10 | 北京7 | 13647254654 | sspudb7163.com | xsq1 | first_people1 (4 rows)5.内置分区表与其分区的继承关系sspudb# select sspudb-# nmsp_parent.nspname as parent_schema, sspudb-# parent.relname as parent, sspudb-# nmsp_child.nspname as child_schema, sspudb-# child.relname as child_schema sspudb-# from pg_inherits join pg_class parent sspudb-# on pg_inherits.inhparent parent.oid join pg_class child sspudb-# on pg_inherits.inhrelid child.oid join pg_namespace nmsp_parent sspudb-# on nmsp_parent.oid parent.relnamespace join pg_namespace nmsp_child sspudb-# on nmsp_child.oid child.relnamespace sspudb-# where parent.relname sspudb_rh1; parent_schema | parent | child_schema | child_schema ------------------------------------------------------------ public | sspudb_rh1 | public | sspudb_rh1_phis public | sspudb_rh1 | public | sspudb_rh1_p2010 public | sspudb_rh1 | public | sspudb_rh1_p2011 public | sspudb_rh1 | public | sspudb_rh1_pother (4 rows)6.查看分区表的分区数量sspudb# select sspudb-# nspname, sspudb-# relname, sspudb-# count(*) as partition_num sspudb-# from sspudb-# pg_class c, sspudb-# pg_namespace n, sspudb-# pg_inherits i sspudb-# where c.oid i.inhparent sspudb-# and c.relnamespace n.oid sspudb-# and c.relhassubclass sspudb-# and c.relkind in (r,p) sspudb-# group by 1,2 sspudb-# order by partition_num desc; nspname | relname | partition_num ------------------------------------------- public | sspudb_r1 | 12 public | sspudb_hash1 | 4 public | sspudb_rh1 | 4 public | sspudb_rr1 | 4 public | sspudb_rr1_p2010 | 4 public | sspudb_rr1_p2011 | 4 public | sspudb_list1 | 3 public | sspudb_rh1_phis | 2 public | sspudb_rh1_pother | 2 public | sspudb_rh1_p2010 | 2 public | sspudb_rh1_p2011 | 2 (11 rows)7.检查表的分析时间sspudb# select relname,last_vacuum,last_analyze sspudb-# from pg_stat_all_tables where relname like sspudb_rh1%; relname | last_vacuum | last_analyze ------------------------------------------------ sspudb_rh1 | | sspudb_rh1_phis | | sspudb_rh1_p2010 | | sspudb_rh1_p2011 | | sspudb_rh1_pother | | sspudb_rh1_phis_1 | | sspudb_rh1_phis_2 | | sspudb_rh1_p2010_1 | | sspudb_rh1_p2010_2 | | sspudb_rh1_p2011_1 | | sspudb_rh1_p2011_2 | | sspudb_rh1_pother_1 | | sspudb_rh1_pother_2 | | (13 rows) --分析主表时子表全部被分析。 sspudb# analyze sspudb_rh1; ANALYZE sspudb# select relname,last_vacuum,last_analyze from pg_stat_all_tables where relname like sspudb_rh1%; relname | last_vacuum | last_analyze ----------------------------------------------------------------- sspudb_rh1 | | 2024-08-31 17:25:32.98380308 sspudb_rh1_phis | | 2024-08-31 17:25:32.98423608 sspudb_rh1_p2010 | | 2024-08-31 17:25:32.98445408 sspudb_rh1_p2011 | | 2024-08-31 17:25:32.98479208 sspudb_rh1_pother | | 2024-08-31 17:25:32.98528508 sspudb_rh1_phis_1 | | 2024-08-31 17:25:32.98572808 sspudb_rh1_phis_2 | | 2024-08-31 17:25:32.98578608 sspudb_rh1_p2010_1 | | 2024-08-31 17:25:32.98583608 sspudb_rh1_p2010_2 | | 2024-08-31 17:25:32.98605208 sspudb_rh1_p2011_1 | | 2024-08-31 17:25:32.98633708 sspudb_rh1_p2011_2 | | 2024-08-31 17:25:32.98650908 sspudb_rh1_pother_1 | | 2024-08-31 17:25:32.9868508 sspudb_rh1_pother_2 | | 2024-08-31 17:25:32.98723108 (13 rows)