213 技術社區[雲棲]

行存、列存，堆表、AO表性能對比 - 阿裏雲HDB for PostgreSQL最佳實踐

標簽

PostgreSQL , GIS , PostGIS , Greenplum , 空間檢索 , GiST , B-Tree , geohash

背景

《Greenplum 行存、列存，堆表、AO表的原理和選擇》

以上文檔詳細的介紹了行存、列存，堆表、AO表的原理以及選擇的依據。

《一個簡單算法可以幫助物聯網,金融用戶節約98%的數據存儲成本 (PostgreSQL,Greenplum幫你做到)》

以上文檔介紹了提升基於列存的全局數據壓縮比的方法。

《解密上帝之手 - 阿裏雲HDB for PostgreSQL數據庫metascan特性(存儲級、塊級、batch級過濾與數據編排)》

以上文檔介紹了局部編排，以及阿裏雲HDB for PostgreSQL數據庫的metascan特性，（在不需要索引的情況下，如何提升任意列的選擇性）。

壓縮實際上是計算(CPU)換空間(磁盤)的做法，該不該做，我們還是先看看壓縮比、性能損耗吧。

堆表

postgres=# create table t_heap(id int, c1 text, c2 int);  
CREATE TABLE  
  
postgres=# insert into t_heap select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
INSERT 0 10000000  
Time: 120526.098 ms

某個維度count查詢。

postgres=# explain analyze select c2,count(*) from t_heap group by c2;  
                                                                          QUERY PLAN                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Gather Motion 48:1  (slice2; segments: 48)  (cost=1578949.03..1579074.98 rows=10076 width=12)  
   Rows out:  10001 rows at destination with 1354 ms to end, start offset by 519 ms.  
   ->  HashAggregate  (cost=1578949.03..1579074.98 rows=210 width=12)  
         Group By: t_heap.c2  
         Rows out:  Avg 208.4 rows x 48 workers.  Max 223 rows (seg17) with 0.001 ms to first row, 692 ms to end, start offset by 581 ms.  
         ->  Redistribute Motion 48:48  (slice1; segments: 48)  (cost=1578596.37..1578797.89 rows=210 width=12)  
               Hash Key: t_heap.c2  
               Rows out:  Avg 10001.0 rows x 48 workers at destination.  Max 10704 rows (seg17) with 596 ms to end, start offset by 581 ms.  
               ->  HashAggregate  (cost=1578596.37..1578596.37 rows=210 width=12)  
                     Group By: t_heap.c2  
                     Rows out:  Avg 10001.0 rows x 48 workers.  Max 10001 rows (seg0) with 0.006 ms to first row, 131 ms to end, start offset by 566 ms.  
                     ->  Seq Scan on t_heap  (cost=0.00..1528595.58 rows=208337 width=4)  
                           Rows out:  Avg 208333.3 rows x 48 workers.  Max 208401 rows (seg18) with 26 ms to first row, 901 ms to end, start offset by 573 ms.  
 Slice statistics:  
   (slice0)    Executor memory: 359K bytes.  
   (slice1)    Executor memory: 724K bytes avg x 48 workers, 724K bytes max (seg0).  
   (slice2)    Executor memory: 388K bytes avg x 48 workers, 388K bytes max (seg0).  
 Statement statistics:  
   Memory used: 128000K bytes  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  
 Total runtime: 1874.143 ms  
(22 rows)  
  
Time: 1879.480 ms

無索引，某個單值查詢

postgres=# explain analyze select * from t_heap where c2=1;  
                                                             QUERY PLAN                                                               
------------------------------------------------------------------------------------------------------------------------------------  
 Gather Motion 48:1  (slice1; segments: 48)  (cost=0.00..1553595.98 rows=980 width=40)  
   Rows out:  1001 rows at destination with 1489 ms to end, start offset by 1.419 ms.  
   ->  Seq Scan on t_heap  (cost=0.00..1553595.98 rows=21 width=40)  
         Filter: c2 = 1  
         Rows out:  Avg 20.9 rows x 48 workers.  Max 35 rows (seg6) with 21 ms to first row, 613 ms to end, start offset by 853 ms.  
 Slice statistics:  
   (slice0)    Executor memory: 295K bytes.  
   (slice1)    Executor memory: 230K bytes avg x 48 workers, 230K bytes max (seg0).  
 Statement statistics:  
   Memory used: 128000K bytes  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  
 Total runtime: 1490.889 ms  
