1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL: 分区表应用之二(取模分区)

• 数据库

在 PostgreSQL 中，分区表的使用并不像 oracle 那么智能， PostgreSQL 中是靠继承和触发器来实现分区表的，由于trigger 的使用，当业务繁忙时会大大降低数据库性能，所以 trigger 并不建议使用；因此 PostgreSQL 中的分区表对应用不再透明，例如，如果不使用 trigger，那么插入时程序需要指定子表等。今天测试了下取模分区的场景：以下为详细步骤

创建父表

创建父表并插入测试数据，如下:

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
 
francs=> create table tbl_name (id int4 primary key,name varchar(32),remark varchar(64));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tbl_name_pkey" for table "tbl_name"  
CREATE TABLE

francs=> insert into tbl_name select generate_series(1,20000000),generate_series(1,20000000) || '_a','0';  
INSERT 0 20000000

francs=> select * from tbl_name limit 10;  
id | name | remark  
----+------+--------  
 1 | 1_a | 0  
 2 | 2_a | 0  
 3 | 3_a | 0  
 4 | 4_a | 0  
 5 | 5_a | 0  
 6 | 6_a | 0  
 7 | 7_a | 0  
 8 | 8_a | 0  
 9 | 9_a | 0  
10 | 10_a | 0  
(10 rows)

francs=> create unique index concurrently idx_tbl_name_name on tbl_name using btree (name);  
CREATE INDEX

francs=> /d tbl_name  
 Table "francs.tbl_name"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer | not null  
name | character varying(32) |  
remark | character varying(64) |  
Indexes:  
 "tbl_name_pkey" PRIMARY KEY, btree (id)  
 "idx_tbl_name_name" UNIQUE, btree (name)

父表备份

1
2
3
4
5
6
7
8
9
 
francs=> create table tbl_name_old as select * from tbl_name;  
SELECT 20000000

francs=> alter table tbl_name_old add primary key(id);  
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tbl_name_old_pkey" for table "tbl_name_old"  
ALTER TABLE

francs=> create unique index concurrently idx_tbl_name_old_name on tbl_name_old using btree (name);  
CREATE INDEX

备注：这里备份父表，用于之后做性能比较，因为分表后 tbl_name 数据要清空。

创建子表

创建子表， 分区字段 id，如下：

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
 
create table tbl_name_0 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_1 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_2 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_3 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_4 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_5 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_6 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_7 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_8 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_9 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_10 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_11 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_12 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_13 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_14 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_15 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_16 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_17 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_18 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_19 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_20 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_21 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_22 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_23 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_24 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_25 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_26 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_27 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_28 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_29 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_30 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );  
create table tbl_name_31 ( like tbl_name including constraints including defaults INCLUDING COMMENTS ) inherits ( tbl_name );

备注：创建子表时这里没有继承 index，是因为先创建索引再导数据速度会比较慢；

将数据分发到子表

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
 
