gpt4 book ai didi

PostgreSQL 创建表分区

转载 作者:qq735679552 更新时间:2022-09-29 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章PostgreSQL 创建表分区由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.

创建表分区步骤如下: 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 创建表分区的文章就讲到这里了,如果你想了解更多关于PostgreSQL 创建表分区的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com