創(chuàng)建表分區(qū)步驟如下： 
1. 創(chuàng)建主表 
CREATE TABLE users ( uid int not null primary key, name varchar(20)); 
2. 創(chuàng)建分區(qū)表(必須繼承上面的主表) 
CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users); 
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users); 
3. 在分區(qū)表上建立索引，其實這步可以省略的哦 
CREATE INDEX users_0_uidindex on users_0(uid); 
CREATE INDEX users_1_uidindex on users_1(uid); 
4. 創(chuàng)建規(guī)則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); 
下面就可以測試寫入數(shù)據(jù)啦： 
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 筆資料列) 
到這里表分區(qū)已經(jīng)可以算完了，不過還有個地方需要修改下，先看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可以看到其他他掃描了所有分區(qū)的表。 
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啦！