溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“PostgreSQL11有哪些新特性”，在日常操作中，相信很多人在PostgreSQL11有哪些新特性問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”PostgreSQL11有哪些新特性”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
Parallel Hash
Hash Join執行時，在構造Hash表和進行Hash連接時，PG 11可使用并行的方式執行。
測試腳本：
testdb=# create table t1 (c1 int,c2 varchar(40),c3 varchar(40)); CREATE TABLE testdb=# testdb=# insert into t1 select generate_series(1,5000000),'TEST'||generate_series(1,1000000),generate_series(1,1000000)||'TEST'; INSERT 0 5000000 testdb=# drop table if exists t2; DROP TABLE testdb=# create table t2 (c1 int,c2 varchar(40),c3 varchar(40)); CREATE TABLE testdb=# testdb=# insert into t2 select generate_series(1,1000000),'T2'||generate_series(1,1000000),generate_series(1,1000000)||'T2'; INSERT 0 1000000 testdb=# explain verbose testdb-# select t1.c1,t2.c1 testdb-# from t1 inner join t2 on t1.c1 = t2.c1; QUERY PLAN --------------------------------------------------------------------------------------------- Gather (cost=18372.00..107975.86 rows=101100 width=8) Output: t1.c1, t2.c1 Workers Planned: 2 -- 2 Workers -> Parallel Hash Join (cost=17372.00..96865.86 rows=42125 width=8) -- Parallel Hash Join Output: t1.c1, t2.c1 Hash Cond: (t1.c1 = t2.c1) -> Parallel Seq Scan on public.t1 (cost=0.00..45787.33 rows=2083333 width=4) Output: t1.c1 -> Parallel Hash (cost=10535.67..10535.67 rows=416667 width=4) -- Parallel Hash Output: t2.c1 -> Parallel Seq Scan on public.t2 (cost=0.00..10535.67 rows=416667 width=4) Output: t2.c1
除了Parallel Hash外,PG 11在執行Parallel Append(執行UNION ALL等集合操作)/CREATE TABLE AS SELECT/CREATE MATERIALIZED VIEW/SELECT INTO/CREATE INDEX等操作時以并行的方式執行.
Hash Partition
PG 在11.x引入了Hash分區,關于Hash分區,官方文檔有如下說明:
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
每個Hash分區需指定"模"(modulus)和"余"(remainder),數據在哪個分區(partition index)的計算公式:
partition index = abs(hashfunc(key)) % modulus
drop table if exists t_hash2; create table t_hash2 (c1 int,c2 varchar(40),c3 varchar(40)) partition by hash(c1); create table t_hash2_1 partition of t_hash2 for values with (modulus 6,remainder 0); create table t_hash2_2 partition of t_hash2 for values with (modulus 6,remainder 1); create table t_hash2_3 partition of t_hash2 for values with (modulus 6,remainder 2); create table t_hash2_4 partition of t_hash2 for values with (modulus 6,remainder 3); create table t_hash2_5 partition of t_hash2 for values with (modulus 6,remainder 4); create table t_hash2_6 partition of t_hash2 for values with (modulus 6,remainder 5); testdb=# insert into t_hash2 testdb-# select generate_series(1,1000000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH'; INSERT 0 1000000
數據在各分區上的分布大體均勻.
2018-9-19 注:由于插入數據時語句出錯,昨天得出的結果有誤(但數據在各個分區的分布上不太均勻,t_hash2_1分區行數明顯的比其他分區的要多很多),請忽略
testdb=# select count(*) from only t_hash2; ; count ------- 0 (1 row) testdb=# select count(*) from only t_hash2_1; count -------- 166480 (1 row) testdb=# select count(*) from only t_hash2_2; count -------- 166904 (1 row) testdb=# select count(*) from only t_hash2_3; count -------- 166302 (1 row) testdb=# select count(*) from only t_hash2_4; count -------- 166783 (1 row) testdb=# select count(*) from only t_hash2_5; count -------- 166593 (1 row) testdb=# select count(*) from only t_hash2_6; count -------- 166938 (1 row)
Hash分區鍵亦可以創建在字符型字段上
testdb=# drop table if exists t_hash4; DROP TABLE testdb=# create table t_hash4 (c1 int,c2 varchar(40),c3 varchar(40)) partition by hash(c2); CREATE TABLE -- 需創建相應的"Partition"用于存儲相應的數據 testdb=# insert into t_hash4 testdb-# select generate_series(1,100000),'HASH'||generate_series(1,1000000),generate_series(1,1000000)||'HASH'; ERROR: no partition of relation "t_hash4" found for row DETAIL: Partition key of the failing row contains (c2) = (HASH1). -- 6個分區,3個sub-table,插入數據會出錯 testdb=# testdb=# create table t_hash4_1 partition of t_hash4 for values with (modulus 6,remainder 0); CREATE TABLE testdb=# create table t_hash4_2 partition of t_hash4 for values with (modulus 6,remainder 1); CREATE TABLE testdb=# create table t_hash4_3 partition of t_hash4 for values with (modulus 6,remainder 2); CREATE TABLE testdb=# insert into t_hash4 testdb-# select generate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH'; ERROR: no partition of relation "t_hash4" found for row DETAIL: Partition key of the failing row contains (c2) = (HASH1). -- 3個分區,3個sub-table,正常 testdb=# drop table if exists t_hash4; DROP TABLE testdb=# create table t_hash4 (c1 int,c2 varchar(40),c3 varchar(40)) partition by hash(c2); CREATE TABLE testdb=# create table t_hash4_1 partition of t_hash4 for values with (modulus 3,remainder 0); CREATE TABLE testdb=# create table t_hash4_2 partition of t_hash4 for values with (modulus 3,remainder 1); CREATE TABLE testdb=# create table t_hash4_3 partition of t_hash4 for values with (modulus 3,remainder 2); CREATE TABLE testdb=# insert into t_hash4 testdb-# select generate_series(1,10000),'HASH'||generate_series(1,10000),generate_series(1,10000)||'HASH'; INSERT 0 10000
考察分區的數據分布,還比較均勻:
testdb=# testdb=# select count(*) from only t_hash4; count ------- 0 (1 row) testdb=# select count(*) from only t_hash4_1; count ------- 3378 (1 row) testdb=# select count(*) from only t_hash4_2; count ------- 3288 (1 row) testdb=# select count(*) from only t_hash4_3; count ------- 3334 (1 row)
Default Partition
List和Range分區可指定Default Partition(Hash分區不支持).
Update partition key
PG 11可Update分區鍵,這會導致數據的"遷移".
Create unique constraint
PG 11在分區表上創建主鍵和唯一索引(注:Oracle在很早的版本已支持此特性).
在普通字段上可以創建BTree索引.
testdb=# alter table t_hash2 add primary key(c1); ALTER TABLE testdb=# create index idx_t_hash2_c2 on t_hash2(c2); CREATE INDEX
FOREIGN KEY support
PG 11支持在分區上創建外鍵.
除了上述幾個新特性外,分區上面,PG 11在Automatic index creation/INSERT ON CONFLICT/Partition-Wise Join / Partition-Wise Aggregate/FOR EACH ROW trigger/Dynamic Partition Elimination/Control Partition Pruning上均有所增強.
到此，關于“PostgreSQL11有哪些新特性”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