(13 rows)  
  
Time: 1492.516 ms

有索引（IO放大），某個單值查詢

create index idx_t_heap on t_heap(c2);  
  
explain analyze select * from t_heap where c2=1;  
  
postgres=# explain analyze select * from t_heap where c2=1;  
                                                               QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------  
 Gather Motion 48:1  (slice1; segments: 48)  (cost=107.99..95579.14 rows=980 width=40)  
   Rows out:  1001 rows at destination with 34 ms to end, start offset by 1.331 ms.  
   ->  Bitmap Heap Scan on t_heap  (cost=107.99..95579.14 rows=21 width=40)  
         Recheck Cond: c2 = 1  
         Rows out:  Avg 20.9 rows x 48 workers.  Max 35 rows (seg6) with 0.236 ms to first row, 0.959 ms to end, start offset by 19 ms.  
         ->  Bitmap Index Scan on idx_t_heap  (cost=0.00..107.74 rows=21 width=0)  
               Index Cond: c2 = 1  
               Bitmaps out:  Avg 1.0 x 48 workers.  Max 1 (seg0) with 0.169 ms to end, start offset by 19 ms.  
               Work_mem used:  168K bytes avg, 282K bytes max (seg6).  
 Slice statistics:  
   (slice0)    Executor memory: 303K bytes.  
   (slice1)    Executor memory: 901K bytes avg x 48 workers, 901K bytes max (seg0).  Work_mem: 282K bytes max.  
 Statement statistics:  
   Memory used: 128000K bytes  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  
 Total runtime: 35.093 ms  
(17 rows)  
  
Time: 37.198 ms

APPENDONLY 行存儲、列存儲

測試腳本如下

vi test.sql  
  
\timing  
  
-- 堆表  
create table t_heap(id int, c1 text, c2 int);  
  
insert into t_heap select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_heap group by c2;  
  
explain analyze select * from t_heap where c2=1;  
  
create index idx_t_heap on t_heap(c2);  
  
explain analyze select * from t_heap where c2=1;  
  
-- AO 行存  
-- 不壓縮, 8K  
  
create table t_ao_row_8k_0(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=row, COMPRESSTYPE=NONE, CHECKSUM=false);  
  
insert into t_ao_row_8k_0 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_8k_0 group by c2;  
  
explain analyze select * from t_ao_row_8k_0 where c2=1;  
  
create index idx_t_ao_row_8k_0 on t_ao_row_8k_0(c2);  
  
explain analyze select * from t_ao_row_8k_0 where c2=1;  
  
-- 不壓縮, 2M  
  
create table t_ao_row_2m_0(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=row, COMPRESSTYPE=NONE, CHECKSUM=false);  
  
insert into t_ao_row_2m_0 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_2m_0 group by c2;  
  
explain analyze select * from t_ao_row_2m_0 where c2=1;  
  
create index idx_t_ao_row_2m_0 on t_ao_row_2m_0(c2);  
  
explain analyze select * from t_ao_row_2m_0 where c2=1;  
  
-- 壓縮比5, 8K  
  
create table t_ao_row_8k_5(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=row, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, CHECKSUM=false);  
  
insert into t_ao_row_8k_5 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_8k_5 group by c2;  
  
explain analyze select * from t_ao_row_8k_5 where c2=1;  
  
create index idx_t_ao_row_8k_5 on t_ao_row_8k_5(c2);  
  
explain analyze select * from t_ao_row_8k_5 where c2=1;  
  
-- 壓縮比5, 2M  
  
create table t_ao_row_2m_5(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=row, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, CHECKSUM=false);  
  
