openGauss每日一练第 21 天 |学习openGauss存储模型-行存和列存

学习openGauss的第二十一天。
学习openGauss存储模型-行存和列存

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。
行、列存储模型各有优劣，通常用于TP场景的数据库，默认使用行存储，仅对执行复杂查询且数据量大的AP场景时，才使用列存储

连接数据库

su - omm
gsql -r


1.创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)

create table tab1(col1 int,col2 char(20),col3 char(20)) ;
create table tab2(col1 int,col2 char(20),col3 char(20)) with (orientation = column);;

insert into tab1 values(generate_series(1, 100000) ,'a','b') ;
insert into tab2 values(generate_series(1, 100000) ,'a','b') ;
\d+ tab1
\d+ tab2 

omm=# create table tab1(col1 int,col2 char(20),col3 char(20)) ;
CREATE TABLE
omm=# create table tab2(col1 int,col2 char(20),col3 char(20)) with (orientation=column);
CREATE TABLE
omm=# insert into tab1 values(generate_series(1, 100000) ,'a','b') ;
INSERT 0 100000
omm=# insert into tab2 values(generate_series(1, 100000) ,'a','b') ;
INSERT 0 100000

omm=# \d+ tab1
Table "public.tab1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
col1 | integer | | plain | |
col2 | character(20) | | extended | |
col3 | character(20) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no

omm=# \d+ tab2
col2 | character(20) | | extended | |
col3 | character(20) | | extended | |
Has OIDs: no
Options: orientation=column, compression=low

omm=# Table "public.tab2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
col1 | integer | | plain | |


2.对比行存表和列存表空间大小

\d+

omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Desc
ription
--------+------+-------+-------+---------+--------------------------------------+-----
--------
public | tab1 | table | omm | 7504 kB | {orientation=row,compression=no} |
public | tab2 | table | omm | 53 MB | {orientation=column,compression=low} |
(2 rows)


3.对比查询一列和插入一行的速度

analyze VERBOSE tab1;
analyze VERBOSE tab2;

explain analyze insert into tab1 values('111', 'abc', '123');
explain analyze insert into tab2 values('111', 'abc', '123');

omm=# analyze VERBOSE tab1;
INFO: analyzing "public.tab1"(gaussdb pid=1)
INFO: ANALYZE INFO : "tab1": scanned 935 of 935 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE

omm=# analyze VERBOSE tab2;
INFO: analyzing "public.tab2"(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of "pg_delta_16395": 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 : "tab2": scanned 100000 of 100000 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE


omm=# explain analyze insert into tab1 values('111', 'abc', '123');
QUERY PLAN

--------------------------------------------------------------------------------------------
[Bypass]
Insert on tab1 (cost=0.00..0.01 rows=1 width=0) (actual time=0.122..0.123 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Total runtime: 0.200 ms
(4 rows)

omm=# explain analyze insert into tab2 values('111', 'abc', '123');
QUERY PLAN

--------------------------------------------------------------------------------------------
Insert on tab2 (cost=0.00..0.01 rows=1 width=0) (actual time=0.191..0.192 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Total runtime: 0.299 ms
(3 rows)

4.清理数据

drop table tab1 ;
drop table tab2 ;

omm=# drop table tab1 ;
DROP TABLE
omm=# drop table tab2 ;
DROP TABLE