亚洲欧美欧美一区二区三区,欧美日韩精品一区视频,欧美一级xxx

主頁 > 知識庫 > PostgreSQL 創建表分區

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 uid100;
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 uid100;
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表分區的三種方式

標簽：黃石池州白山呼倫貝爾舟山菏澤楚雄安順

巨人網絡通訊聲明：本文標題《PostgreSQL 創建表分區》，本文關鍵詞 PostgreSQL,創建,表,分區,PostgreSQL,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。

婷婷综合国产,91蜜桃婷婷狠狠久久综合9色 ,九九九九九精品,国产综合av

PostgreSQL 創建表分區