openGauss每日一练第21天 | 学习心得体会

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

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

执行火山模型和向量查询分别对应行列存储。

CREATE TABLE test_t1
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
);
\d+ test_t1
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);

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

\d+ test_t2;

insert into test_t2 select * from test_t1;

\d+

analyze VERBOSE test_t1;
analyze VERBOSE test_t2;

explain analyze select distinct col1 from test_t1;
explain analyze select distinct col1 from test_t2;

explain analyze insert into test_t1 values('x', 'xxxx', '123');
explain analyze insert into test_t2 values('x', 'xxxx', '123');

drop table test_t1;
drop table test_t2;

2.作业

CREATE TABLE A(id int)
WITH (ORIENTATION = COLUMN);

CREATE TABLE B(id int);

INSERT INTO A VALUES(generate_series(1, 100000));
INSERT INTO B VALUES(generate_series(1, 100000));

\d+

ANALYZE VERBOSE A;
ANALYZE VERBOSE B;

explain analyze select distinct id from A;
explain analyze select distinct id from B;

explain analyze insert into A values(5001);
explain analyze insert into B values(5001);

DROP TABLE A;
DROP TABLE B;