insert into t_ao_row_2m_5 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_2m_5 group by c2;  
  
explain analyze select * from t_ao_row_2m_5 where c2=1;  
  
create index idx_t_ao_row_2m_5 on t_ao_row_2m_5(c2);  
  
explain analyze select * from t_ao_row_2m_5 where c2=1;  
  
-- 壓縮比9, 8K  
  
create table t_ao_row_8k_9(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=row, COMPRESSTYPE=zlib, COMPRESSLEVEL=9, CHECKSUM=false);  
  
insert into t_ao_row_8k_9 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_8k_9 group by c2;  
  
explain analyze select * from t_ao_row_8k_9 where c2=1;  
  
create index idx_t_ao_row_8k_9 on t_ao_row_8k_9(c2);  
  
explain analyze select * from t_ao_row_8k_9 where c2=1;  
  
-- 壓縮比9, 2M  
  
create table t_ao_row_2m_9(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=row, COMPRESSTYPE=zlib, COMPRESSLEVEL=9, CHECKSUM=false);  
  
insert into t_ao_row_2m_9 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_row_2m_9 group by c2;  
  
explain analyze select * from t_ao_row_2m_9 where c2=1;  
  
create index idx_t_ao_row_2m_9 on t_ao_row_2m_9(c2);  
  
explain analyze select * from t_ao_row_2m_9 where c2=1;  
  
-- AO 列存  
  
-- 不壓縮, 8K  
  
create table t_ao_COLUMN_8k_0(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=COLUMN, COMPRESSTYPE=NONE, CHECKSUM=false);  
  
insert into t_ao_COLUMN_8k_0 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_8k_0 group by c2;  
  
explain analyze select * from t_ao_COLUMN_8k_0 where c2=1;  
  
create index idx_t_ao_COLUMN_8k_0 on t_ao_COLUMN_8k_0(c2);  
  
explain analyze select * from t_ao_COLUMN_8k_0 where c2=1;  
  
-- 不壓縮, 2M  
  
create table t_ao_COLUMN_2m_0(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=COLUMN, COMPRESSTYPE=NONE, CHECKSUM=false);  
  
insert into t_ao_COLUMN_2m_0 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_2m_0 group by c2;  
  
explain analyze select * from t_ao_COLUMN_2m_0 where c2=1;  
  
create index idx_t_ao_COLUMN_2m_0 on t_ao_COLUMN_2m_0(c2);  
  
explain analyze select * from t_ao_COLUMN_2m_0 where c2=1;  
  
-- 壓縮比5, 8K  
  
create table t_ao_COLUMN_8k_5(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=COLUMN, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, CHECKSUM=false);  
  
insert into t_ao_COLUMN_8k_5 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_8k_5 group by c2;  
  
explain analyze select * from t_ao_COLUMN_8k_5 where c2=1;  
  
create index idx_t_ao_COLUMN_8k_5 on t_ao_COLUMN_8k_5(c2);  
  
explain analyze select * from t_ao_COLUMN_8k_5 where c2=1;  
  
-- 壓縮比5, 2M  
  
create table t_ao_COLUMN_2m_5(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=COLUMN, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, CHECKSUM=false);  
  
insert into t_ao_COLUMN_2m_5 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_2m_5 group by c2;  
  
explain analyze select * from t_ao_COLUMN_2m_5 where c2=1;  
  
create index idx_t_ao_COLUMN_2m_5 on t_ao_COLUMN_2m_5(c2);  
  
explain analyze select * from t_ao_COLUMN_2m_5 where c2=1;  
  
-- 壓縮比9, 8K  
  
create table t_ao_COLUMN_8k_9(id int, c1 text, c2 int) with (appendonly=true, blocksize=8192, ORIENTATION=COLUMN, COMPRESSTYPE=zlib, COMPRESSLEVEL=9, CHECKSUM=false);  
  
insert into t_ao_COLUMN_8k_9 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_8k_9 group by c2;  
  
explain analyze select * from t_ao_COLUMN_8k_9 where c2=1;  
  