insert into tbl_name_0 select * from only tbl_name where mod(id,32) =0;  
insert into tbl_name_1 select * from only tbl_name where mod(id,32) =1;  
insert into tbl_name_2 select * from only tbl_name where mod(id,32) =2;  
insert into tbl_name_3 select * from only tbl_name where mod(id,32) =3;  
insert into tbl_name_4 select * from only tbl_name where mod(id,32) =4;  
insert into tbl_name_5 select * from only tbl_name where mod(id,32) =5;  
insert into tbl_name_6 select * from only tbl_name where mod(id,32) =6;  
insert into tbl_name_7 select * from only tbl_name where mod(id,32) =7;  
insert into tbl_name_8 select * from only tbl_name where mod(id,32) =8;  
insert into tbl_name_9 select * from only tbl_name where mod(id,32) =9;  
insert into tbl_name_10 select * from only tbl_name where mod(id,32) =10;  
insert into tbl_name_11 select * from only tbl_name where mod(id,32) =11;  
insert into tbl_name_12 select * from only tbl_name where mod(id,32) =12;  
insert into tbl_name_13 select * from only tbl_name where mod(id,32) =13;  
insert into tbl_name_14 select * from only tbl_name where mod(id,32) =14;  
insert into tbl_name_15 select * from only tbl_name where mod(id,32) =15;  
insert into tbl_name_16 select * from only tbl_name where mod(id,32) =16;  
insert into tbl_name_17 select * from only tbl_name where mod(id,32) =17;  
insert into tbl_name_18 select * from only tbl_name where mod(id,32) =18;  
insert into tbl_name_19 select * from only tbl_name where mod(id,32) =19;  
insert into tbl_name_20 select * from only tbl_name where mod(id,32) =20;  
insert into tbl_name_21 select * from only tbl_name where mod(id,32) =21;  
insert into tbl_name_22 select * from only tbl_name where mod(id,32) =22;  
insert into tbl_name_23 select * from only tbl_name where mod(id,32) =23;  
insert into tbl_name_24 select * from only tbl_name where mod(id,32) =24;  
insert into tbl_name_25 select * from only tbl_name where mod(id,32) =25;  
insert into tbl_name_26 select * from only tbl_name where mod(id,32) =26;  
insert into tbl_name_27 select * from only tbl_name where mod(id,32) =27;  
insert into tbl_name_28 select * from only tbl_name where mod(id,32) =28;  
insert into tbl_name_29 select * from only tbl_name where mod(id,32) =29;  
insert into tbl_name_30 select * from only tbl_name where mod(id,32) =30;  
insert into tbl_name_31 select * from only tbl_name where mod(id,32) =31;

创建子表约束

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
 
alter table tbl_name_0 add CONSTRAINT con_tbl_name_0 check ( mod(id,32) =0);  
alter table tbl_name_1 add CONSTRAINT con_tbl_name_1 check ( mod(id,32) =1);  
alter table tbl_name_2 add CONSTRAINT con_tbl_name_2 check ( mod(id,32) =2);  
alter table tbl_name_3 add CONSTRAINT con_tbl_name_3 check ( mod(id,32) =3);  
alter table tbl_name_4 add CONSTRAINT con_tbl_name_4 check ( mod(id,32) =4);  
alter table tbl_name_5 add CONSTRAINT con_tbl_name_5 check ( mod(id,32) =5);  
alter table tbl_name_6 add CONSTRAINT con_tbl_name_6 check ( mod(id,32) =6);  
alter table tbl_name_7 add CONSTRAINT con_tbl_name_7 check ( mod(id,32) =7);  
alter table tbl_name_8 add CONSTRAINT con_tbl_name_8 check ( mod(id,32) =8);  
alter table tbl_name_9 add CONSTRAINT con_tbl_name_9 check ( mod(id,32) =9);  
alter table tbl_name_10 add CONSTRAINT con_tbl_name_10 check ( mod(id,32) =10);  
alter table tbl_name_11 add CONSTRAINT con_tbl_name_11 check ( mod(id,32) =11);  
alter table tbl_name_12 add CONSTRAINT con_tbl_name_12 check ( mod(id,32) =12);  
alter table tbl_name_13 add CONSTRAINT con_tbl_name_13 check ( mod(id,32) =13);  
alter table tbl_name_14 add CONSTRAINT con_tbl_name_14 check ( mod(id,32) =14);  
alter table tbl_name_15 add CONSTRAINT con_tbl_name_15 check ( mod(id,32) =15);
alter table tbl_name_16 add CONSTRAINT con_tbl_name_16 check ( mod(id,32) =16);  
alter table tbl_name_17 add CONSTRAINT con_tbl_name_17 check ( mod(id,32) =17);  
alter table tbl_name_18 add CONSTRAINT con_tbl_name_18 check ( mod(id,32) =18);  
alter table tbl_name_19 add CONSTRAINT con_tbl_name_19 check ( mod(id,32) =19);  
alter table tbl_name_20 add CONSTRAINT con_tbl_name_20 check ( mod(id,32) =20);  
alter table tbl_name_21 add CONSTRAINT con_tbl_name_21 check ( mod(id,32) =21);  
alter table tbl_name_22 add CONSTRAINT con_tbl_name_22 check ( mod(id,32) =22);  
alter table tbl_name_23 add CONSTRAINT con_tbl_name_23 check ( mod(id,32) =23);  
alter table tbl_name_24 add CONSTRAINT con_tbl_name_24 check ( mod(id,32) =24);  
alter table tbl_name_25 add CONSTRAINT con_tbl_name_25 check ( mod(id,32) =25);  
alter table tbl_name_26 add CONSTRAINT con_tbl_name_26 check ( mod(id,32) =26);  
alter table tbl_name_27 add CONSTRAINT con_tbl_name_27 check ( mod(id,32) =27);  
alter table tbl_name_28 add CONSTRAINT con_tbl_name_28 check ( mod(id,32) =28);  
alter table tbl_name_29 add CONSTRAINT con_tbl_name_29 check ( mod(id,32) =29);  
alter table tbl_name_30 add CONSTRAINT con_tbl_name_30 check ( mod(id,32) =30);  
alter table tbl_name_31 add CONSTRAINT con_tbl_name_31 check ( mod(id,32) =31);

