注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

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

2012-05-29 16:37:22|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


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


--1 创建父表并插入测试数据

 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)

   
 
   
--2 父表备份

 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 数据要清空。

   

--3 创建子表 ( 分区字段 id ) 

 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,是因为先创建索引再导数据速度会比较慢;


--4 将数据分发到子表 ( 1 hour )

 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;

   


--5 创建子表约束

 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);

   


--6 创建索引
--6.1 增加 pk  
                       

 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); 
   


--6.2 创建 unique 索引

 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);
   


--7 清空父表数据
truncate table only tbl_name;    

   
                                                                                             
--8 执行计划
--8.1 查看参数 constraint_exclusion
francs=> show constraint_exclusion;
 constraint_exclusion
----------------------
 partition
(1 row)

 

--8.2 显示查询父表的PLAN

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)
   

   备注:查询父表时,扫描了所有分区。


--8.3 修改SQL,再次显示PLAN。                                                   ^

 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)= ? "时,不再扫描所有分区,而只扫描一个分区。


--8.4 关闭 constraint_exclusion 参数,再次查看 PALN

 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 参数后,即扫描所有分区,关于这个参数的作用可以参考本文末尾的附录。

--9 性能测试
--9.1 根据分区字段 id 查询单条记录 测试
--分表前查询

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

 

--分表后查询

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 取模,然后直接定位子表查询,这里数据来看,性能无明显变化,

               数据量大的时候分表后的这种场景性能有小辐上升。

 

--9.2 根据非分区字段 name 查询单条记录测试

--分表前

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

 

--分表后

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

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


--10 总结


     1  pg 中的表分区后,如果根据 " where 分区键= ?" 查询,如果扫描父表,性能降低!
     2  pg 中的表分区后,如果根据 " where 分区键= ?" 查询,如果程序定位到子表,性能小辐度上升!
     3  pg 中的表分区后,如果根据 " where 非分区键= ?" 查询,且非分区键有索引,性能大辐下降。
     4  pg 中的表分区后,非分区字段的唯一性无法保证。

      5 PG 中的表分区后,为后期的分库奠定了基础。

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

                 则建议分区。


--11 附
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.


  评论这张
 
阅读(32598)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016