PostgreSQL Create table partitions

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 a partition table (must inherit the above main table)
CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users);
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);
3. Create an index on the partition table. In fact, this step can be omitted.
CREATE INDEX users_0_uidindex on users_0(uid);
CREATE INDEX users_1_uidindex on users_1(uid);
4. Create rule RULE
CREATE RULE users_insert_0 AS
ON INSERT TO users WHERE
(uid >= 0 and uid < 100)
DO INSTEAD
INSERT INTO users_0 VALUES (,);
CREATE RULE users_insert_1 AS
ON INSERT TO users WHERE
(uid >= 100)
DO INSTEAD
INSERT INTO users_1 VALUES (,);
The following is the test to write data:
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 data list)
postgres=# select * from users_0;
uid | name
-----+-------
20 | aaaaa
(1 data list)
postgres=# select * from users_1;
uid | name
-----+-----------
100 | smallfish
(1 data list)
The table partition can be calculated here, but there is still a place to modify, let’s 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 list)
According to the original idea, if the uid is less than 100, it should theoretically just query the users_0 table. Through EXPLAIN, you can see other tables that scan all 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 list)
The whole process is OK here!