旷世神医,旷世神医,大主宰天蚕土豆小说

創(chuàng)建表分區(qū)步驟如下：
1. 創(chuàng)建主表
CREATE TABLE users ( uid int not null primary key, name varchar(20));
2. 創(chuàng)建分區(qū)表(必須繼承上面的主表)
CREATE TABLE users_0 ( check (uid >= 0 and uid 100) ) INHERITS (users);
CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);
3. 在分區(qū)表上建立索引，其實(shí)這步可以省略的哦
CREATE INDEX users_0_uidindex on users_0(uid);
CREATE INDEX users_1_uidindex on users_1(uid);
4. 創(chuàng)建規(guī)則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);
下面就可以測(cè)試寫(xiě)入數(shù)據(jù)啦：
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 筆資料列)
到這里表分區(qū)已經(jīng)可以算完了，不過(guò)還有個(gè)地方需要修改下，先看count查詢(xún)把。
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 筆資料列)
按照本來(lái)想法，uid小于100，理論上應(yīng)該只是查詢(xún)users_0表，通過(guò)EXPLAIN可以看到其他他掃描了所有分區(qū)的表。
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 筆資料列)
到這里整個(gè)過(guò)程都OK啦！

您可能感興趣的文章:

PostgreSQL LIST、RANGE 表分區(qū)的實(shí)現(xiàn)方案
淺析postgresql 數(shù)據(jù)庫(kù) TimescaleDB 修改分區(qū)時(shí)間范圍
利用python為PostgreSQL的表自動(dòng)添加分區(qū)
如何為PostgreSQL的表自動(dòng)添加分區(qū)
淺談PostgreSQL 11 新特性之默認(rèn)分區(qū)
PostgreSQL之分區(qū)表(partitioning)
PostgreSQL分區(qū)表（partitioning）應(yīng)用實(shí)例詳解
PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
淺談PostgreSQL表分區(qū)的三種方式

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

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《PostgreSQL 創(chuàng)建表分區(qū)》，本文關(guān)鍵詞 PostgreSQL,創(chuàng)建,表,分區(qū),PostgreSQL,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。

濮阳杆衣贸易有限公司

PostgreSQL 創(chuàng)建表分區(qū)