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

学习目标

学习openGauss存储模型-行存和列存

行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。默认情况下，创建的表为行存储。

行、列存储模型各有优劣，通常用于TP场景的数据库，默认使用行存储，仅对执行复杂查询且数据量大的AP场景时，才使用列存储

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

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

需要自己建表！！

-- 行存表
create table t1
(
    id int,
    name varchar2(20)
);
\d+ t1

-- 列存表
create table t2
(
    id int,
    name varchar2(20)
)
with (ORIENTATION = COLUMN)
;
\d+ t2

-- 批量插入10万数据
insert into t1 select id, name from (select generate_series(1, 1000000) as id, repeat(chr(int4(random() * 26) + 65), 2) as name);

select count(*) from t1;

-- 插入t2
insert into t2 select * from t1;
select count(*) from t2;

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

\d+

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

-- 收集统计信息
analyze VERBOSE t1;
analyze VERBOSE t2;

-- 对比查询一列的速度
explain analyze select distinct id from t1;
explain analyze select distinct id from t2;

-- 对比插入一行的速度
explain analyze insert into t1 values(2000000, 'name2000000');
explain analyze insert into t2 values(2000000, 'name2000000');

4.清理数据

\d
drop table t1;
drop table t2;
\d

作业执行结果

omm=# -- 行存表
omm=# create table t1
omm-# (
id int,
name varchar2(20)
 );
CREATE TABLE
omm=# \d+ t1
                                 Table "public.t1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | Options: orientation=row, compression=no

omm=# 
omm=# 
 name   | character varying(20) |           | extended |              | 
Has OIDs: no
-- 列存表

omm(# create table t2
id int,
name varchar2(20)
 )
with (ORIENTATION = COLUMN)
;
CREATE TABLE
omm=# \d+ t2
                                 Table "public.t2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=column, compression=low

omm=# omm=# 
-- 批量插入10万数据
omm=# insert into t1 select id, name from (select generate_series(1, 1000000) as id, repeat(chr(int4(random() * 26) + 65), 2) as name);

INSERT 0 1000000
omm=# 
omm=# select count(*) from t1;
  count  
---------
 1000000
(1 row)

omm=# 
omm=# -- 插入t2
omm=# insert into t2 select * from t1;
INSERT 0 1000000
omm=# select count(*) from t2;
  count  
---------
 1000000
(1 row)

omm=# 
omm=# 
omm=# \d+
                                      List of relations
 Schema | Name | Type  | Owner |  Size   |               Storage                | Description 
--------+------+-------+-------+---------+--------------------------------------+-------------
 public | t1   | table | omm   | 35 MB   | {orientation=row,compression=no}     | 
 public | t2   | table | omm   | 4968 kB | {orientation=column,compression=low} | 
(2 rows)

omm=# 
omm=# -- 收集统计信息
omm=# analyze VERBOSE t1;
INFO:  analyzing "public.t1"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 4425 of 4425 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 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_16448": 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 17 of 17 cus, sample 30000 rows, estimated total 1000000 rows(gaussdb pid=1)
ANALYZE
omm=# 
omm=# -- 对比查询一列的速度
omm=# explain analyze select distinct id from t1;
                                                     QUERY PLAN                                  
                   
-------------------------------------------------------------------------------------------------
-------------------
 HashAggregate  (cost=16925.00..26925.00 rows=1000000 width=4) (actual time=681.859..1024.240 row
s=1000000 loops=1)
   Group By Key: id
   ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.025..287.878 row
s=1000000 loops=1)
 Total runtime: 1135.763 ms
(4 rows)

omm=# explain analyze select distinct id from t2;

         Group By Key: id
         ->  CStore Scan on t2  (cost=0.00..1867.00 rows=1000000 width=4) (actual time=0.069..4.5
84 rows=1000000 loops=1)
 Total runtime: 299.823 ms
(5 rows)

                                                              QUERY PLAN                         
                                     
-------------------------------------------------------------------------------------------------
-------------------------------------
 Row Adapter  (cost=14367.00..14367.00 rows=1000000 width=4) (actual time=116.564..226.820 rows=1
000000 loops=1)
   ->  Vector Sonic Hash Aggregate  (cost=4367.00..14367.00 rows=1000000 width=4) (actual time=11
6.558..131.607 rows=1000000 loops=1)omm=# 
omm=# -- 对比插入一行的速度
omm=# explain analyze insert into t1 values(2000000, 'name2000000');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 [Bypass]
 Insert on t1  (cost=0.00..0.01 rows=1 width=0) (actual time=0.112..0.113 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.175 ms
(4 rows)

omm=# explain analyze insert into t2 values(2000000, 'name2000000');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Insert on t2  (cost=0.00..0.01 rows=1 width=0) (actual time=0.150..0.150 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
 Total runtime: 0.207 ms
(3 rows)


omm=# 
omm=# omm=# \d
                          List of relations
 Schema | Name | Type  | Owner |               Storage                
--------+------+-------+-------+--------------------------------------
 public | t1   | table | omm   | {orientation=row,compression=no}
 public | t2   | table | omm   | {orientation=column,compression=low}
(2 rows)

omm=# drop table t1;
DROP TABLE
omm=# drop table t2;
DROP TABLE
omm=# \d
No relations found.