postgresql分区表1
来源:互联网 发布:小孩多大开始学编程 编辑:程序博客网 时间:2024/05/22 03:11
mkdir -p /data/pg/data/tb01CREATE TABLESPACE tb01 LOCATION '/data/pg/data/tb01';create database "wind"with owner postgresencoding 'UTF8'tablespace tb01connection 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 ason insert to ft where (sdate >= date '2015-01-01' and sdate < date '2015-02-01')do insteadinsert into ft01 values (new.sid, new.sdate, new.saddress);create rule rule_ft02 ason insert to ft where (sdate >= date '2015-02-01' and sdate < date '2015-03-01')do insteadinsert into ft02 values (new.sid, new.sdate, new.saddress);create rule rule_ft03 ason insert to ft where (sdate >= date '2015-03-01' and sdate < date '2015-04-01')do insteadinsert into ft03 values (new.sid, new.sdate, new.saddress);create rule rule_ft04 ason insert to ft where (sdate >= date '2015-04-01' and sdate < date '2015-05-01')do insteadinsert into ft04 values (new.sid, new.sdate, new.saddress);create rule rule_ft05 ason insert to ft where (sdate >= date '2015-05-01' and sdate < date '2015-06-01')do insteadinsert into ft05 values (new.sid, new.sdate, new.saddress);create rule rule_ft06 ason insert to ft where (sdate >= date '2015-06-01' and sdate < date '2015-07-01')do insteadinsert into ft06 values (new.sid, new.sdate, new.saddress);create rule rule_ft07 ason insert to ft where (sdate >= date '2015-07-01' and sdate < date '2015-08-01')do insteadinsert into ft07 values (new.sid, new.sdate, new.saddress);create rule rule_ft08 ason insert to ft where (sdate >= date '2015-08-01' and sdate < date '2015-09-01')do insteadinsert into ft08 values (new.sid, new.sdate, new.saddress);create rule rule_ft09 ason insert to ft where (sdate >= date '2015-09-01' and sdate < date '2015-10-01')do insteadinsert into ft09 values (new.sid, new.sdate, new.saddress);create rule rule_ft10 ason insert to ft where (sdate >= date '2015-10-01' and sdate < date '2015-11-01')do insteadinsert into ft10 values (new.sid, new.sdate, new.saddress);create rule rule_ft11 ason insert to ft where (sdate >= date '2015-11-01' and sdate < date '2015-12-01')do insteadinsert into ft11 values (new.sid, new.sdate, new.saddress);create rule rule_ft12 ason insert to ft where (sdate >= date '2015-12-01' and sdate < date '2016-01-01')do insteadinsert into ft12 values (new.sid, new.sdate, new.saddress);----插入测试数据insert into ft(sid,sdate,saddress) select n,'2015-01-03',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-02-04',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-03-05',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-04-04',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-05-05',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-06-06',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-07-07',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-08-08',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-09-09',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-10-10',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-11-11',n||':杨楂文'from generate_series(1,100) n;insert into ft(sid,sdate,saddress) select n,'2015-12-12',n||':杨楂文'from generate_series(1,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)
0 0
- postgresql分区表1
- postgresql分区表
- postgresql分区表笔记
- postgresql 分区表 创建函数
- POSTGRESQL 分区表初次体验
- 转载:PostgreSQL的分区表
- postgresql分区表-触发器
- postgresql 建立删除分区表
- Postgresql 实现分区表
- postgresql分区表创建
- postgresql分区表2
- postgresql分区表的使用
- Postgresql 分区表测试
- 【PostgreSQL-9.6.3】分区表
- PostgreSQL分区表相关
- PostgreSQL之分区表(partitioning)
- PostgreSQL分区表(Table Partitioning)
- 关于Postgresql 分区表的那些事
- postgres不错的命令
- Android入门心得(一)
- UIAlertViewController详解
- [数据存储之一]NSCoding协议练习
- 读书学习之道
- postgresql分区表1
- 黑马程序员--Java编程之集合阶段总结
- 第13周项目2 算法验证—Dijkstra算法
- PHP对数组的常用处理
- HDU 5273 Dylans loves sequence
- 直接双击启动tomcat中的startup.bat闪退原因及解决方法
- quick 3.3 中绑定WebView
- 开荒第一篇!SHU1981 Handsome yaoge(用余数考虑倍数问题)
- bat 运行java程序