创建索引

增加主键，如下

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
 
ALTER TABLE tbl_name_0 ADD CONSTRAINT pk_tbl_name_id_0 PRIMARY KEY (id);  
ALTER TABLE tbl_name_1 ADD CONSTRAINT pk_tbl_name_id_1 PRIMARY KEY (id);  
ALTER TABLE tbl_name_2 ADD CONSTRAINT pk_tbl_name_id_2 PRIMARY KEY (id);  
ALTER TABLE tbl_name_3 ADD CONSTRAINT pk_tbl_name_id_3 PRIMARY KEY (id);  
ALTER TABLE tbl_name_4 ADD CONSTRAINT pk_tbl_name_id_4 PRIMARY KEY (id);  
ALTER TABLE tbl_name_5 ADD CONSTRAINT pk_tbl_name_id_5 PRIMARY KEY (id);  
ALTER TABLE tbl_name_6 ADD CONSTRAINT pk_tbl_name_id_6 PRIMARY KEY (id);  
ALTER TABLE tbl_name_7 ADD CONSTRAINT pk_tbl_name_id_7 PRIMARY KEY (id);  
ALTER TABLE tbl_name_8 ADD CONSTRAINT pk_tbl_name_id_8 PRIMARY KEY (id);  
ALTER TABLE tbl_name_9 ADD CONSTRAINT pk_tbl_name_id_9 PRIMARY KEY (id);  
ALTER TABLE tbl_name_10 ADD CONSTRAINT pk_tbl_name_id_10 PRIMARY KEY (id);  
ALTER TABLE tbl_name_11 ADD CONSTRAINT pk_tbl_name_id_11 PRIMARY KEY (id);  
ALTER TABLE tbl_name_12 ADD CONSTRAINT pk_tbl_name_id_12 PRIMARY KEY (id);  
ALTER TABLE tbl_name_13 ADD CONSTRAINT pk_tbl_name_id_13 PRIMARY KEY (id);  
ALTER TABLE tbl_name_14 ADD CONSTRAINT pk_tbl_name_id_14 PRIMARY KEY (id);  
ALTER TABLE tbl_name_15 ADD CONSTRAINT pk_tbl_name_id_15 PRIMARY KEY (id);  
ALTER TABLE tbl_name_16 ADD CONSTRAINT pk_tbl_name_id_16 PRIMARY KEY (id);  
ALTER TABLE tbl_name_17 ADD CONSTRAINT pk_tbl_name_id_17 PRIMARY KEY (id);  
ALTER TABLE tbl_name_18 ADD CONSTRAINT pk_tbl_name_id_18 PRIMARY KEY (id);  
ALTER TABLE tbl_name_19 ADD CONSTRAINT pk_tbl_name_id_19 PRIMARY KEY (id);  
ALTER TABLE tbl_name_20 ADD CONSTRAINT pk_tbl_name_id_20 PRIMARY KEY (id);  
ALTER TABLE tbl_name_21 ADD CONSTRAINT pk_tbl_name_id_21 PRIMARY KEY (id);  
ALTER TABLE tbl_name_22 ADD CONSTRAINT pk_tbl_name_id_22 PRIMARY KEY (id);  
ALTER TABLE tbl_name_23 ADD CONSTRAINT pk_tbl_name_id_23 PRIMARY KEY (id);  
ALTER TABLE tbl_name_24 ADD CONSTRAINT pk_tbl_name_id_24 PRIMARY KEY (id);  
ALTER TABLE tbl_name_25 ADD CONSTRAINT pk_tbl_name_id_25 PRIMARY KEY (id);  
ALTER TABLE tbl_name_26 ADD CONSTRAINT pk_tbl_name_id_26 PRIMARY KEY (id);  
ALTER TABLE tbl_name_27 ADD CONSTRAINT pk_tbl_name_id_27 PRIMARY KEY (id);  
ALTER TABLE tbl_name_28 ADD CONSTRAINT pk_tbl_name_id_28 PRIMARY KEY (id);  
ALTER TABLE tbl_name_29 ADD CONSTRAINT pk_tbl_name_id_29 PRIMARY KEY (id);  
ALTER TABLE tbl_name_30 ADD CONSTRAINT pk_tbl_name_id_30 PRIMARY KEY (id);  
ALTER TABLE tbl_name_31 ADD CONSTRAINT pk_tbl_name_id_31 PRIMARY KEY (id);

