测试版本：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
啦！