【postgresql】分区表管理

最新推荐文章于 2024-08-31 23:36:42 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-08-31 23:36:42 发布

阅读量82

点赞数 1

分类专栏：数据库运维-PostgreSQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/141755649

版权

数据库运维-PostgreSQL 专栏收录该内容

40 篇文章 5 订阅

订阅专栏

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 | sspudb@163.com  | xsq       | first_people
 sspudb_rh1_p2011_2  |  2 | xsq3 |  18 | male | 622722199003121121 | 2011-10-01 10:10:10 | 北京1  | 13641254654 | sspudb1@163.com | xsq1      | first_people1
 sspudb_rh1_pother_1 |  4 | xsq3 |  14 | male | 622722199403121121 | 2014-10-01 10:10:10 | 北京4  | 13641254654 | sspudb4@163.com | xsq1      | first_people1
 sspudb_rh1_pother_1 |  7 | xsq3 |  17 | male | 622722199703121121 | 2017-10-01 10:10:10 | 北京7  | 13647254654 | sspudb7@163.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.983803+08
 sspudb_rh1_phis     |             | 2024-08-31 17:25:32.984236+08
 sspudb_rh1_p2010    |             | 2024-08-31 17:25:32.984454+08
 sspudb_rh1_p2011    |             | 2024-08-31 17:25:32.984792+08
 sspudb_rh1_pother   |             | 2024-08-31 17:25:32.985285+08
 sspudb_rh1_phis_1   |             | 2024-08-31 17:25:32.985728+08
 sspudb_rh1_phis_2   |             | 2024-08-31 17:25:32.985786+08
 sspudb_rh1_p2010_1  |             | 2024-08-31 17:25:32.985836+08
 sspudb_rh1_p2010_2  |             | 2024-08-31 17:25:32.986052+08
 sspudb_rh1_p2011_1  |             | 2024-08-31 17:25:32.986337+08
 sspudb_rh1_p2011_2  |             | 2024-08-31 17:25:32.986509+08
 sspudb_rh1_pother_1 |             | 2024-08-31 17:25:32.98685+08
 sspudb_rh1_pother_2 |             | 2024-08-31 17:25:32.987231+08
(13 rows)