【postgresql】pg组合分区之范围哈希分区

戒掉贪嗔痴(薛双奇)

于 2024-08-31 17:09:40 发布

阅读量58

点赞数 1

分类专栏：数据库运维-PostgreSQL 文章标签： postgresql 哈希算法 java

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

版权

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

40 篇文章 5 订阅

订阅专栏

1.主表创建

create table sspudb_rh1
(
id numeric(20,0) NOT NULL,
name character varying(20) NOT NULL,
age numeric(10,0) NOT NULL,
sex character varying(10) NOT NULL,
cardid numeric(30,0) NOT NULL,
joindate timestamp without time zone NOT NULL,
region character varying(12) NOT NULL,
tel character varying(12) NOT NULL,
email character varying(30) NOT NULL,
recommend character varying(10),
identifier character varying(100),
primary key (id,joindate)
)
PARTITION BY RANGE(joindate);

2.子表创建

--一级子分区创建 
create table sspudb_rh1_phis partition OF sspudb_rh1 for values from (MINVALUE) TO ('2010-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_p2010 partition of sspudb_rh1 for values from ('2010-01-01 00:00:00') to ('2011-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_p2011 partition of sspudb_rh1 for values from ('2011-01-01 00:00:00') to ('2012-01-01 00:00:00') PARTITION BY HASH (id);
create table sspudb_rh1_pother partition of sspudb_rh1 for values from ('2012-01-01 00:00:00') to (MAXVALUE) PARTITION BY HASH (id);

3.创建二级hash子分区

--sspudb_rh1_phis 分区建两个子分区
CREATE TABLE sspudb_rh1_phis_1 PARTITION OF sspudb_rh1_phis FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_phis_2 PARTITION OF sspudb_rh1_phis FOR VALUES WITH (modulus 2, remainder 1);

--sspudb_rh1_p2010 分区建两个子分区 
CREATE TABLE sspudb_rh1_p2010_1 PARTITION OF sspudb_rh1_p2010 FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_p2010_2 PARTITION OF sspudb_rh1_p2010 FOR VALUES WITH (modulus 2, remainder 1);

--sspudb_rh1_p2011 分区建两个子分区 
CREATE TABLE sspudb_rh1_p2011_1 PARTITION OF sspudb_rh1_p2011 FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_p2011_2 PARTITION OF sspudb_rh1_p2011 FOR VALUES WITH (modulus 2, remainder 1);

--sspudb_rh1_pother 分区建两个子分区
CREATE TABLE sspudb_rh1_pother_1 PARTITION OF sspudb_rh1_pother FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE sspudb_rh1_pother_2 PARTITION OF sspudb_rh1_pother FOR VALUES WITH (modulus 2, remainder 1);

4.创建全局索引

CREATE INDEX idx_sspudb_rh1_joindate_id ON sspudb_rh1 USING BTREE(joindate,id);
sspudb=# insert into sspudb_rh1 select * from sspudb_r1;
INSERT 0 8

sspudb=# select * from sspudb_rh1;
 id | name | age | sex  |       cardid       |      joindate       | region |     tel     |      email      | recommend |  identifier 
----+------+-----+------+--------------------+---------------------+--------+-------------+-----------------+-----------+---------------
  1 | xsq1 |  18 | male | 622722199009121121 | 2010-10-01 10:10:10 | 北京   | 13651254654 | sspudb@163.com  | xsq       | first_people
  2 | xsq3 |  18 | male | 622722199003121121 | 2011-10-01 10:10:10 | 北京1  | 13641254654 | sspudb1@163.com | xsq1      | first_people1
  4 | xsq3 |  14 | male | 622722199403121121 | 2014-10-01 10:10:10 | 北京4  | 13641254654 | sspudb4@163.com | xsq1      | first_people1
  7 | xsq3 |  17 | male | 622722199703121121 | 2017-10-01 10:10:10 | 北京7  | 13647254654 | sspudb7@163.com | xsq1      | first_people1
  3 | xsq3 |  19 | male | 622722199033121121 | 2012-10-01 10:10:10 | 北京3  | 13641254634 | sspudb3@163.com | xsq3      | first_people1
  5 | xsq3 |  15 | male | 622722199503121121 | 2015-10-01 10:10:10 | 北京5  | 13645254654 | sspudb1@163.com | xsq1      | first_people1
  6 | xsq6 |  16 | male | 622722199603121121 | 2016-10-01 10:10:10 | 北京6  | 13641256654 | sspudb6@163.com | xsq6      | first_people1
  8 | xsq8 |  18 | male | 622722199803121121 | 2018-10-01 10:10:10 | 北京8  | 13648254654 | sspudb8@163.com | xsq1      | first_people1
(8 rows)

sspudb=# SELECT v.tableoid::regclass,count(v.*) FROM sspudb_rh1 v GROUP BY v.tableoid;
      tableoid       | count 
---------------------+-------
 sspudb_rh1_pother_2 |     4
 sspudb_rh1_p2011_2  |     1
 sspudb_rh1_pother_1 |     2
 sspudb_rh1_p2010_2  |     1
(4 rows)


select
nmsp_parent.nspname as parent_schema,
parent.relname as parent,
nmsp_child.nspname as child_schema,
child.relname as child_schema
from pg_inherits 
join pg_class parent on pg_inherits.inhparent = parent.oid 
join pg_class child on pg_inherits.inhrelid = child.oid 
join pg_namespace nmsp_parent on nmsp_parent.oid = parent.relnamespace 
join pg_namespace nmsp_child on nmsp_child.oid = child.relnamespace
where parent.relname like 'sspudb_rh1%';

 parent_schema |              parent               | child_schema |            child_schema             
---------------+-----------------------------------+--------------+-------------------------------------
 public        | sspudb_rh1                        | public       | sspudb_rh1_phis
 public        | sspudb_rh1_pkey                   | public       | sspudb_rh1_phis_pkey
 public        | sspudb_rh1                        | public       | sspudb_rh1_p2010
 public        | sspudb_rh1_pkey                   | public       | sspudb_rh1_p2010_pkey
 public        | sspudb_rh1                        | public       | sspudb_rh1_p2011
 public        | sspudb_rh1_pkey                   | public       | sspudb_rh1_p2011_pkey
 public        | sspudb_rh1                        | public       | sspudb_rh1_pother
 public        | sspudb_rh1_pkey                   | public       | sspudb_rh1_pother_pkey
 public        | sspudb_rh1_phis                   | public       | sspudb_rh1_phis_1
 public        | sspudb_rh1_phis_pkey              | public       | sspudb_rh1_phis_1_pkey
 public        | sspudb_rh1_phis                   | public       | sspudb_rh1_phis_2
 public        | sspudb_rh1_phis_pkey              | public       | sspudb_rh1_phis_2_pkey
 public        | sspudb_rh1_p2010                  | public       | sspudb_rh1_p2010_1
 public        | sspudb_rh1_p2010_pkey             | public       | sspudb_rh1_p2010_1_pkey
 public        | sspudb_rh1_p2010                  | public       | sspudb_rh1_p2010_2
 public        | sspudb_rh1_p2010_pkey             | public       | sspudb_rh1_p2010_2_pkey
 public        | sspudb_rh1_p2011                  | public       | sspudb_rh1_p2011_1
 public        | sspudb_rh1_p2011_pkey             | public       | sspudb_rh1_p2011_1_pkey
 public        | sspudb_rh1_p2011                  | public       | sspudb_rh1_p2011_2
 public        | sspudb_rh1_p2011_pkey             | public       | sspudb_rh1_p2011_2_pkey
 public        | sspudb_rh1_pother                 | public       | sspudb_rh1_pother_1
 public        | sspudb_rh1_pother_pkey            | public       | sspudb_rh1_pother_1_pkey
 public        | sspudb_rh1_pother                 | public       | sspudb_rh1_pother_2
 public        | sspudb_rh1_pother_pkey            | public       | sspudb_rh1_pother_2_pkey
 public        | sspudb_rh1_phis_joindate_id_idx   | public       | sspudb_rh1_phis_1_joindate_id_idx
 public        | sspudb_rh1_phis_joindate_id_idx   | public       | sspudb_rh1_phis_2_joindate_id_idx
 public        | sspudb_rh1_p2010_joindate_id_idx  | public       | sspudb_rh1_p2010_1_joindate_id_idx
 public        | sspudb_rh1_p2010_joindate_id_idx  | public       | sspudb_rh1_p2010_2_joindate_id_idx
 public        | sspudb_rh1_p2011_joindate_id_idx  | public       | sspudb_rh1_p2011_1_joindate_id_idx
 public        | sspudb_rh1_p2011_joindate_id_idx  | public       | sspudb_rh1_p2011_2_joindate_id_idx
 public        | sspudb_rh1_pother_joindate_id_idx | public       | sspudb_rh1_pother_1_joindate_id_idx
 public        | sspudb_rh1_pother_joindate_id_idx | public       | sspudb_rh1_pother_2_joindate_id_idx
(32 rows)