openGauss每日一练第21天 openGauss存储模型-行存和列存
1.创建行存表
CREATE TABLE test_t1
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
);
omm=#
omm=# CREATE TABLE test_t1
omm-# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# );
CREATE TABLE
–压缩属性为no
\d+ test_t1
omm=# \d+ test_t1
Table “public.test_t1”
Column | Type | Modifiers | Storage | Stats target | Description
--------±----------------------±----------±---------±-------------±------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=row, compression=no
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);
omm=# 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);
INSERT 0 100000
2.创建列存表
CREATE TABLE test_t2
(
col1 CHAR(2),
col2 VARCHAR2(40),
col3 NUMBER
)
WITH (ORIENTATION = COLUMN);
omm=# CREATE TABLE test_t2
omm-# (
omm(# col1 CHAR(2),
omm(# col2 VARCHAR2(40),
omm(# col3 NUMBER
omm(# omm-# )
WITH (ORIENTATION = COLUMN);
CREATE TABLE
–压缩属性为low
\d+ test_t2;
omm=# \d+ test_t2;
Table “public.test_t2”
Column | Type | Modifiers | Storage | Stats target | Description
--------±----------------------±----------±---------±-------------±------------
col1 | character(2) | | extended | |
col2 | character varying(40) | | extended | |
col3 | numeric | | main | |
Has OIDs: no
Options: orientation=column, compression=low
–插入和行存表相同的数据
insert into test_t2 select * from test_t1;
omm=# insert into test_t2 select * from test_t1;
INSERT 0 100000
3.占用空间对比
\d+
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------±--------±------±------±--------±-------------------------------------±------------
public | test_t1 | table | omm | 6760 kB | {orientation=row,compression=no} |
public | test_t2 | table | omm | 1112 kB | {orientation=column,compression=low} |
(2 rows)
4.对比读取一列的速度
analyze VERBOSE test_t1;
analyze VERBOSE test_t2;
omm=# analyze VERBOSE test_t1;
INFO: analyzing “public.test_t1”(gaussdb pid=1)
INFO: ANALYZE INFO : “test_t1”: scanned 841 of 841 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=#
omm=# analyze VERBOSE test_t2;
INFO: analyzing “public.test_t2”(gaussdb pid=1)
INFO: ANALYZE INFO : estimate total rows of “pg_delta_16395”: 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 : “test_t2”: scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
–列存表时间少于行存表
explain analyze select distinct col1 from test_t1;
explain analyze select distinct col1 from test_t2;
omm=# explain analyze select distinct col1 from test_t1;
QUERY PLAN
HashAggregate (cost=2091.00…2091.27 rows=27 width=3) (actual time=51.899…51.904 rows=27 loops=1)
Group By Key: col1
-> Seq Scan on test_t1 (cost=0.00…1841.00 rows=100000 width=3) (actual time=0.011…24.916 rows=100000
loops=1)
Total runtime: 51.960 ms
(4 rows)
omm=# explain analyze select distinct col1 from test_t2;
QUERY PLAN
Row Adapter (cost=1008.27…1008.27 rows=27 width=3) (actual time=4.161…4.165 rows=27 loops=1)
-> Vector Sonic Hash Aggregate (cost=1008.00…1008.27 rows=27 width=3) (actual time=4.160…4.161 rows=
27 loops=1)
Group By Key: col1
-> CStore Scan on test_t2 (cost=0.00…758.00 rows=100000 width=3) (actual time=0.031…0.265 rows
=100000 loops=1)
Total runtime: 4.271 ms
(5 rows)
5.对比插入一行的速度
–行存表时间少于列存表
explain analyze insert into test_t1 values(‘x’, ‘xxxx’, ‘123’);
explain analyze insert into test_t2 values(‘x’, ‘xxxx’, ‘123’);
omm=# explain analyze insert into test_t1 values(‘x’, ‘xxxx’, ‘123’);
QUERY PLAN
[Bypass]
Insert on test_t1 (cost=0.00…0.01 rows=1 width=0) (actual time=0.059…0.060 rows=1 loops=1)
-> Result (cost=0.00…0.01 rows=1 width=0) (actual time=0.002…0.002 rows=1 loops=1)
Total runtime: 0.146 ms
(4 rows)
omm=# explain analyze insert into test_t2 values(‘x’, ‘xxxx’, ‘123’);
QUERY PLAN
Insert on test_t2 (cost=0.00…0.01 rows=1 width=0) (actual time=3.755…3.756 rows=1 loops=1)
-> Result (cost=0.00…0.01 rows=1 width=0) (actual time=0.002…0.002 rows=1 loops=1)
Total runtime: 3.839 ms
(3 rows)
6.清理数据
drop table test_t1;
drop table test_t2;
omm=# drop table test_t1;
DROP TABLE
omm=# omm=#
omm=# drop table test_t2;
omm=# DROP TABLE
omm=#
课程作业
1.创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)
omm=# create table table1(id int, info text, c_time timestamp);
CREATE TABLE
omm=# insert into table1 select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000
omm=# create table table1(id int, info text, c_time timestamp);
CREATE TABLE
omm=# insert into table1 select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000
omm=# create table table2 (id int, info text, c_time timestamp) WITH (ORIENTATION = COLUMN);
CREATE TABLE
omm=# insert into table2 select * from table1;
INSERT 0 100000
2.对比行存表和列存表空间大小
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------±-------±------±------±--------±-------------------------------------±------------
public | table1 | table | omm | 7512 kB | {orientation=row,compression=no} |
public | table2 | table | omm | 3904 kB | {orientation=column,compression=low} |
(2 rows)
3.对比查询一列和插入一行的速度
omm=# analyze VERBOSE table1;
INFO: analyzing “public.table1”(gaussdb pid=1)
INFO: ANALYZE INFO : “table1”: scanned 935 of 935 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze VERBOSE table2;
INFO: analyzing “public.table2”(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 : “table2”: scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
omm=# explain analyze select count(*) from table1;
QUERY PLAN
Aggregate (cost=2185.00…2185.01 rows=1 width=8) (actual time=32.310…32.310 rows=1 loops=1)
-> Seq Scan on table1 (cost=0.00…1935.00 rows=100000 width=0) (actual time=0.011…18.627 rows=100000
loops=1)
Total runtime: 32.375 ms
(3 rows)
omm=# explain analyze select count(*) from table2;
QUERY PLAN
Row Adapter (cost=1008.01…1008.01 rows=1 width=8) (actual time=0.618…0.619 rows=1 loops=1)
-> Vector Aggregate (cost=1008.00…1008.01 rows=1 width=8) (actual time=0.615…0.615 rows=1 loops=1)
-> CStore Scan on table2 (cost=0.00…758.00 rows=100000 width=0) (actual time=0.025…0.272 rows=
100000 loops=1)
Total runtime: 0.713 ms
(4 rows)
omm=# select * from table1 limit 2;
id | info | c_time
----±---------------------------------±---------------------------
1 | 97773cf7fb8829fda9f24f3816dce73a | 2021-12-24 17:46:30.734842
2 | e01c1ee4a06c14c2cf2ab0826ac6b423 | 2021-12-24 17:46:30.734971
(2 rows)
omm=# insert into table1 values(100001,‘aaaa’,clock_timestamp());
INSERT 0 1
omm=# explain analyze insert into table1 values(100003,‘aaaa’,clock_timestamp());
QUERY PLAN
Insert on table1 (cost=0.00…0.01 rows=1 width=0) (actual time=0.037…0.038 rows=1 loops=1)
-> Result (cost=0.00…0.01 rows=1 width=0) (actual time=0.009…0.009 rows=1 loops=1)
Total runtime: 0.141 ms
(3 rows)
omm=# explain analyze insert into table2 values(100002,‘aaaa’,clock_timestamp());
QUERY PLAN
Insert on table2 (cost=0.00…0.01 rows=1 width=0) (actual time=0.172…0.173 rows=1 loops=1)
-> Result (cost=0.00…0.01 rows=1 width=0) (actual time=0.011…0.011 rows=1 loops=1)
Total runtime: 0.270 ms
(3 rows)
4.清理数据
omm=# drop table table1;
DROP TABLE
omm=# drop table table2;
DROP TABLE
