postgresql分区表2
发布时间:2020-12-13 17:11:18 所属栏目:百科 来源:网络整理
导读:1). 创建"主表",所有分区都从它继承。create table ft( sid int not null,sdate date not null,saddress varchar(100))tablespace tb01; 2). 创建几个"子"表,每个都从主表上继承create table ft01 (check ( sdate = date '2015-01-01' and sdate date '201
1). 创建"主表",所有分区都从它继承。 create table ft ( sid int not null,sdate date not null,saddress varchar(100) )tablespace tb01; 2). 创建几个"子"表,每个都从主表上继承 create table ft01 (check ( sdate >= date '2015-01-01' and sdate < date '2015-02-01') ) inherits (ft) tablespace tb01; create table ft02 ( check ( sdate >= date '2015-02-01' and sdate < date '2015-03-01')) inherits (ft) tablespace tb01; create table ft03 ( check ( sdate >= date '2015-03-01' and sdate < date '2015-04-01')) inherits (ft) tablespace tb01; create table ft04 ( check ( sdate >= date '2015-04-01' and sdate < date '2015-05-01')) inherits (ft) tablespace tb01; create table ft05 ( check ( sdate >= date '2015-05-01' and sdate < date '2015-06-01')) inherits (ft) tablespace tb01; create table ft06 ( check ( sdate >= date '2015-06-01' and sdate < date '2015-07-01')) inherits (ft) tablespace tb01; create table ft07 ( check ( sdate >= date '2015-07-01' and sdate < date '2015-08-01')) inherits (ft) tablespace tb01; create table ft08 ( check ( sdate >= date '2015-08-01' and sdate < date '2015-09-01')) inherits (ft) tablespace tb01; create table ft09 ( check ( sdate >= date '2015-09-01' and sdate < date '2015-10-01')) inherits (ft) tablespace tb01; create table ft10 ( check ( sdate >= date '2015-10-01' and sdate < date '2015-11-01')) inherits (ft) tablespace tb01; create table ft11 ( check ( sdate >= date '2015-11-01' and sdate < date '2015-12-01')) inherits (ft) tablespace tb01; create table ft12 ( check ( sdate >= date '2015-12-01' and sdate < date '2016-1-01')) inherits (ft) tablespace tb01; create index index_ft01_sdate on ft01 (sdate) tablespace tb01_index; create index index_ft02_sdate on ft02 (sdate) tablespace tb01_index; create index index_ft03_sdate on ft03 (sdate) tablespace tb01_index ; create index index_ft04_sdate on ft04 (sdate) tablespace tb01_index; create index index_ft05_sdate on ft05 (sdate) tablespace tb01_index; create index index_ft06_sdate on ft06 (sdate) tablespace tb01_index; create index index_ft07_sdate on ft07 (sdate) tablespace tb01_index; create index index_ft08_sdate on ft08 (sdate) tablespace tb01_index; create index index_ft09_sdate on ft09 (sdate) tablespace tb01_index; create index index_ft10_sdate on ft10 (sdate) tablespace tb01_index; create index index_ft11_sdate on ft11 (sdate) tablespace tb01_index; create index index_ft12_sdate on ft12 (sdate) tablespace tb01_index; create or replace function ft_insert_trigger() returns trigger as $$ begin if( new.sdate between '2015-1-01' and '2015-02-01' ) then insert into ft01 values (new.*); elsif( new.sdate between '2015-2-01' and '2015-03-01' ) then insert into ft02 values (new.*); elsif( new.sdate between '2015-3-01' and '2015-04-01' ) then insert into ft03 values (new.*); elsif( new.sdate between '2015-4-01' and '2015-05-01' ) then insert into ft04 values (new.*); elsif( new.sdate between '2015-05-01' and '2015-06-01' ) then insert into ft05 values (new.*); elsif( new.sdate between '2015-06-01' and '2015-07-01' ) then insert into ft06 values (new.*); elsif( new.sdate between '2015-07-01' and '2015-08-01' ) then insert into ft07 values (new.*); elsif( new.sdate between '2015-08-01' and '2015-09-01' ) then insert into ft08 values (new.*); elsif( new.sdate between '2015-09-01' and '2015-10-01' ) then insert into ft09 values (new.*); elsif( new.sdate between '2015-10-01' and '2015-11-01' ) then insert into ft10 values (new.*); elsif( new.sdate between '2015-11-01' and '2015-12-01' ) then insert into ft11 values (new.*); elsif( new.sdate between '2015-12-01' and '2016-01-01' ) then insert into ft12 values (new.*); else raise exception '!out of range!'; end if; return null; end; $$ language plpgsql; create trigger insert_ft_trigger before insert on ft for each row execute procedure ft_insert_trigger() ; ----插入测试数据 insert into ft(sid,sdate,saddress) select n,'2015-01-03',n||':杨楂文' from generate_series(1,100) n; insert into ft(sid,'2015-02-04','2015-03-05','2015-04-04','2015-05-05','2015-06-06','2015-07-07','2015-08-08','2015-09-09','2015-10-10','2015-11-11','2015-12-12',100) n; ----查询 SET constraint_exclusion = off; #EXPLAIN SELECT count(*) FROM ft WHERE sdate >= DATE '2015-01-01'; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=30.02..30.03 rows=1 width=0) -> Append (cost=0.00..27.01 rows=1202 width=0) -> Seq Scan on ft (cost=0.00..0.00 rows=1 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft01 (cost=0.00..2.26 rows=101 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft02 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft03 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft04 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft05 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft06 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft07 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft08 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft09 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft10 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft11 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft12 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) (28 rows) SET constraint_exclusion = off; #EXPLAIN SELECT count(*) FROM ft WHERE sdate >= DATE '2015-01-01'; ------------------------------------------------------------------ Aggregate (cost=30.02..30.03 rows=1 width=0) -> Append (cost=0.00..27.01 rows=1202 width=0) -> Seq Scan on ft (cost=0.00..0.00 rows=1 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft01 (cost=0.00..2.26 rows=101 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft02 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft03 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft04 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft05 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft06 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft07 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft08 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft09 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft10 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft11 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) -> Seq Scan on ft12 (cost=0.00..2.25 rows=100 width=0) Filter: (sdate >= '2015-01-01'::date) (28 rows) # EXPLAIN SELECT count(*) FROM ft WHERE sdate between '2015-03-01' and '2015-04-01'; QUERY PLAN ----------------------------------------------------------------------------------------- Aggregate (cost=5.25..5.26 rows=1 width=0) -> Append (cost=0.00..5.00 rows=102 width=0) -> Seq Scan on ft (cost=0.00..0.00 rows=1 width=0) Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date)) -> Seq Scan on ft03 (cost=0.00..2.50 rows=100 width=0) Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date)) -> Seq Scan on ft04 (cost=0.00..2.50 rows=1 width=0) Filter: ((sdate >= '2015-03-01'::date) AND (sdate <= '2015-04-01'::date)) (8 rows) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |