行存、列存，堆表、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秒44GB1.8秒1.5秒13秒37毫秒AO行存8K不压缩81秒1.3GB168毫秒96毫秒356毫秒32毫秒AO行存2MB不压缩101秒39GB1.8秒1.7秒1.9秒158毫秒AO行存8K压缩5级80秒557MB322毫秒269毫秒505毫秒54毫秒AO行存2MB压缩5级104秒690MB1.6秒1.5秒1.7秒163毫秒AO行存8K压缩9级80秒557MB331毫秒247毫秒500毫秒58毫秒AO行存2MB压缩9级106秒690MB1.7秒1.38秒1.67秒162毫秒AO列存8K不压缩92.6秒39GB362毫秒622毫秒877毫秒36毫秒AO列存2MB不压缩98.8秒38GB140毫秒1.62秒1.8秒176毫秒AO列存8K压缩5级83秒1.4GB125毫秒2.2秒2.5秒58毫秒AO列存2MB压缩5级104秒593MB152毫秒1.37秒1.73秒189毫秒AO列存8K压缩9级83秒1.4GB122毫秒2.3秒2.5秒62毫秒AO列存2MB压缩9级106秒593MB136毫秒1.5秒1.77秒181毫秒

云端产品

阿里云 RDS PostgreSQL

阿里云 HybridDB for PostgreSQL