时间:2021-05-24
创建表分区步骤如下:
1. 创建主表
CREATE TABLE users ( uid int not null primary key, name varchar(20));
2. 创建分区表(必须继承上面的主表)
CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users);
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);
3. 在分区表上建立索引,其实这步可以省略的哦
CREATE INDEX users_0_uidindex on users_0(uid);
CREATE INDEX users_1_uidindex on users_1(uid);
4. 创建规则RULE
CREATE RULE users_insert_0 AS
ON INSERT TO users WHERE
(uid >= 0 and uid < 100)
DO INSTEAD
INSERT INTO users_0 VALUES (NEW.uid,NEW.name);
CREATE RULE users_insert_1 AS
ON INSERT TO users WHERE
(uid >= 100)
DO INSTEAD
INSERT INTO users_1 VALUES (NEW.uid,NEW.name);
下面就可以测试写入数据啦:
postgres=# INSERT INTO users VALUES (100,'smallfish');
INSERT 0 0
postgres=# INSERT INTO users VALUES (20,'aaaaa');
INSERT 0 0
postgres=# select * from users;
uid | name
-----+-----------
20 | aaaaa
100 | smallfish
(2 笔资料列)
postgres=# select * from users_0;
uid | name
-----+-------
20 | aaaaa
(1 笔资料列)
postgres=# select * from users_1;
uid | name
-----+-----------
100 | smallfish
(1 笔资料列)
到这里表分区已经可以算完了,不过还有个地方需要修改下,先看count查询把。
postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=62.75..62.76 rows=1 width=0)
-> Append (cost=6.52..60.55 rows=879 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_1 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_1_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(14 笔资料列)
按照本来想法,uid小于100,理论上应该只是查询users_0表,通过EXPLAIN可以看到其他他扫描了所有分区的表。
postgres=# SET constraint_exclusion = on;
SET
postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=41.83..41.84 rows=1 width=0)
-> Append (cost=6.52..40.37 rows=586 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(10 笔资料列)
到这里整个过程都OK啦!
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
PostgreSQL引进“分区”表特性,解放了之前采用“表继承”+“触发器”来实现分区表的繁琐、低效。而添加分区,都是手动执行SQL。演示目的:利用python
PostgreSQL引进“分区”表特性,解放了之前采用“表继承”+“触发器”来实现分区表的繁琐、低效。而添加分区,都是手动执行SQL。演示目的:利用python
前言项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提
文章目录PosgtreSQL11支持为分区表创建一个默认(DEFAULT)的分区,用于存储无法匹配其他任何分区的数据。显然,只有RANGE分区表和LIST分区表
创建一个和目标表一样定义的表;在表上加上constraint以确保表的数据在目标分区里;Altertable源表switchto目标表partition分区号其