创建 unique 索引

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
 
CREATE UNIQUE INDEX idx_tbl_name_name_0 ON tbl_name_0 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_1 ON tbl_name_1 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_2 ON tbl_name_2 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_3 ON tbl_name_3 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_4 ON tbl_name_4 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_5 ON tbl_name_5 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_6 ON tbl_name_6 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_7 ON tbl_name_7 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_8 ON tbl_name_8 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_9 ON tbl_name_9 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_10 ON tbl_name_10 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_11 ON tbl_name_11 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_12 ON tbl_name_12 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_13 ON tbl_name_13 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_14 ON tbl_name_14 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_15 ON tbl_name_15 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_16 ON tbl_name_16 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_17 ON tbl_name_17 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_18 ON tbl_name_18 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_19 ON tbl_name_19 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_20 ON tbl_name_20 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_21 ON tbl_name_21 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_22 ON tbl_name_22 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_23 ON tbl_name_23 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_24 ON tbl_name_24 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_25 ON tbl_name_25 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_26 ON tbl_name_26 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_27 ON tbl_name_27 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_28 ON tbl_name_28 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_29 ON tbl_name_29 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_30 ON tbl_name_30 USING btree (name);  
CREATE UNIQUE INDEX idx_tbl_name_name_31 ON tbl_name_31 USING btree (name);

清空父表数据

1
 
truncate table only tbl_name;

执行计划

查看参数 constraint_exclusion

1
2
3
4
5
 
francs=> show constraint_exclusion;  
constraint_exclusion  
----------------------  
partition  
(1 row)

显示查询父表的PLAN

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
francs=> explain select id,name,remark from tbl_name where id =32 ;  
 QUERY PLAN  
