广告合作QQ：76874919

postgresql分区表1

Postgre SQL 2020-06-24 
mkdir -p /data/pg/data/tb01


CREATE TABLESPACE tb01 LOCATION '/data/pg/data/tb01';


create database "wind"
with owner postgres
encoding 'UTF8'
tablespace tb01
connection limit -1;


实现分区：
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 rule rule_ft01 as
on insert to ft where (sdate >= date '2015-01-01' and sdate < date '2015-02-01')
do instead
insert into ft01 values (new.sid,new.sdate,new.saddress);



create rule rule_ft02 as
on insert to ft where (sdate >= date '2015-02-01' and sdate < date '2015-03-01')
do instead
insert into ft02 values (new.sid,new.saddress);


create rule rule_ft03 as
on insert to ft where (sdate >= date '2015-03-01' and sdate < date '2015-04-01')
do instead
insert into ft03 values (new.sid,new.saddress);


create rule rule_ft04 as
on insert to ft where (sdate >= date '2015-04-01' and sdate < date '2015-05-01')
do instead
insert into ft04 values (new.sid,new.saddress);


create rule rule_ft05 as
on insert to ft where (sdate >= date '2015-05-01' and sdate < date '2015-06-01')
do instead
insert into ft05 values (new.sid,new.saddress);


create rule rule_ft06 as
on insert to ft where (sdate >= date '2015-06-01' and sdate < date '2015-07-01')
do instead
insert into ft06 values (new.sid,new.saddress);



create rule rule_ft07 as
on insert to ft where (sdate >= date '2015-07-01' and sdate < date '2015-08-01')
do instead
insert into ft07 values (new.sid,new.saddress);

create rule rule_ft08 as
on insert to ft where (sdate >= date '2015-08-01' and sdate < date '2015-09-01')
do instead
insert into ft08 values (new.sid,new.saddress);

create rule rule_ft09 as
on insert to ft where (sdate >= date '2015-09-01' and sdate < date '2015-10-01')
do instead
insert into ft09 values (new.sid,new.saddress);

create rule rule_ft10 as
on insert to ft where (sdate >= date '2015-10-01' and sdate < date '2015-11-01')
do instead
insert into ft10 values (new.sid,new.saddress);

create rule rule_ft11 as
on insert to ft where (sdate >= date '2015-11-01' and sdate < date '2015-12-01')
do instead
insert into ft11 values (new.sid,new.saddress);

create rule rule_ft12 as
on insert to ft where (sdate >= date '2015-12-01' and sdate < date '2016-01-01')
do instead
insert into ft12 values (new.sid,new.saddress);


----插入测试数据

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)

相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键词必须以一个...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的整数以及4或8...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。当一个表被读...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行的操作过程中...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是PostgreSQL中的...
PosegreSQL基础回顾(第 9 章 函数和操作符)
来源：http://www.postgres.cn/docs/11/ 9.7.&#160;模式匹配 PostgreSQL提供了三种独立的实现模式匹...