PostgreSQL 创建表分区

编辑：rootadmin

创建表分区步骤如下： 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar()); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< ) ) INHERITS (users); CREATE TABLE users_1 ( check (uid >= )) 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 < ) DO INSTEAD INSERT INTO users_0 VALUES (NEW.uid,NEW.name); CREATE RULE users_insert_1 AS ON INSERT TO users WHERE (uid >= ) DO INSTEAD INSERT INTO users_1 VALUES (NEW.uid,NEW.name); 下面就可以测试写入数据啦： postgres=# INSERT INTO users VALUES (,'smallfish'); INSERT 0 0 postgres=# INSERT INTO users VALUES (,'aaaaa'); INSERT 0 0 postgres=# select * from users; uid | name -----+----------- | aaaaa | smallfish (2 笔资料列) postgres=# select * from users_0; uid | name -----+------- | aaaaa (1 笔资料列) postgres=# select * from users_1; uid | name -----+----------- | smallfish (1 笔资料列) 到这里表分区已经可以算完了，不过还有个地方需要修改下，先看count查询把。 postgres=# EXPLAIN SELECT count(*) FROM users where uid<; QUERY PLAN --------------------------------------------------------------------------------------------- Aggregate (cost=.... rows=1 width=0) -> Append (cost=6.... rows= width=0) -> Bitmap Heap Scan on users (cost=6.... rows= width=0) Recheck Cond: (uid < ) -> Bitmap Index Scan on users_pkey (cost=0...6. rows= width=0) Index Cond: (uid < ) -> Bitmap Heap Scan on users_0 users (cost=6.... rows= width=0) Recheck Cond: (uid < ) -> Bitmap Index Scan on users_0_uidindex (cost=0...6. rows= width=0) Index Cond: (uid < ) -> Bitmap Heap Scan on users_1 users (cost=6.... rows= width=0) Recheck Cond: (uid < ) -> Bitmap Index Scan on users_1_uidindex (cost=0...6. rows= width=0) Index Cond: (uid < ) ( 笔资料列) 按照本来想法，uid小于，理论上应该只是查询users_0表，通过EXPLAIN可以看到其他他扫描了所有分区的表。 postgres=# SET constraint_exclusion = on; SET postgres=# EXPLAIN SELECT count(*) FROM users where uid<; QUERY PLAN --------------------------------------------------------------------------------------------- Aggregate (cost=.... rows=1 width=0) -> Append (cost=6.... rows= width=0) -> Bitmap Heap Scan on users (cost=6.... rows= width=0) Recheck Cond: (uid < ) -> Bitmap Index Scan on users_pkey (cost=0...6. rows= width=0) Index Cond: (uid < ) -> Bitmap Heap Scan on users_0 users (cost=6.... rows= width=0) Recheck Cond: (uid < ) -> Bitmap Index Scan on users_0_uidindex (cost=0...6. rows= width=0) Index Cond: (uid < ) ( 笔资料列) 到这里整个过程都OK啦！

推荐整理分享PostgreSQL 创建表分区，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:,内容如对您有帮助，希望把文章链接给更多的朋友！

PostgreSQL中的OID和XID 说明 oid：行的对象标识符（对象ID）。这个字段只有在创建表的时候使用了WITHOIDS，或者是设置了default_with_oids配置参数时出现。这个字段的类型是oid（和字

PostgreSQL 角色与用户管理介绍一、角色与用户的区别角色就相当于岗位：角色可以是经理，助理。用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。在PostgreSQL里没有区分用户

PostgreSQL 查看数据库，索引，表，表空间大小的示例代码一、简介PostgreSQL提供了多个系统管理函数来查看表，索引，表空间及数据库的大小，下面详细介绍一下。二、数据库对象尺寸函数函数名返回类型描述p