学习openGauss的第十九天。
主要内容是学习openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint
连接数据库
su - omm
gsql -r
1.创建分区表，并用generate_series(1,N)函数对表插入数据
create table par_tab (col1 int ,col2 char(20),col3 char(20))
partition by range(col1)
(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30)
);
insert into par_tab values(1, 'a','aa');
insert into par_tab values(6, 'b','bb');
insert into par_tab values(15,'c','cc');
insert into par_tab values(23,'d','dd');
insert into par_tab values(generate_series(1, 10));
omm=# create table par_tab (col1 int ,col2 char(20),col3 char(20))
omm-# (
omm(# partition by range(col1)
omm-# partition p1 values less than (10),
omm(# partition p2 values less than (20),
omm(# partition p3 values less than (30)
omm(# );
CREATE TABLE
omm=# insert into par_tab values(1, 'a','aa');
INSERT 0 1
omm=# insert into par_tab values(6, 'b','bb');
INSERT 0 1
omm=# insert into par_tab values(15,'c','cc');
INSERT 0 1
omm=# insert into par_tab values(23,'d','dd');
INSERT 0 1
omm=# insert into par_tab values(generate_series(1, 10));
INSERT 0 10
omm=# select * from par_tab ;
col1 | col2 | col3
------+----------------------+----------------------
1 | a | aa
6 | b | bb
1 | |
2 | |
3 | |
4 | |
5 | |
6 | |
7 | |
8 | |
9 | |
15 | c | cc
10 | |
23 | d | dd
(14 rows)
2.收集表统计信息
select relname, relpages, reltuples from pg_class where relname = 'par_tab';
omm=# select relname, relpages, reltuples from pg_class where relname = 'par_tab';
omm=# relname | relpages | reltuples
---------+----------+-----------
par_tab | 0 | 0
(1 row)
3.显示简单查询的执行计划；建立索引并显示有索引条件的执行计划
explain select * from par_tab;
create index idx_par on par_tab(col1) ;
explain select * from par_tab;
omm=# explain select * from par_tab;
QUERY PLAN
------------------------------------------------------------------------------
Partition Iterator (cost=0.00..14.04 rows=404 width=172)
Iterations: 3
-> Partitioned Seq Scan on par_tab (cost=0.00..14.04 rows=404 width=172)
Selected Partitions: 1..3
(4 rows)
omm=# create index idx_par on par_tab(col1) ;
CREATE INDEX
omm=# explain select * from par_tab;
QUERY PLAN
------------------------------------------------------------------------------
Partition Iterator (cost=0.00..14.04 rows=404 width=172)
Iterations: 3
-> Partitioned Seq Scan on par_tab (cost=0.00..14.04 rows=404 width=172)
Selected Partitions: 1..3
(4 rows)
4.更新表数据，并做垃圾收集
update par_tab set col1 = col1 + 1 ;
VACUUM (VERBOSE, ANALYZE) par_tab;
omm=# update par_tab set col1 = col1 + 1 ;
UPDATE 14
omm=#
omm=# VACUUM (VERBOSE, ANALYZE) par_tab;
INFO: vacuuming "public.par_tab"(gaussdb pid=1)
INFO: index "idx_par" now contains 21 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "par_tab": found 0 removable, 21 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 11 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.par_tab"(gaussdb pid=1)
INFO: scanned index "idx_par" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "idx_par" now contains 5 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "par_tab": found 10 removable, 5 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 2 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "public.par_tab"(gaussdb pid=1)
INFO: scanned index "idx_par" to remove 10 row versions(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "idx_par" now contains 2 row versions in 2 pages(gaussdb pid=1)
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "par_tab": found 10 removable, 2 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL: 1 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "idx_par" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.par_tab"(gaussdb pid=1)
INFO: ANALYZE INFO : "par_tab": scanned 1 of 1 pages, containing 10 live rows and 11 dead rows; 10 rows in sample, 10 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "par_tab": scanned 1 of 1 pages, containing 3 live rows and 2 dead rows; 3 rows in sample, 3 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "par_tab": scanned 1 of 1 pages, containing 1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total rows(gaussdb pid=1)
VACUUM
5.清理数据
drop table par_tab
omm=# drop table par_tab;
DROP TABLE
