PostgreSQL CREATE TABLE Partitions _postgresql

The steps to create a table partition are as follows:
1. Create the Main table
CREATE TABLE users (UID int not null primary key, name varchar (20));
2. Create the partition table (you must inherit the main table above)
CREATE TABLE users_0 (check (UID >= 0 and uid<)) INHERITS (users);
CREATE TABLE users_1 (check (uid >=)) INHERITS (users);
3. The index on the partition table, in fact, this step can be omitted Oh
CREATE INDEX Users_0_uidindex on Users_0 (UID);
CREATE INDEX Users_1_uidindex on Users_1 (UID);
4. Creating Rule Rules
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);
Here you can test the write data:
postgres=# INSERT into Users VALUES (M, ' smallfish ');
INSERT 0 0
postgres=# INSERT into Users VALUES (' aaaaa ');
INSERT 0 0
postgres=# select * from users;
UID | Name
-----+-----------
20 | Aaaaa
100 | Smallfish
(2 data columns)
postgres=# select * from Users_0;
UID | Name
-----+-------
20 | Aaaaa
(1 data columns)
postgres=# select * from Users_1;
UID | Name
-----+-----------
100 | Smallfish
(1 data columns)
Here the table partition can be finished, but there is a place to change, first look at the count query.
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 data columns)
According to the idea that the UID is less than 100, the theory should simply be to query the Users_0 table, and through explain you can see other tables where he scanned all the partitions.
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 data columns)
The whole process here is OK!