openGauss存储模型-行存和列存
1.创建行存表
omm=# CREATE TABLE test_t1
omm-# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
);omm(# col3 NUMBER
omm(#
CREATE TABLE
omm=# \d+ test_t1
Table "public.test_t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into test_t1 select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65), 2) as col1, repeat(chr(int4(random() * 26) + 65), 30) as col2, (random() * (10^4))::integer as col3);
INSERT 0 100000
2.创建列存表
omm=# CREATE TABLE test_t2
omm-# (
omm(# omm(# col1 CHAR(2),
col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# )
omm-# WITH (ORIENTATION = COLUMN);
CREATE TABLE
omm=# \d+ test_t2;
Table "public.test_t2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=column, compression=low
omm=# insert into test_t2 select * from test_t1;
INSERT 0 100000
3.占用空间对比
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------------------+-------+-------+------------+--------------------------------------+-------------
public | reason | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | table1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_t1 | table | omm | 6760 kB | {orientation=row,compression=no} |
omm=# public | test_t2 | table | omm | 1112 kB | {orientation=column,compression=low} |
public | test_trigger_des_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_trigger_src_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | tt1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | tt2 | table | omm | 8192 bytes | {orientation=row,compression=no} |
(8 rows)
4.对比读取一列的速度
omm=# analyze VERBOSE test_t1;
INFO: analyzing "public.test_t1"(gaussdb pid=1)
INFO: ANALYZE INFO : "test_t1": scanned 841 of 841 pages, containing 100000 live rows and 0 dead rows;
30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze VERBOSE test_t2;
INFO: analyzing "public.test_t2"(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of "pg_delta_16474": scanned 0 pages of total 0 pages with 1 retry times,
containing 0 live rows and 0 dead rows, estimated 0 total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "test_t2": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
omm=# explain analyze select distinct col1 from test_t1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2091.00..2091.27 rows=27 width=3) (actual time=51.992..51.995 rows=27 loops=1)
Group By Key: col1
-> Seq Scan on test_t1 (cost=0.00..1841.00 rows=100000 width=3) (actual time=0.013..25.132 rows=100000 loops=1)
Total runtime: 52.058 ms
(4 rows)
omm=# explain analyze select distinct col1 from test_t2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Row Adapter (cost=1008.27..1008.27 rows=27 width=3) (actual time=4.173..4.176 rows=27 loops=1)
omm=# -> Vector Sonic Hash Aggregate (cost=1008.00..1008.27 rows=27 width=3) (actual time=4.170..4.170 rows=27 loops=1)
Group By Key: col1
-> CStore Scan on test_t2 (cost=0.00..758.00 rows=100000 width=3) (actual time=0.031..0.272 rows=100000 loops=1)
Total runtime: 4.281 ms
(5 rows)
5.对比插入一行的速度
–行存表时间少于列存表
omm=# explain analyze insert into test_t1 values('x', 'xxxx', '123');
QUERY PLAN
-----------------------------------------------------------------------------------------------
[Bypass]
Insert on test_t1 (cost=0.00..0.01 rows=1 width=0) (actual time=0.061..0.062 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 0.158 ms
(4 rows)
omm=# explain analyze insert into test_t2 values('x', 'xxxx', '123');
QUERY PLAN
-----------------------------------------------------------------------------------------------
Insert on test_t2 (cost=0.00..0.01 rows=1 width=0) (actual time=4.848..4.849 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
Total runtime: 4.944 ms
(3 rows)
6.清理数据
omm=# drop table test_t1;
DROP TABLE
omm=# drop table test_t2
omm-#
omm-#
课程作业
1.创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)
omm=# CREATE TABLE t1
omm-# (
omm(#
omm=# col1 CHAR(2),
omm(# omm(# col3 NUMBER
omm(# col2 VARCHAR2(40),
);
CREATE TABLE
omm=#
CREATE TABLE t2
omm-# omm=# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# omm-# )
WITH (ORIENTATION = COLUMN);
CREATE TABLE
omm=#
omm=# insert into t1 select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65),
2) as col1, repeat(chr(int4(random() * 26) + 65), 30) as col2, (random() * (10^4))::integer as col3);
INSERT 0 100000
omm=#
omm=# insert into t2 select col1, col2, col3 from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 26) + 65),
2) as col1, repeat(chr(int4(random() * 26) + 65), 30) as col2, (random() * (10^4))::integer as col3);
INSERT 0 100000
omm=#
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------------------+-------+-------+------------+--------------------------------------+-------------
public | reason | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | t1 | table | omm | 6760 kB | {orientation=row,compression=no} |
public | t2 | table | omm | 1112 kB | {orientation=column,compression=low} |
public | table1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_t2 | table | omm | 1120 kB | {orientation=column,compression=low} |
public | test_trigger_des_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_trigger_src_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
(9 rows)
omm=# public | tt1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | tt2 | table | omm | 8192 bytes | {orientation=row,compression=no} |
2.对比行存表和列存表空间大小
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------------------+-------+-------+------------+--------------------------------------+-------------
public | reason | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | t1 | table | omm | 6760 kB | {orientation=row,compression=no} |
public | t2 | table | omm | 1112 kB | {orientation=column,compression=low} |
public | table1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_t2 | table | omm | 1120 kB | {orientation=column,compression=low} |
public | test_trigger_des_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | test_trigger_src_tbl | table | omm | 8192 bytes | {orientation=row,compression=no} |
(9 rows)
omm=# public | tt1 | table | omm | 8192 bytes | {orientation=row,compression=no} |
public | tt2 | table | omm | 8192 bytes | {orientation=row,compression=no} |
omm=# analyze VERBOSE t1;
INFO: analyzing "public.t1"(gaussdb pid=1)
INFO: ANALYZE INFO : "t1": scanned 841 of 841 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample,
100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze VERBOSE t2;
INFO: analyzing "public.t2"(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of "pg_delta_16496": scanned 0 pages of total 0 pages with 1 retry times,
containing 0 live rows and 0 dead rows, estimated 0 total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "t2": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
omm=#
3.对比查询一列和插入一行的速度
omm=# explain analyze insert into t1 values('x', 'xxxx', '123');
omm=# QUERY PLAN
------------------------------------------------------------------------------------------
[Bypass]
Insert on t1 (cost=0.00..0.01 rows=1 width=0) (actual time=0.058..0.059 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 0.167 ms
(4 rows)
explain analyze insert into t2 values('x', 'xxxx', '123');
QUERY PLAN
------------------------------------------------------------------------------------------
Insert on t2 (cost=0.00..0.01 rows=1 width=0) (actual time=4.491..4.493 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Total runtime: 4.566 ms
(3 rows)
4.清理数据
omm=# drop table t1;
DROP TABLE
omm=# drop table t2;
DROP TABLE