create index idx_t_ao_COLUMN_8k_9 on t_ao_COLUMN_8k_9(c2);  
  
explain analyze select * from t_ao_COLUMN_8k_9 where c2=1;  
  
-- 壓縮比9, 2M  
  
create table t_ao_COLUMN_2m_9(id int, c1 text, c2 int) with (appendonly=true, blocksize=2097152, ORIENTATION=COLUMN, COMPRESSTYPE=zlib, COMPRESSLEVEL=9, CHECKSUM=false);  
  
insert into t_ao_COLUMN_2m_9 select id, repeat(md5(random()::text), 128), random()*10000 from generate_series(1,10000000) t(id);  
  
explain analyze select c2,count(*) from t_ao_COLUMN_2m_9 group by c2;  
  
explain analyze select * from t_ao_COLUMN_2m_9 where c2=1;  
  
create index idx_t_ao_COLUMN_2m_9 on t_ao_COLUMN_2m_9(c2);  
  
explain analyze select * from t_ao_COLUMN_2m_9 where c2=1;  
  
select tablename, pg_size_pretty(pg_total_relation_size('postgres.'||tablename)) from pg_tables where schemaname='postgres';

nohup psql -f ./test.sql > ./log 2>&1 &

結果對比

存儲形態	寫入1000萬耗時	空間占用	分組聚合	單值多行查詢(走全表)	建索引耗時	單值多行查詢(走索引)
堆表	120秒	44GB	1.8秒	1.5秒	13秒	37毫秒
AO行存8K不壓縮	81秒	1.3GB	168毫秒	96毫秒	356毫秒	32毫秒
AO行存2MB不壓縮	101秒	39GB	1.8秒	1.7秒	1.9秒	158毫秒
AO行存8K壓縮5級	80秒	557MB	322毫秒	269毫秒	505毫秒	54毫秒
AO行存2MB壓縮5級	104秒	690MB	1.6秒	1.5秒	1.7秒	163毫秒
AO行存8K壓縮9級	80秒	557MB	331毫秒	247毫秒	500毫秒	58毫秒
AO行存2MB壓縮9級	106秒	690MB	1.7秒	1.38秒	1.67秒	162毫秒
AO列存8K不壓縮	92.6秒	39GB	362毫秒	622毫秒	877毫秒	36毫秒
AO列存2MB不壓縮	98.8秒	38GB	140毫秒	1.62秒	1.8秒	176毫秒
AO列存8K壓縮5級	83秒	1.4GB	125毫秒	2.2秒	2.5秒	58毫秒
AO列存2MB壓縮5級	104秒	593MB	152毫秒	1.37秒	1.73秒	189毫秒
AO列存8K壓縮9級	83秒	1.4GB	122毫秒	2.3秒	2.5秒	62毫秒
AO列存2MB壓縮9級	106秒	593MB	136毫秒	1.5秒	1.77秒	181毫秒

雲端產品

阿裏雲 RDS PostgreSQL

阿裏雲 HybridDB for PostgreSQL

最後更新：2017-08-28 09:32:17

行存、列存，堆表、AO表性能對比 - 阿裏雲HDB for PostgreSQL最佳實踐

標簽

背景

堆表

APPENDONLY 行存儲、列存儲

結果對比

雲端產品

上一篇： Mongodb學習記錄：入門（一）——五葉草

下一篇：阿裏雲視頻技術專家柿蒂：視頻AI in傳媒九大業務場景解析

相關內容

熱門內容

最新內容

行存、列存，堆表、AO表性能對比 - 阿裏雲HDB for PostgreSQL最佳實踐

標簽

背景

堆表

APPENDONLY 行存儲、列存儲

結果對比

雲端產品

上一篇： Mongodb學習記錄：入門（一）——五葉草

下一篇： 阿裏雲視頻技術專家柿蒂：視頻AI in傳媒九大業務場景解析

相關內容

熱門內容

最新內容

下一篇：阿裏雲視頻技術專家柿蒂：視頻AI in傳媒九大業務場景解析