Postgre SQL
发布时间:2022-05-20 发布网站:大佬教程 code.js-code.com
大佬教程收集整理的这篇文章主要介绍了postgresql 创建表分区,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
测试版本:pg 8.3 (ubuntu)
在pg里表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。
创建表分区步骤如下:
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啦!
大佬总结
以上是大佬教程为你收集整理的postgresql 创建表分区全部内容,希望文章能够帮你解决postgresql 创建表分区所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。