-----------------------------------------------------------------------------------------------------------  
Result (cost=0.00..73.11 rows=33 width=16)  
 -> Append (cost=0.00..73.11 rows=33 width=16)  
 -> Seq Scan on tbl_name (cost=0.00..0.00 rows=1 width=16)  
 Filter: (id = 32)  
 -> Index Scan using pk_tbl_name_id_0 on tbl_name_0 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_1 on tbl_name_1 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_2 on tbl_name_2 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_3 on tbl_name_3 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_4 on tbl_name_4 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_5 on tbl_name_5 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_6 on tbl_name_6 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_7 on tbl_name_7 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_8 on tbl_name_8 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_9 on tbl_name_9 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_10 on tbl_name_10 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_11 on tbl_name_11 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_12 on tbl_name_12 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_13 on tbl_name_13 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_14 on tbl_name_14 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_15 on tbl_name_15 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_16 on tbl_name_16 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_17 on tbl_name_17 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_18 on tbl_name_18 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_19 on tbl_name_19 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_20 on tbl_name_20 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_21 on tbl_name_21 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_22 on tbl_name_22 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_23 on tbl_name_23 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_24 on tbl_name_24 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_25 on tbl_name_25 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_26 on tbl_name_26 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_27 on tbl_name_27 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_28 on tbl_name_28 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_29 on tbl_name_29 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_30 on tbl_name_30 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
 -> Index Scan using pk_tbl_name_id_31 on tbl_name_31 tbl_name (cost=0.00..2.28 rows=1 width=16)  
 Index Cond: (id = 32)  
(68 rows)

备注：查询父表时，扫描了所有分区。

修改SQL，再次显示PLAN

1
2
3
4
5
6
7
8
9
10
11
 
francs=> explain select id,name,remark from tbl_name where mod(id,32)=0 and id =32 ;  
 QUERY PLAN  
---------------------------------------------------------------------------------------------------------  
Result (cost=0.00..2.29 rows=2 width=16)  
 -> Append (cost=0.00..2.29 rows=2 width=16)  
 -> Seq Scan on tbl_name (cost=0.00..0.00 rows=1 width=16)  
 Filter: ((id = 32) AND (mod(id, 32) = 0))  
 -> Index Scan using pk_tbl_name_id_0 on tbl_name_0 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
(7 rows)

备注：查询父表时，如果加上 “where mod(id,32)= ? “时，不再扫描所有分区，而只扫描一个分区。

关闭 constraint_exclusion 参数，再次查看 PALN

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
francs=> set constraint_exclusion=off;  
SET
francs=> explain select id,name,remark from tbl_name where mod(id,32)=0 and id =32 ;  
 QUERY PLAN  
-----------------------------------------------------------------------------------------------------------  
Result (cost=0.00..73.27 rows=33 width=16)  
 -> Append (cost=0.00..73.27 rows=33 width=16)  
 -> Seq Scan on tbl_name (cost=0.00..0.00 rows=1 width=16)  
 Filter: ((id = 32) AND (mod(id, 32) = 0))  
 -> Index Scan using pk_tbl_name_id_0 on tbl_name_0 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_1 on tbl_name_1 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_2 on tbl_name_2 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_3 on tbl_name_3 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_4 on tbl_name_4 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_5 on tbl_name_5 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_6 on tbl_name_6 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_7 on tbl_name_7 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_8 on tbl_name_8 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_9 on tbl_name_9 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_10 on tbl_name_10 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_11 on tbl_name_11 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_12 on tbl_name_12 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_13 on tbl_name_13 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_14 on tbl_name_14 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_15 on tbl_name_15 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_16 on tbl_name_16 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_17 on tbl_name_17 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_18 on tbl_name_18 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_19 on tbl_name_19 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_20 on tbl_name_20 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_21 on tbl_name_21 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_22 on tbl_name_22 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_23 on tbl_name_23 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_24 on tbl_name_24 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_25 on tbl_name_25 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_26 on tbl_name_26 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_27 on tbl_name_27 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_28 on tbl_name_28 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_29 on tbl_name_29 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_30 on tbl_name_30 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
 -> Index Scan using pk_tbl_name_id_31 on tbl_name_31 tbl_name (cost=0.00..2.29 rows=1 width=16)  
 Index Cond: (id = 32)  
 Filter: (mod(id, 32) = 0)  
