分区表类型
openGauss数据库支持这些划分类型：
(1) 范围分区表：指定一个或多个列划分为多个范围，每个范围创建一个分区，用来存储相应的数据。
例如可以采用日期划分范围，将销售数据按照月份进行分区。
(2) 列表分区表：直接按照一个列上的值来划分出分区。例如可以采用销售门店划分销售数据。
(3) 间隔分区表：是一种特殊的范围分区，新增了间隔值定义。当插入记录找不到匹配的分区时可以根据间隔值自动创建分区。
(4) 哈希分区表：根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中。
范围分区表
RANGE 分区键最大数为16列
CREATE TABLE tp_r16 (
c1 int,
c2 int,
c3 int,
c4 int,
c5 int,
c6 int,
c7 int,
c8 int,
c9 int,
c10 int,
c11 int,
c12 int,
c13 int,
c14 int,
c15 int,
c16 int,
c17 int,
PRIMARY KEY (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16)
)
PARTITION BY RANGE (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) (
PARTITION p0 VALUES LESS THAN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16),
PARTITION p1 VALUES LESS THAN (2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16),
PARTITION p2 VALUES LESS THAN (3, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE, MAXVALUE)
);
auxdb=# \d+ tp_r16
Table "public.tp_r16"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | integer | not null | plain | |
c2 | integer | not null | plain | |
c3 | integer | not null | plain | |
c4 | integer | not null | plain | |
c5 | integer | not null | plain | |
c6 | integer | not null | plain | |
c7 | integer | not null | plain | |
c8 | integer | not null | plain | |
c9 | integer | not null | plain | |
c10 | integer | not null | plain | |
c11 | integer | not null | plain | |
c12 | integer | not null | plain | |
c13 | integer | not null | plain | |
c14 | integer | not null | plain | |
c15 | integer | not null | plain | |
c16 | integer | not null | plain | |
c17 | integer | | plain | |
Indexes:
"tp_r16_pkey" PRIMARY KEY, btree (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) LOCAL TABLESPACE pg
_default
Partition By RANGE(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16)
Number of partitions: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no1.语法格式
CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
PARTITION BY RANGE (partition_key)
(
PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
[, ... ]
);
2.参数说明
partition_table_name：分区表的名称
column_name：新表中要创建的字段名。
data_type：字段的数据类型。
partition_key：partition_key为分区键的名称。
注：对于从句是VALUE LESS THAN的语法格式，范围分区策略的分区键最多支持4列。
partition_name：partition_name为范围分区的名称。
VALUES LESS THAN：分区中的数值必须小于上边界值。
partition_value：范围分区的上边界，取值依赖于partition_key的类型。
MAXVALUE：表示分区的上边界，它通常用于设置最后一个范围分区的上边界。
3.分区表示例
示例1：创建范围分区表sales_table，含有4个分区，分区键为DATE类型。
分区的范围分别为：
sales_date<2022-04-01，
2022-04-01<= sales_date<2022-07-01，
2022-07-01<=sales_date< 2022-10-01，
2022-10-01 <= sales_date< MAXVALUE
--创建分区表sales_table。
auxdb=# CREATE TABLE sales_table
(
order_no INTEGER NOT NULL,
goods_name CHAR(20) NOT NULL,
sales_date DATE NOT NULL,
sales_volume INTEGER,
sales_store CHAR(20)
)
PARTITION BY RANGE(sales_date)
(
PARTITION season1 VALUES LESS THAN('2022-04-01 00:00:00'),
PARTITION season2 VALUES LESS THAN('2022-07-01 00:00:00'),
PARTITION season3 VALUES LESS THAN('2022-10-01 00:00:00'),
PARTITION season4 VALUES LESS THAN(MAXVALUE)
);
INSERT INTO sales_table VALUES(1, 'jacket', '2022-01-10 00:00:00', 3,'Alaska');
INSERT INTO sales_table VALUES(2, 'hat', '2022-05-06 00:00:00', 5,'Clolorado');
INSERT INTO sales_table VALUES(3, 'shirt', '2022-09-17 00:00:00', 7,'Florida');
INSERT INTO sales_table VALUES(4, 'coat', '2022-10-21 00:00:00', 9,'Hawaii');
--查询sales_table的数据
auxdb=# select * from sales_table;
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
1 | jacket | 2022-01-10 00:00:00 | 3 | Alaska
2 | hat | 2022-05-06 00:00:00 | 5 | Clolorado
2 | hat | 2022-05-06 00:00:00 | 5 | Clolorado
4 | coat | 2022-10-21 00:00:00 | 9 | Hawaii
--查询sales_table的4季度数据
auxdb=# SELECT * FROM sales_table PARTITION (season4);
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
4 | coat | 2022-10-21 00:00:00 | 9 | Hawaii
--查询sales_table的1季度数据
auxdb=# SELECT * FROM sales_table PARTITION FOR ('2021-01-01 00:00:00');
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
1 | jacket | 2022-01-10 00:00:00 | 3 | Alaska列表分区表
List分区支持16列
create table tp16 (
c1 int,
c2 int,
c3 int,
c4 int,
c5 int,
c6 int,
c7 int,
c8 int,
c9 int,
c10 int,
c11 int,
c12 int,
c13 int,
c14 int,
c15 int,
c16 int
)
partition by list (
c1,
c2,
c3,
c4,
c5,
c6,
c7,
c8,
c9,
c10,
c11,
c12,
c13,
c14,
c15,
c16
)
(
partition p1 values ((1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p2 values ((2,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p3 values ((3,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p4 values ((4,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p5 values ((5,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p6 values ((6,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p7 values ((7,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p8 values ((8,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p9 values ((9,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p10 values ((10,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p11 values ((11,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p12 values ((12,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p13 values ((13,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p14 values ((14,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p15 values ((15,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)),
partition p16 values ((16,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16))
);
omm=# \d tp16
Table "public.tp16"
Column | Type | Modifiers
--------+---------+-----------
c1 | integer |
c2 | integer |
c3 | integer |
c4 | integer |
c5 | integer |
c6 | integer |
c7 | integer |
c8 | integer |
c9 | integer |
c10 | integer |
c11 | integer |
c12 | integer |
c13 | integer |
c14 | integer |
c15 | integer |
c16 | integer |
Partition By LIST(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16)
Number of partitions: 16 (View pg_partition to check each partition range.)1.语法
CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
PARTITION BY LIST (partition_key)
(
PARTITION partition_name VALUES (list_values_clause)
[, ... ]
);
list_values_clause：对应分区存在的一个或者多个键值。多个键值之间以逗号分隔。
VALUES (DEFAULT)：加入的数据如有“list_values_clause”中未列出的键值，存放在VALUES (DEFAULT)对应的分区。
MAXVALUE：MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。
2.示例：
创建列表分区表graderecord。含有4个分区，分区键为CHAR类型。分区的范围分别为：21.01，21.02，21.03，21.04。
--创建分区表graderecord。
auxdb=# CREATE TABLE graderecord
(
number INTEGER,
name CHAR(20),
class CHAR(20),
grade INTEGER
)
PARTITION BY LIST(class)
(
PARTITION class_01 VALUES ('21.01'),
PARTITION class_02 VALUES ('21.02'),
PARTITION class_03 VALUES ('21.03'),
PARTITION class_04 VALUES ('21.04')
);
-- 数据插入分区。
insert into graderecord values('210101','Alan','21.01',92);
insert into graderecord values('210102','Ben','21.01',62);
insert into graderecord values('210103','Brain','21.01',26);
insert into graderecord values('210204','Carl','21.02',77);
insert into graderecord values('210205','David','21.02',47);
insert into graderecord values('210206','Eric','21.02',97);
insert into graderecord values('210307','Frank','21.03',90);
insert into graderecord values('210308','Gavin','21.03',100);
insert into graderecord values('210309','Henry','21.03',67);
insert into graderecord values('210410','Jack','21.04',75);
insert into graderecord values('210311','Jerry','21.04',60);
--查询graderecord的数据。
auxdb=# SELECT * FROM graderecord;
number | name | class | grade
--------+----------------------+----------------------+-------
210410 | Jack | 21.04 | 75
210311 | Jerry | 21.04 | 60
210307 | Frank | 21.03 | 90
210308 | Gavin | 21.03 | 100
210309 | Henry | 21.03 | 67
210204 | Carl | 21.02 | 77
210205 | David | 21.02 | 47
210206 | Eric | 21.02 | 97
210101 | Alan | 21.01 | 92
210102 | Ben | 21.01 | 62
210103 | Brain | 21.01 | 26
(11 rows)
--查询graderecord的class_01分区数据。
auxdb=# SELECT * FROM graderecord PARTITION (class_01);
number | name | class | grade
--------+----------------------+----------------------+-------
210101 | Alan | 21.01 | 92
210102 | Ben | 21.01 | 62
210103 | Brain | 21.01 | 26
(3 rows)
--查询graderecord的class_04分区数据。
auxdb=# SELECT * FROM graderecord PARTITION (class_04);
number | name | class | grade
--------+----------------------+----------------------+-------
210410 | Jack | 21.04 | 75
210311 | Jerry | 21.04 | 60
(2 rows)insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
insert into graderecord select * from graderecord PARTITION (class_01);
select count(*) from graderecord PARTITION (class_01);
analyze graderecord;
\x
select * from pg_partition where parentid in (select parentid from pg_partition where relname = 'graderecord') and relname = 'class_01';当前机制下，analyze的时候只更新分区表整体的统计信息（即只会更新pg_class中pages和tuples信息），不会收集各个子分区的统计信息（不会更新pg_partition中pages和tuples中统计信息），因此pg_partition中的pages和tuples信息在analyze前后都是0
select * from pg_class where relname = 'graderecord';间隔分区表
间隔分区是在范围分区的基础上，增加了间隔值
1.VALUES LESS THAN间隔分区语法格式
CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
PARTITION BY RANGE (partition_key)
(
INTERVAL ('interval_expr')
PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
[, ... ]
);
interval_expr自动创建分区的间隔，例如：自动创建分区的间隔，例如：1 day、1 month。
2.示例：
--创建分区表sales_table。
auxdb=# CREATE TABLE sales_table_int
(
order_no INTEGER NOT NULL,
goods_name CHAR(20) NOT NULL,
sales_date DATE NOT NULL,
sales_volume INTEGER,
sales_store CHAR(20)
)
PARTITION BY RANGE(sales_date)
INTERVAL ('1 month')
(
PARTITION start VALUES LESS THAN('2021-01-01 00:00:00'),
PARTITION later VALUES LESS THAN('2021-01-10 00:00:00')
);
-- 数据插入分区later
auxdb=# INSERT INTO sales_table_int VALUES(1, 'jacket', '2021-01-8 00:00:00', 3,'Alaska');
-- 不在已有分区的数据插入，系统会新建分区sys_p1。
auxdb=# INSERT INTO sales_table_int VALUES(2, 'hat', '2021-04-06 00:00:00', 255,'Clolorado');
-- 不在已有分区的数据插入，系统会新建分区sys_p2。
auxdb=# INSERT INTO sales_table_int VALUES(3, 'shirt', '2021-11-17 00:00:00', 7000,'Florida');
-- 数据插入分区start
auxdb=# INSERT INTO sales_table_int VALUES(4, 'coat', '2020-10-21 00:00:00', 9000,'Hawaii');
auxdb=# \d+ sales_table_int
Table "public.sales_table_int"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+--------------+-------------
order_no | integer | not null | plain | |
goods_name | character(20) | not null | extended | |
sales_date | timestamp(0) without time zone | not null | plain | |
sales_volume | integer | | plain | |
sales_store | character(20) | | extended | |
Partition By RANGE(sales_date) INTERVAL('1 month')
Number of partitions: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
-----------------+-------------+-------------------------
sales_table | |
season1 | | {"2022-04-01 00:00:00"}
season2 | | {"2022-07-01 00:00:00"}
season3 | | {"2022-10-01 00:00:00"}
season4 | | {NULL}
graderecord | |
class_01 | | {21.01}
class_02 | | {21.02}
class_03 | | {21.03}
class_04 | | {21.04}
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
--查询sales_table的数据。
auxdb=# SELECT * FROM sales_table_int;
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
(4 rows)
--查询sales_table的start分区数据。这里采用“sales_table_int PARTITION (start);”来引用分区。
auxdb=# SELECT * FROM sales_table_int PARTITION (start);
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
(1 row)
--查询sales_table的later分区数据。这里采用“sales_table_int PARTITION (later);”来引用分区。
auxdb=# SELECT * FROM sales_table_int PARTITION (later);
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
(1 row)
--查询sales_table的sys_p1分区数据。这里采用“sales_table_int PARTITION (sys_p1);”来引用分区。
auxdb=# SELECT * FROM sales_table_int PARTITION (sys_p1);
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
(1 row)
--查询sales_table的sys_p2分区数据。这里采用“sales_table_int PARTITION (sys_p2);”来引用分区。
auxdb=# SELECT * FROM sales_table_int PARTITION (sys_p2);
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
(1 row)
INSERT INTO sales_table_int VALUES(5, 'mengwl', '2021-06-17 00:00:00', 8888,'low');
auxdb=# \d+ sales_table_int
Table "public.sales_table_int"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+--------------+-------------
order_no | integer | not null | plain | |
goods_name | character(20) | not null | extended | |
sales_date | timestamp(0) without time zone | not null | plain | |
sales_volume | integer | | plain | |
sales_store | character(20) | | extended | |
Partition By RANGE(sales_date) INTERVAL('1 month')
Number of partitions: 5 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
-----------------+-------------+-------------------------
sales_table | |
season1 | | {"2022-04-01 00:00:00"}
season2 | | {"2022-07-01 00:00:00"}
season3 | | {"2022-10-01 00:00:00"}
season4 | | {NULL}
graderecord | |
class_01 | | {21.01}
class_02 | | {21.02}
class_03 | | {21.03}
class_04 | | {21.04}
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
sys_p3 | | {"2021-07-10 00:00:00"}
(16 rows)CREATE TABLE test_part(a timestamp, b int,c varchar(120))
PARTITION BY range(a) INTERVAL ('1 day')
(
PARTITION p1 VALUES LESS THAN('2000-01-01 00:00:00')
);
\timing on
insert into test_part select to_date((mod(generate_series,4000)+1)::text||'-'||(mod(generate_series,11)+1)::text||'-'||(mod(generate_series,27)+1)::text||' '||(mod(generate_series,22)+1)::text,'yyyy-mm-dd hh24'),generate_series,md5(generate_series) from generate_series(1,2000);
select relname,parttype,parentid,boundaries from pg_partition where parentid in (select parentid from pg_partition where relname = 'test_part');
insert into test_part select to_date((mod(generate_series,4000)+1)::text||'-'||(mod(generate_series,11)+1)::text||'-'||(mod(generate_series,27)+1)::text||' '||(mod(generate_series,22)+1)::text,'yyyy-mm-dd hh24'),generate_series,md5(generate_series) from generate_series(1,3000);
insert into test_part select to_date((mod(generate_series,4000)+1)::text||'-'||(mod(generate_series,11)+1)::text||'-'||(mod(generate_series,27)+1)::text||' '||(mod(generate_series,22)+1)::text,'yyyy-mm-dd hh24'),generate_series,md5(generate_series) from generate_series(1,4000);
insert into test_part select to_date((mod(generate_series,4000)+1)::text||'-'||(mod(generate_series,11)+1)::text||'-'||(mod(generate_series,27)+1)::text||' '||(mod(generate_series,22)+1)::text,'yyyy-mm-dd hh24'),generate_series,md5(generate_series) from generate_series(1,5000);
select max(boundaries) from pg_partition where parentid in (select parentid from pg_partition where relname = 'test_part');
select relname,a.parentid,max from
(select relname,parentid from pg_partition where parentid in (select parentid from pg_partition where relname = 'test_part') and parttype = 'r')a
left join
(select max(boundaries) max,parentid from pg_partition where parentid in (select parentid from pg_partition where relname = 'test_part') group by parentid) b on a.parentid = b.parentid;
对于该分区表，由于分区interval为一天，因此对于a超过2000-01-01 00:00:00的数据，每隔一天都会创建一个分区，数据生成过程中，基本上每条数据相隔一年，因此从1999条数据开始（该条数据开始a超过了2000-01-01 00:00:00），每条数据都会创建一个分区，从partitionno上可以看出来，插入2000条数据时，第1999、2000条数据均创建了分区，分区数为3，插入3000条数据时，相比于2000条多了1000个分区，需要新创建1000个分区，因此会出现运行时长增加，大部分为创建分区所耗费时间：
在分区创建完成之后，分区创建不会影响执行速度，因此该问题为正常现象--分区表事务
--间隔分区 rollback 回滚数据的同时会回滚ddl操作，且全局索引不会失效
--间隔分区 rollback 回滚数据的同时会回滚ddl操作，且全局索引不会失效
auxdb=# CREATE TABLE sales_table_int
(
order_no INTEGER NOT NULL,
goods_name CHAR(20) NOT NULL,
sales_date DATE NOT NULL,
sales_volume INTEGER,
sales_store CHAR(20)
)
PARTITION BY RANGE(sales_date)
INTERVAL ('1 month')
(
PARTITION start VALUES LESS THAN('2021-01-01 00:00:00'),
PARTITION later VALUES LESS THAN('2021-01-10 00:00:00')
);
INSERT INTO sales_table_int VALUES(1, 'jacket', '2021-01-8 00:00:00', 3,'Alaska');
INSERT INTO sales_table_int VALUES(2, 'hat', '2021-04-06 00:00:00', 255,'Clolorado');
INSERT INTO sales_table_int VALUES(3, 'shirt', '2021-11-17 00:00:00', 7000,'Florida');
INSERT INTO sales_table_int VALUES(4, 'coat', '2020-10-21 00:00:00', 9000,'Hawaii');
select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
-----------------+-------------+-------------------------
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
(5 rows)
create index on sales_table_int(order_no);
auxdb=# \d+ sales_table_int
Table "public.sales_table_int"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+--------------+-------------
order_no | integer | not null | plain | |
goods_name | character(20) | not null | extended | |
sales_date | timestamp(0) without time zone | not null | plain | |
sales_volume | integer | | plain | |
sales_store | character(20) | | extended | |
Indexes:
"sales_table_int_order_no_tableoid_idx" btree (order_no) TABLESPACE pg_default
Partition By RANGE(sales_date) INTERVAL('1 month')
Number of partitions: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# begin;
BEGIN
auxdb=# INSERT INTO sales_table_int VALUES(5, 'mengwl', '2021-03-17 00:00:00', 3333,'high');
INSERT 0 1
auxdb=# INSERT INTO sales_table_int VALUES(6, 'mengwl', '2021-06-17 00:00:00', 8888,'low');
INSERT 0 1
select relname,interval,boundaries from pg_partition ;
auxdb=# rollback;
ROLLBACK
auxdb=# select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
-----------------+-------------+-------------------------
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
(5 rows)
\d+ sales_table_int
Table "public.sales_table_int"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------+----------+--------------+-------------
order_no | integer | not null | plain | |
goods_name | character(20) | not null | extended | |
sales_date | timestamp(0) without time zone | not null | plain | |
sales_volume | integer | | plain | |
sales_store | character(20) | | extended | |
Indexes:
"sales_table_int_order_no_tableoid_idx" btree (order_no) TABLESPACE pg_default
Partition By RANGE(sales_date) INTERVAL('1 month')
Number of partitions: 4 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# select * from sales_table_int;
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
(4 rows)
auxdb=# INSERT INTO sales_table_int VALUES(5, 'mengwl', '2021-03-17 00:00:00', 3333,'high');
INSERT 0 1
auxdb=# INSERT INTO sales_table_int VALUES(6, 'mengwl', '2021-06-17 00:00:00', 8888,'low');
INSERT 0 1
auxdb=# select * from sales_table_int;
order_no | goods_name | sales_date | sales_volume | sales_store
----------+----------------------+---------------------+--------------+----------------------
4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
5 | mengwl | 2021-03-17 00:00:00 | 3333 | high
6 | mengwl | 2021-06-17 00:00:00 | 8888 | low
3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
(6 rows)
auxdb=# select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
-----------------+-------------+-------------------------
sys_p3 | | {"2021-07-10 00:00:00"}
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
(6 rows)哈希分区表
1.语法
CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
PARTITION BY HASH (partition_key)
(PARTITION partition_name )
[, ... ]
);
partition_name：哈希分区的名称。希望创建几个哈希分区就给出几个分区名。
2.创建哈希分区表hash_partition_table
auxdb=# create table hash_partition_table (
col1 int,
col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);
-- 数据插入
INSERT INTO hash_partition_table VALUES(1, 1);
INSERT INTO hash_partition_table VALUES(2, 2);
INSERT INTO hash_partition_table VALUES(3, 3);
INSERT INTO hash_partition_table VALUES(4, 4);
-- 查看数据
auxdb=# select * from hash_partition_table partition (p1);
col1 | col2
------+------
3 | 3
4 | 4
(2 rows)
auxdb=# select * from hash_partition_table partition (p2);
col1 | col2
------+------
1 | 1
2 | 2
(2 rows)分区表索引
MogDB 分区表支持两种索引：全局（global）索引和本地（local）索引。
默认索引是全局索引，创建本地索引需要指定local。
- | 添加分区 | 删除分区 | 备注 |
全局索引 | 生效 | 默认语法失效/update global index语法生效 | 索引失效后，需要reindex |
本地索引 | 生效 | 生效 |
concurrently 选项，它允许在不阻塞并发写操作的情况下重建索引
虽然 concurrently 选项提供了在不中断服务的情况下重建索引的能力，但它也有一些限制和潜在的问题：
- 它可能会比非并发索引重建花费更长的时间。
- 在创建并发索引的过程中，如果表的结构发生变化（例如，添加了新的列或修改了现有列的类型），则可能会导致索引创建失败。
- 并发索引创建可能会消耗更多的系统资源，包括 CPU、内存和 I/O。
reindex index concurrently index_name;
create index concurrently on table_name(索引字段);-- 查看失效索引
SELECT
i.relname AS index_name,
c.relname AS table_name
FROM
pg_index idx
JOIN
pg_class c ON c.oid = idx.indrelid
JOIN
pg_class i ON i.oid = idx.indexrelid
WHERE
idx.indisusable = 'f';create table part_index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
)partition by range(col2)
interval('1 day')
(
partition part1 values less than ('20210331'),
partition part2 values less than ('20210401')
);--全局索引
create index on part_index_test(col2,col1,col3);insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000);select relname,parttype,parentid,boundaries from pg_partition where parentid in (select parentid from pg_partition where relname = 'part_index_test');
relname | parttype | parentid | boundaries
-----------------+----------+----------+-------------------------
part_index_test | r | 61616 |
part1 | p | 61616 | {20210331}
part2 | p | 61616 | {20210401}
sys_p1 | p | 61616 | {"2021-04-02 00:00:00"}
sys_p2 | p | 61616 | {"2021-04-03 00:00:00"}
sys_p3 | p | 61616 | {"2021-04-04 00:00:00"}
sys_p4 | p | 61616 | {"2021-04-05 00:00:00"}
sys_p5 | p | 61616 | {"2021-04-06 00:00:00"}
sys_p6 | p | 61616 | {"2021-04-07 00:00:00"}
(9 rows)auxdb=# select * from part_index_test limit 10;
partid | col1 | col2 | col3
--------+------+---------------------+------
1 | 1 | 2021-04-01 00:00:00 | 1
2 | 1 | 2021-04-01 00:00:00 | 2
3 | 1 | 2021-04-01 00:00:00 | 3
4 | 1 | 2021-04-01 00:00:00 | 4
5 | 1 | 2021-04-01 00:00:00 | 5
6 | 1 | 2021-04-01 00:00:00 | 6
7 | 1 | 2021-04-01 00:00:00 | 7
8 | 1 | 2021-04-01 00:00:00 | 8
9 | 1 | 2021-04-01 00:00:00 | 9
10 | 1 | 2021-04-01 00:00:00 | 10
(10 rows)auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=4.33..27.98 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..4.33 rows=10 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)auxdb=# alter table part_index_test DROP PARTITION sys_p5;
ALTER TABLE
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=0.00..132.00 rows=2 width=16)
Iterations: 1
Selected Partitions: 3
-> Partitioned Seq Scan on part_index_test (cost=0.00..132.00 rows=2 width=16)
Filter: ((col2 = '2021-04-01 00:00:00'::timestamp without time zone) AND ((partid)::text = '10'::text))
(5 rows)auxdb=# \d+ part_index_test;
Table "public.part_index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"part_index_test_col2_col1_col3_tableoid_idx" btree (col2, col1, col3) TABLESPACE pg_default UNUSABLE
Partition By RANGE(col2) INTERVAL('1 day')
Number of partitions: 7 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no--reindex 全局索引
auxdb=# REINDEX INDEX part_index_test_col2_col1_col3_tableoid_idx;
REINDEX
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=27.75..84.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..27.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)--update global index;
auxdb=# alter table part_index_test DROP PARTITION sys_p4 update global index;
ALTER TABLE
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=27.75..84.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..27.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)-- 本地索引
auxdb=# drop index part_index_test_col2_col1_col3_tableoid_idx;
DROP INDEX
auxdb=# create index on part_index_test(col2,col1,col3) local;
CREATE INDEX
auxdb=# \d+ part_index_test;
Table "public.part_index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"part_index_test_col2_col1_col3_idx" btree (col2, col1, col3) LOCAL TABLESPACE pg_default
Partition By RANGE(col2) INTERVAL('1 day')
Number of partitions: 6 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=noauxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=11.75..68.75 rows=2 width=16)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=11.75..68.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_col3_idx (cost=0.00..11.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)auxdb=# alter table part_index_test DROP PARTITION sys_p6;
ALTER TABLE
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=11.75..68.75 rows=2 width=16)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=11.75..68.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_col3_idx (cost=0.00..11.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)--调换本地分区键位置
auxdb=# drop index part_index_test_col2_col1_col3_idx;
DROP INDEX
auxdb=# create index on part_index_test(col1,col2,col3) local;
CREATE INDEXauxdb=# \d+ part_index_test
Table "public.part_index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"part_index_test_col1_col2_col3_idx" btree (col1, col2, col3) LOCAL TABLESPACE pg_default
Partition By RANGE(col2) INTERVAL('1 day')
Number of partitions: 7 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=noauxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=0.00..60.83 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Seq Scan on part_index_test (cost=0.00..60.83 rows=1 width=136)
Filter: ((col2 = '2021-04-01 00:00:00'::timestamp without time zone) AND ((partid)::text = '10'::text))
(5 rows)auxdb=# analyze part_index_test;
ANALYZE
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=0.00..110.00 rows=2 width=16)
Iterations: 1
Selected Partitions: 3
-> Partitioned Seq Scan on part_index_test (cost=0.00..110.00 rows=2 width=16)
Filter: ((col2 = '2021-04-01 00:00:00'::timestamp without time zone) AND ((partid)::text = '10'::text))
(5 rows)auxdb=# \d+ part_index_test
Table "public.part_index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"part_index_test_col1_col2_col3_idx" btree (col1, col2, col3) LOCAL TABLESPACE pg_default
Partition By RANGE(col2) INTERVAL('1 day')
Number of partitions: 7 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=noauxdb=# drop index part_index_test_col1_col2_col3_idx;
DROP INDEX
auxdb=# create index on part_index_test(col2,col1,col3) local;
CREATE INDEX
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=11.75..61.75 rows=2 width=16)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=11.75..61.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_col3_idx (cost=0.00..11.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)分区表维护
删除分区：
ALTER TABLE partition_table_name DROP PARTITION partition_name;
增加分区：
ALTER TABLE partition_table_name ADD {partition_less_than_item | partition_start_end_item| partition_list_item };
重命名分区：
ALTER TABLE partition_table_name RENAME PARTITION partition_name TO partition_new_name;
分裂分区（指定切割点split_partition_value的语法）：
ALTER TABLE partition_table_name SPLIT PARTITION partition_name AT ( split_partition_value ) INTO ( PARTITION partition_new_name1, PARTITION partition_new_name2);
分裂分区（指定分区范围的语法）：
ALTER TABLE partition_table_name SPLIT PARTITION partition_name INTO { ( partition_less_than_item [, …] ) | ( partition_start_end_item [, …] ) };
合并分区：
ALTER TABLE partition_table_name MERGE PARTITIONS { partition_name } [, …] INTO PARTITION partition_name;--创建分区表employees_table。
auxdb=# CREATE TABLE employees_table
(
employee_id INTEGER NOT NULL,
employee_name CHAR(20) NOT NULL,
onboarding_date DATE NOT NULL,
position CHAR(20)
)
PARTITION BY RANGE(onboarding_date)
(
PARTITION founders VALUES LESS THAN('2000-01-01 00:00:00'),
PARTITION senate VALUES LESS THAN('2010-01-01 00:00:00'),
PARTITION seniors VALUES LESS THAN('2020-01-01 00:00:00'),
PARTITION newcomer VALUES LESS THAN(MAXVALUE)
);
-- 插入数据
auxdb=# INSERT INTO employees_table VALUES
(1, 'SMITH', '1997-01-10 00:00:00','Manager'),
(2, 'JONES', '2001-05-06 00:00:00', 'Supervisor'),
(3, 'WILLIAMS', '2011-09-17 00:00:00', 'Engineer'),
(4, 'TAYLOR', '2021-10-21 00:00:00', 'Clerk');
查看newcomer分区
auxdb=# SELECT * FROM employees_table PARTITION (newcomer);
employee_id | employee_name | onboarding_date | position
-------------+----------------------+---------------------+----------------------
4 | TAYLOR | 2021-10-21 00:00:00 | Clerk
(1 row)
--删除newcomer分区。
auxdb=# ALTER TABLE employees_table DROP PARTITION newcomer;
-- 查看newcomer分区数据
auxdb=# select * from employees_table partition (newcomer);
ERROR: partition "newcomer" of relation "employees_table" does not exist
--增加fresh分区。
auxdb=# ALTER TABLE employees_table ADD PARTITION fresh VALUES LESS THAN ('2040-01-01 00:00:00');
ALTER TABLE
--以2030-01-01 00:00:00为分割点，分裂fresh分区为current、future两个分区
auxdb=# ALTER TABLE employees_table SPLIT PARTITION fresh AT ('2030-01-01 00:00:00') INTO (PARTITION current, PARTITION future);
auxdb=# select relname,interval,boundaries from pg_partition ;
relname | interval | boundaries
----------------------+-------------+-------------------------
sales_table | |
season1 | | {"2022-04-01 00:00:00"}
season2 | | {"2022-07-01 00:00:00"}
season3 | | {"2022-10-01 00:00:00"}
season4 | | {NULL}
graderecord | |
class_01 | | {21.01}
class_02 | | {21.02}
class_03 | | {21.03}
class_04 | | {21.04}
sales_table_int | {"1 month"} |
start | | {"2021-01-01 00:00:00"}
later | | {"2021-01-10 00:00:00"}
sys_p1 | | {"2021-04-10 00:00:00"}
sys_p2 | | {"2021-12-10 00:00:00"}
sys_p3 | | {"2021-07-10 00:00:00"}
hash_partition_table | |
p1 | | {0}
p2 | | {1}
employees_table | |
founders | | {"2000-01-01 00:00:00"}
senate | | {"2010-01-01 00:00:00"}
seniors | | {"2020-01-01 00:00:00"}
current | | {"2030-01-01 00:00:00"}
future | | {"2040-01-01 00:00:00"}
(25 rows)
--将分区current改名为now
auxdb=# ALTER TABLE employees_table RENAME PARTITION current TO now;
employees_table | |
founders | | {"2000-01-01 00:00:00"}
senate | | {"2010-01-01 00:00:00"}
seniors | | {"2020-01-01 00:00:00"}
future | | {"2040-01-01 00:00:00"}
now | | {"2030-01-01 00:00:00"}
--将founders，senate合并为一个分区original。
auxdb=# ALTER TABLE employees_table MERGE PARTITIONS founders, senate INTO PARTITION original;
employees_table | |
seniors | | {"2020-01-01 00:00:00"}
future | | {"2040-01-01 00:00:00"}
now | | {"2030-01-01 00:00:00"}
original | | {"2010-01-01 00:00:00"}分区交换
分区表和普通表交换
注意点：
一：普通表必须要有本地分区索引，不能有全局索引（和分区表索引对上）
二：update global index 全局索引才不会失效
三：本地索引不会失效
--全局索引切换
create table part_index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
)partition by range(col2)
interval('1 day')
(
partition part1 values less than ('20210331'),
partition part2 values less than ('20210401')
);
create index on part_index_test(col2,col1,col3);
select relname,parttype,parentid,boundaries from pg_partition where parentid in (select parentid from pg_partition where relname = 'part_index_test');
insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000);
create table index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
);
create index on index_test(col2,col1,col3);
explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=4.33..27.98 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..4.33 rows=10 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)
-- 普通表不能有全局索引
alter table part_index_test exchange partition(sys_p5) with table index_test with VALIDATION verbose; -- 失效
ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indexs
auxdb=# \d+ index_test;
Table "public.index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"index_test_col2_col1_col3_idx" btree (col2, col1, col3) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# drop index index_test_col2_col1_col3_idx;
DROP INDEX
alter table part_index_test exchange partition(sys_p5) with table index_test with VALIDATION verbose;
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=0.00..60.83 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Seq Scan on part_index_test (cost=0.00..60.83 rows=1 width=136)
Filter: ((col2 = '2021-04-01 00:00:00'::timestamp without time zone) AND ((partid)::text = '10'::text))
(5 rows)
auxdb=# \d+ part_index_test
Table "public.part_index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"part_index_test_col2_col1_col3_tableoid_idx" btree (col2, col1, col3) TABLESPACE pg_default UNUSABLE
Partition By RANGE(col2) INTERVAL('1 day')
Number of partitions: 8 (View pg_partition to check each partition range.)
Has OIDs: no
Options: orientation=row, compression=no
-- reindex 索引
auxdb=# reindex index part_index_test_col2_col1_col3_tableoid_idx;
REINDEX
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=4.32..24.98 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..4.32 rows=9 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)
auxdb=# truncate table index_test;
TRUNCATE TABLE
auxdb=# select count(*) from index_test;
count
-------
0
(1 row)
-- update global index
alter table part_index_test exchange partition(sys_p2) with table index_test with VALIDATION verbose update global index; -- 正常
auxdb=# select count(*) from index_test;
count
-------
1000
(1 row)
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on part_index_test (cost=31.75..81.75 rows=2 width=16)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on part_index_test_col2_col1_col3_tableoid_idx (cost=0.00..31.75 rows=1000 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(5 rows)--本地索引切换
create table part_index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
)partition by range(col2)
interval('1 day')
(
partition part1 values less than ('20210331'),
partition part2 values less than ('20210401')
);
create index on part_index_test(col2,col1,col3) local;
select relname,parttype,parentid,boundaries from pg_partition where parentid in (select parentid from pg_partition where relname = 'part_index_test');
insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000);
create table index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
);
-- create index on index_test(col2,col1,col3);
explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
--------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=4.33..27.98 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=4.33..27.98 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_col3_idx (cost=0.00..4.33 rows=10 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)
-- 普通表要有本地索引
alter table part_index_test exchange partition(sys_p5) with table index_test with VALIDATION verbose;
ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indexs
create index on index_test(col2,col1,col3);
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=4.32..24.98 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=4.32..24.98 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_col3_idx (cost=0.00..4.32 rows=9 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)
explain select * from index_test where col2 = '2021-04-05 00:00:00' and partid = '10';
auxdb=# explain select * from index_test where col2 = '2021-04-05 00:00:00' and partid = '10';
QUERY PLAN
--------------------------------------------------------------------------------------------
Bitmap Heap Scan on index_test (cost=4.27..9.61 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-05 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Bitmap Index Scan on index_test_col2_col1_col3_idx (cost=0.00..4.26 rows=2 width=0)
Index Cond: (col2 = '2021-04-05 00:00:00'::timestamp without time zone)
(5 rows)--本地全局都有
create table part_index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
)partition by range(col2)
interval('1 day')
(
partition part1 values less than ('20210331'),
partition part2 values less than ('20210401')
);
create index on part_index_test(col2,col1) local;
create index on part_index_test(partid);
select relname,parttype,parentid,boundaries from pg_partition where parentid in (select parentid from pg_partition where relname = 'part_index_test');
insert into part_index_test select generate_series(1,1000),'1','20210401',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210402',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210403',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210404',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210405',generate_series(1,1000);
insert into part_index_test select generate_series(1,1000),'1','20210406',generate_series(1,1000);
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=8.90..12.92 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=8.90..12.92 rows=1 width=136)
Recheck Cond: (((partid)::text = '10'::text) AND (col2 = '2021-04-01 00:00:00'::timestamp without time zone))
-> BitmapAnd (cost=8.90..8.90 rows=1 width=0)
-> Bitmap Index Scan on part_index_test_partid_tableoid_idx (cost=0.00..4.33 rows=10 width=0)
Index Cond: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_idx (cost=0.00..4.33 rows=10 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(10 rows)
create table index_test(
partid varchar(32) not null,
col1 varchar(2) not null,
col2 date not null,
col3 varchar(8) not null
);
create index on index_test(col2,col1);
create index on index_test(partid);
--删除全局索引
alter table part_index_test exchange partition(sys_p2) with table index_test with VALIDATION verbose update global index;
ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indexs
auxdb=# \d+ index_test
Table "public.index_test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+--------------------------------+-----------+----------+--------------+-------------
partid | character varying(32) | not null | extended | |
col1 | character varying(2) | not null | extended | |
col2 | timestamp(0) without time zone | not null | plain | |
col3 | character varying(8) | not null | extended | |
Indexes:
"index_test_col2_col1_idx" btree (col2, col1) TABLESPACE pg_default
"index_test_partid_idx" btree (partid) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
auxdb=# drop index index_test_partid_idx;
DROP INDEX
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=8.89..12.90 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=8.89..12.90 rows=1 width=136)
Recheck Cond: (((partid)::text = '10'::text) AND (col2 = '2021-04-01 00:00:00'::timestamp without time zone))
-> BitmapAnd (cost=8.89..8.89 rows=1 width=0)
-> Bitmap Index Scan on part_index_test_partid_tableoid_idx (cost=0.00..4.32 rows=9 width=0)
Index Cond: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_idx (cost=0.00..4.32 rows=9 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(10 rows)
alter table part_index_test exchange partition(sys_p3) with table index_test with VALIDATION verbose;
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=4.30..21.91 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=4.30..21.91 rows=1 width=136)
Recheck Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
Filter: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_idx (cost=0.00..4.30 rows=7 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(8 rows)
auxdb=# reindex index part_index_test_partid_tableoid_idx;
REINDEX
auxdb=# explain select * from part_index_test where col2 = '2021-04-01 00:00:00' and partid = '10';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Partition Iterator (cost=8.86..12.87 rows=1 width=136)
Iterations: 1
Selected Partitions: 3
-> Partitioned Bitmap Heap Scan on part_index_test (cost=8.86..12.87 rows=1 width=136)
Recheck Cond: (((partid)::text = '10'::text) AND (col2 = '2021-04-01 00:00:00'::timestamp without time zone))
-> BitmapAnd (cost=8.86..8.86 rows=1 width=0)
-> Bitmap Index Scan on part_index_test_partid_tableoid_idx (cost=0.00..4.30 rows=7 width=0)
Index Cond: ((partid)::text = '10'::text)
-> Partitioned Bitmap Index Scan on part_index_test_col2_col1_idx (cost=0.00..4.30 rows=7 width=0)
Index Cond: (col2 = '2021-04-01 00:00:00'::timestamp without time zone)
(10 rows)普通表转成分区表
没有类似oracle 的在线重定义功能，只能先创建分区表，将普通表的数据insert into select 的方式导入分区表
数据归档
select * from part_index_test Partition(sys_p2);
gsql -h 192.168.100.81 -p26000 -Udatamt -WSge@123456 auxdb -c "\copy (select * from part_index_test Partition(sys_p2)) to '/tmp/test' WITH (delimiter'|||')"
alter table part_index_test truncate partition sys_p2;
gsql -h 192.168.100.81 -p26000 -Udatamt -WSge@123456 auxdb -c "\copy part_index_test from '/tmp/test' WITH (delimiter'|||')"
select * from part_index_test Partition(sys_p2);BUG
Hash分区表无法通过pg_get_tabledef获取表定义
create table t_p_range_hash(id numeric primary key,
col_varchar varchar(20),
col_date date,
col_timestamp TIMESTAMP(6))
partition by range(id) subpartition by hash(col_date)
(
partition p1 values less than (50000)
(
subpartition p1_1,
subpartition p1_2
)
);
create table t_p_hash(id numeric primary key,
col_varchar varchar(20),
col_date date,
col_timestamp TIMESTAMP(6))
partition by hash(col_date)
(
partition p1,
partition p2
);
auxdb=# select pg_get_tabledef('t_p_hash');
ERROR: date/time field value out of range: "0"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: SQL statement "SELECT /*+ hashjoin(p t) */p.relname AS partname, p.boundaries[1] AS partboundary, p.oid AS partoid, p.reltablespace AS spcoid,t.spcname AS reltblspc FROM pg_partition p LEFT JOIN pg_tablespace t ON p.reltablespace = t.oid WHERE p.parentid = 57518 AND p.parttype = 'p' AND p.partstrategy = 'h' ORDER BY p.boundaries[1]::timestamp ASC"
referenced column: pg_get_tabledef
auxdb=# select pg_get_tabledef('t_p_range_hash');
ERROR: date/time field value out of range: "0"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: SQL statement "SELECT /*+ hashjoin(p t) */ p.relname AS partName, array_to_string(p.boundaries, ',') as partbound, array_to_string(p.boundaries, ''',''') as partboundstr, p.reltablespace AS spcoid,t.spcname AS reltblspc FROM pg_partition p LEFT JOIN pg_tablespace t ON p.reltablespace = t.oid WHERE p.parentid = 57507 AND p.parttype = 's' AND p.partstrategy = 'h' ORDER BY p.boundaries[1]::timestamp ASC"
referenced column: pg_get_tabledef
