课程作业
1.创建行存表和列存表，并批量插入10万条数据(行存表和列存表数据相同)
omm=# create table student (id char(10),name varchar(40),age number);
omm=# CREATE TABLE
omm=# \d+ student
Table "public.student"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | character(10) | | extended | |
name | character varying(40) | | extended | |
age | numeric | | main | |
Has OIDs: no
Options: orientation=row, compression=no
omm=# insert into student select id, name, age from (select generate_series(1, 100000) as key, repeat(chr(int4(random() * 10) + 11), 10) as id, repeat(chr(int4(random() * 22) + 33), 40) as name, (random() * (10^3))::integer as age);
INSERT 0 100000
omm=# create table student2 (id char(10),name varchar(40),age number) WITH (ORIENTATION = COLUMN);
CREATE TABLE
omm=# \d+ student2
Table "public.student2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | character(10) | | extended | |
name | character varying(40) | | extended | |
age | numeric | | main | |
Has OIDs: no
Options: orientation=column, compression=low
omm=# insert into student2 select * from student;
INSERT 0 100000
2.对比行存表和列存表空间大小
omm=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------+-------+-------+---------+--------------------------------------+-------------
public | student | table | omm | 9128 kB | {orientation=row,compression=no} |
public | student2 | table | omm | 1024 kB | {orientation=column,compression=low} |
(2 rows)
3.对比查询一列和插入一行的速度
omm=# analyze verbose student;
INFO: analyzing "public.student"(gaussdb pid=1)
INFO: ANALYZE INFO : "student": scanned 1137 of 1137 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# analyze verbose student2;
INFO: analyzing "public.student2"(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 : "student2": scanned 2 of 2 cus, sample 30000 rows, estimated total 100000 rows(gaussdb pid=1)
ANALYZE
omm=# explain analyze select distinct id from student;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2387.00..2387.11 rows=11 width=11) (actual time=55.313..55.316 rows=11 loops=1)
Group By Key: id
-> Seq Scan on student (cost=0.00..2137.00 rows=100000 width=11) (actual time=0.013..28.463 rows=100000 loops=1)
Total runtime: 55.374 ms
(4 rows)
omm=# explain analyze select distinct id from student2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Row Adapter (cost=1272.11..1272.11 rows=11 width=11) (actual time=4.446..4.449 rows=11 loops=1)
-> Vector Sonic Hash Aggregate (cost=1272.00..1272.11 rows=11 width=11) (actual time=4.443..4.443 rows=11 loops=1)
Group By Key: id
-> CStore Scan on student2 (cost=0.00..1022.00 rows=100000 width=11) (actual time=0.032..0.380 rows=100000 loops=1)
Total runtime: 4.545 ms
(5 rows)
omm=# explain analyze insert into student values(122,'lala',888);
QUERY PLAN
-----------------------------------------------------------------------------------------------
[Bypass]
Insert on student (cost=0.00..0.01 rows=1 width=0) (actual time=0.074..0.075 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
Total runtime: 0.182 ms
(4 rows)
omm=# explain analyze insert into student2 values(122,'lala',888);
omm=# QUERY PLAN
------------------------------------------------------------------------------------------------
Insert on student2 (cost=0.00..0.01 rows=1 width=0) (actual time=3.212..3.214 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: 3.307 ms
(3 rows)
4.清理数据
omm=# drop table student;
DROP TABLE
omm=# drop table student2;
omm=# DROP TABLE
