文章摘要
这篇文章详细讲解了如何在PostgreSQL中创建表分区（Table Partitions）的步骤与注意事项。文章分为以下主要内容：
1. **表分区创建步骤**：
- 创建主表 `users`，包含 `uid` 和 `name` 列；
- 创建分区表 `users_0` 和 `users_1`，分别继承主表 `users`，并根据 `uid` 值范围进行分区；
- 在分区表上建立索引；
- 创建数据插入规则，分别针对不同分区的 `uid` 值范围进行数据写入。
2. **测试写入数据**：
- 插入数据后，验证写入结果；
- 使用 `EXPLAIN` 命令分析查询性能，发现未启用约束排除时，计数查询扫描了所有分区表；
- 启用 `SET CONSTRAINT EXCLUSION=ON` 后，优化了查询性能。
总结来说，文章通过实例展示了如何在PostgreSQL中实现表分区，并强调了分区表的优化与查询性能的提升。文章还提供了相关扩展阅读文章，供读者进一步学习和参考。
（关键词：CREATE TABLE、PARTITION、INHERITS、RULE、EXPLAIN、CONSTRUCTOR EXCLUSION）
创建表分区步骤如下：
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啦！
您可能感兴趣的文章:PostgreSQL LIST、RANGE 表分区的实现方案浅析postgresql 数据库 TimescaleDB 修改分区时间范围利用python为PostgreSQL的表自动添加分区如何为PostgreSQL的表自动添加分区浅谈PostgreSQL 11 新特性之默认分区PostgreSQL之分区表(partitioning)PostgreSQL分区表（partitioning）应用实例详解PostgreSQL教程(三)：表的继承和分区表详解浅谈PostgreSQL表分区的三种方式
© 版权声明
文章版权归作者所有，未经允许请勿转载。
