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

openGauss每日一练第21天课后作业

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

create table  day21_test1(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
);

insert into day21_test1 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);

\d+ day21_test1;

CREATE TABLE day21_test2(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
)
WITH (ORIENTATION = COLUMN);

insert into day21_test2  select * from day21_test1;

\d+ day21_test2;

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

\d+

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

--先收集统计信息

analyze VERBOSE day21_test1;
analyze VERBOSE day21_test2;

--对比查询一列的速度

explain analyze select distinct col1 from day21_test1;
explain analyze select distinct col1 from day21_test2;

--对比插入一行的速度

explain analyze insert into day21_test1 values('20', '2021-12-20', 123456);

explain analyze insert into day21_test1 values('21', '2021-12-21', 654321);

4.清理数据

drop table day21_test1;

drop table day21_test2;