今天是学习openGausss的第21天，行列存储。
openGauss支持行存、列存。
默认情况下，创建的表为行存储。
行存储是指将表按行存储到硬盘分区上，列存储是指将表按列存储到硬盘分区上。
行、列存储模型各有优劣，通常用于OLTP场景的数据库，默认使用行存储。仅对执行复杂查询且数据量大的OLAP场景时，才使用列存储。
测试样例：
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# create table test01(c1 int,c2 varchar(30));
CREATE TABLE
omm=# insert into test01 select c1,c2 from (select generate_series(1, 100000) as key, (random() * (10^4))::integer as c1,repeat(chr(int4(random() * 26) + 65), 2) as c2);
INSERT 0 100000
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+--------+-------+-------+---------+----------------------------------+-------------
public | test01 | table | omm | 3568 kB | {orientation=row,compression=no} |
(1 rows)
omm=# create table test02(c1 int,c2 varchar(30)) with (orientation=column);
CREATE TABLE
omm=# insert into test02 select * from test01;
INSERT 0 100000
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+--------+-------+-------+---------+--------------------------------------+-------------
public | test01 | table | omm | 3568 kB | {orientation=row,compression=no} |
public | test02 | table | omm | 528 kB | {orientation=column,compression=low} |
(2 rows)
omm=# analyze verbose test01;
INFO: analyzing "public.test01"(gaussdb pid=1)
INFO: ANALYZE INFO : "test01": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze verbose test02;
INFO: analyzing "public.test02"(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of "pg_delta_16417": 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 : "test02": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
omm=# explain analyze select c1 from test01;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on test01 (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.016..22.890 rows=100000 loops=1)
Total runtime: 31.428 ms
(2 rows)
omm=# explain analyze select c1 from test02;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Row Adapter (cost=202.00..202.00 rows=100000 width=4) (actual time=0.033..9.024 rows=100000 loops=1)
-> CStore Scan on test02 (cost=0.00..202.00 rows=100000 width=4) (actual time=0.029..0.413 rows=100000 loops=1)
Total runtime: 15.349 ms
(3 rows)
omm=# explain analyze insert into test01 values(1,'woi');
QUERY PLAN
----------------------------------------------------------------------------------------------
[Bypass]
Insert on test01 (cost=0.00..0.01 rows=1 width=0) (actual time=0.070..0.071 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.163 ms
(4 rows)
omm=# explain analyze insert into test02 values(2,'ooo');
QUERY PLAN
----------------------------------------------------------------------------------------------
Insert on test02 (cost=0.00..0.01 rows=1 width=0) (actual time=0.153..0.154 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.241 ms
(3 rows)omm=# drop table test01;
DROP TABLE
omm=# drop table test02;
DROP TABLE
omm=#