(100 rows)

备注： 关闭 constraint_exclusion 参数后，即扫描所有分区，关于这个参数的作用可以参考本文末尾的附录。

性能测试

下面测试两种场景：

  • 场景一: 根据分区字段 id 查询测试
  • 场景二: 根据非分区字段 name 查询测试

根据分区字段 id 查询测试

分表前查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
francs=> select id,name,remark from tbl_name_old where id =128 ;  
id | name | remark  
-----+-------+--------  
128 | 128_a | 0  
(1 row)
Time: 0.305 ms  

francs=> select id,name,remark from tbl_name_old where id =128 ;  
id | name | remark  
-----+-------+--------  
128 | 128_a | 0  
(1 row)
Time: 0.277 ms  

francs=> select id,name,remark from tbl_name_old where id =128 ;  
id | name | remark  
-----+-------+--------  
128 | 128_a | 0  
(1 row)
Time: 0.351 ms

分表后查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
francs=> select id,name,remark from tbl_name_0 where id =64 ;  
id | name | remark  
----+------+--------  
64 | 64_a | 0  
(1 row)
Time: 0.331 ms  

francs=> select id,name,remark from tbl_name_0 where id =64 ;  
id | name | remark  
----+------+--------  
64 | 64_a | 0  
(1 row)
Time: 0.322 ms  

francs=> select id,name,remark from tbl_name_0 where id =64 ;  
id | name | remark  
----+------+--------  
64 | 64_a | 0  
(1 row)
Time: 0.301 ms
skyid 测试 测试一 测试二 测试三
分表前 0.305 ms 0.277 ms 0.351 ms
分表后 0.331 ms 0.322 ms 0.301 ms

备注：分表后，这里建议应用程序根据 id 取模，然后直接定位子表查询，这里数据来看，性能无明显变化，数据量大的时候分表后的这种场景性能有小辐上升。

根据非分区字段 name 查询测试

分表前

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
francs=> select id,name,remark from tbl_name_old where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 0.466 ms  
francs=> select id,name,remark from tbl_name_old where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 0.348 ms  
francs=> select id,name,remark from tbl_name_old where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 0.326 ms

分表后

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
francs=> select id,name,remark from tbl_name where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 1.587 ms  

francs=> select id,name,remark from tbl_name where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 1.478 ms  

francs=> select id,name,remark from tbl_name where name='32_a' ;  
id | name | remark  
----+------+--------  
32 | 32_a | 0  
(1 row)
Time: 2.227 ms
name 测试 测试一 测试二 测试三
分表前 0.466 ms 0.348 ms 0.326 ms
分表后 1.587 ms 1.478 ms 2.227 ms

备注：根据非分区字段查询，分表后由于需要扫描所有分区，性能有下降辐度较大。

总结

  1. pg 中的表分区后，如果根据 “ where 分区键= ？” 查询，如果扫描父表，性能降低!
  2. pg 中的表分区后，如果根据 “ where 分区键= ？” 查询，如果程序定位到子表，性能小辐度上升!
  3. pg 中的表分区后，如果根据 “ where 非分区键= ？” 查询，且非分区键有索引，性能大辐下降。
  4. pg 中的表分区后，非分区字段的唯一性无法保证。
  5. PG 中的表分区后，为后期的分库奠定了基础。

备注：签于以上特点，故业务表分区需要权衡各方面的利弊，例如如果应用 90% 以上都是根据分区键查询，则建议分区。

附

constraint_exclusion (enum)
Controls the query planner is use of table constraints to optimize queries. The allowed values of constraint_exclusion are on (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with inheritance and partitioned tables to improve performance.

原创文章，作者：745907710，如若转载，请注明出处：https://blog.ytso.com/237859.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论