openGauss每日一练第21天 |行存和列存

今天是openGauss每日一练第21天，感谢有这种形式学习，对新知识的掌握更加夯实。

今天学习存储方式:

• 行存储：是指将表按行存储到硬盘分区上;
• 列存储：是指将表按列存储到硬盘分区上。

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

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-TABLE.html

注意事项

• 列存表不支持数组。
• 列存表不支持生成列。
• 列存表不支持创建全局临时表。
• 创建列存表的数量建议不超过1000个。
• 列存表的表级约束只支持PARTIAL CLUSTER KEY、UNIQUE、PRIAMRY KEY，不支持外键等表级约束。
• 列存表的字段约束只支持NULL、NOT NULL、DEFAULT常量值、UNIQUE和PRIMARY KEY。
• 每张表的列数最大为1600，具体取决于列的类型，所有列的大小加起来不能超过8192 byte，text、varchar、char等长度可变的类型除外。

学习笔记：

•     创建列表：CREATE TABLE test_t2 ( col1 CHAR(2), col2 VARCHAR2(40), col3 NUMBER ) WITH (ORIENTATION = COLUMN);

课程作业

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

openGauss=# CREATE TABLE t1
openGauss-# (
openGauss(# aa CHAR(2),
openGauss(# bb VARCHAR2(40),
openGauss(# cc NUMBER
openGauss(# );
CREATE TABLE
openGauss=# insert into t1 select col1, col2, col3 from (
openGauss(# 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
openGauss=# 
openGauss=# CREATE TABLE t2
openGauss-# (
openGauss(# aa CHAR(2),
openGauss(# bb VARCHAR2(40),
openGauss(# cc NUMBER
openGauss(# ) WITH (ORIENTATION = COLUMN);
CREATE TABLE
openGauss=# insert into t2 values select * from t1;
ERROR:  syntax error at or near "select"
LINE 1: insert into t2 values select * from t1;
                              ^
openGauss=# insert into t2 select * from t1;
INSERT 0 100000
openGauss=# 
复制

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

openGauss=# \d+
                                           List of relations
 Schema |    Name    | Type  | Owner |    Size    |               Storage                | Description 
--------+------------+-------+-------+------------+--------------------------------------+-------------
 public | t1         | table | omm   | 6760 kB    | {orientation=row,compression=no}     | 
 public | t2         | table | omm   | 1112 kB    | {orientation=column,compression=low} | 
复制

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

openGauss=# explain analyze select distinct aa from t1;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2091.00..2091.27 rows=27 width=3) (actual time=31.879..31.882 rows=27 loops=1)
   Group By Key: aa
   ->  Seq Scan on t1  (cost=0.00..1841.00 rows=100000 width=3) (actual time=0.011..15.187 rows=100000 loops=1)
 Total runtime: 31.959 ms
(4 rows)

openGauss=# explain analyze select distinct aa from t2;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Row Adapter  (cost=1008.27..1008.27 rows=27 width=3) (actual time=6.479..6.481 rows=27 loops=1)
   ->  Vector Sonic Hash Aggregate  (cost=1008.00..1008.27 rows=27 width=3) (actual time=6.477..6.477 rows=27 loops=1)
         Group By Key: aa
         ->  CStore Scan on t2  (cost=0.00..758.00 rows=100000 width=3) (actual time=0.054..0.536 rows=100000 loops=1)
 Total runtime: 8.691 ms
(5 rows)

openGauss=# 

openGauss=# explain analyze insert into t1 values('x', 'xxxx', '123');
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 [Bypass]
 Insert on t1  (cost=0.00..0.01 rows=1 width=0) (actual time=0.111..0.112 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.184 ms
(4 rows)

openGauss=# 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=9.748..9.750 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: 9.840 ms
(3 rows)

openGauss=# 
复制

4.清理数据

openGauss=# drop table t1;
DROP TABLE
openGauss=# drop table t2;
DROP TABLE
复制