openGauss每日一练第19天|openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint

本次课学习的内容是openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint。

先进入实训环境，输入su - omm命令和密码连接openGauss。

root@modb:~# 
root@modb:~# su - omm
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.

作业打卡

1、创建分区表，并用generate_series(1,N)函数对表插入数据

omm=# create table t1
omm-# (t_id int,
omm(# t_name char(30) 
omm(# )
omm-# partition by range(t_id)
omm-# (    
omm(# partition t1_0 values less than(50),
omm(# partition t1_1 values less than(200),
omm(# partition t1_2 values less than(500),
omm(# partition t1_3 values less than(800)
omm(# );
CREATE TABLE
omm=# insert into t1 values(generate_series(10,799));
INSERT 0 790

2、收集表统计信息

omm=# select relname, relpages, reltuples from pg_class where relname = 't1';              
 relname | relpages | reltuples 
---------+----------+-----------
 t1      |        0 |         0
(1 row)
omm=# analyze VERBOSE t1;
INFO:  analyzing "public.t1"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 40 live rows and 123 dead rows; 40 rows in sample, 40 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 4 of 4 pages, containing 150 live rows and 450 dead rows; 150 rows in sample, 150 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 7 of 7 pages, containing 300 live rows and 900 dead rows; 300 rows in sample, 300 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 7 of 7 pages, containing 300 live rows and 900 dead rows; 300 rows in sample, 300 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# select relname, relpages, reltuples from pg_class where relname = 't1';   
 relname | relpages | reltuples 
---------+----------+-----------
 t1      |       19 |       790

3、显示简单查询的执行计划；建立索引并显示有索引条件的执行计划

omm=# EXPLAIN SELECT * FROM t1;                    
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..26.90 rows=790 width=128)
   Iterations: 4
   ->  Partitioned Seq Scan on t1  (cost=0.00..26.90 rows=790 width=128)
         Selected Partitions:  1..4
(4 rows)
omm=# create index t_id_index on t1(t_id) local;
CREATE INDEX
omm=# explain select * from t1 where t_id<200;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..13.59 rows=191 width=128)
   Iterations: 2
   ->  Partitioned Index Scan using t_id_index on t1  (cost=0.00..13.59 rows=191 width=128)
         Index Cond: (t_id < 200)
         Selected Partitions:  1..2
(5 rows)

4、更新表数据，并做垃圾收集

omm=# update t1 set t_id=t_id+50 where t_id <500;
UPDATE 490
omm=# vacuum (verbose,analyze) t1;
INFO:  vacuuming "public.t1"(gaussdb pid=1)
INFO:  scanned index "t_id_index" to remove 123 row versions(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "t_id_index" now contains 40 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:  "t1": found 123 removable, 40 nonremovable row versions in 1 out of 1 pages(gaussdb pid=1)
DETAIL:  40 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.t1"(gaussdb pid=1)
INFO:  scanned index "t_id_index" to remove 450 row versions(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "t_id_index" now contains 290 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:  "t1": found 450 removable, 290 nonremovable row versions in 4 out of 4 pages(gaussdb pid=1)
DETAIL:  150 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.t1"(gaussdb pid=1)
INFO:  scanned index "t_id_index" to remove 900 row versions(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "t_id_index" now contains 600 row versions in 5 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:  "t1": found 900 removable, 600 nonremovable row versions in 8 out of 8 pages(gaussdb pid=1)
DETAIL:  300 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.t1"(gaussdb pid=1)
INFO:  scanned index "t_id_index" to remove 900 row versions(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "t_id_index" now contains 350 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:  "t1": found 900 removable, 350 nonremovable row versions in 7 out of 7 pages(gaussdb pid=1)
DETAIL:  0 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:  analyzing "public.t1"(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 1 of 1 pages, containing 0 live rows and 40 dead rows; 0 rows in sample, 0 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 4 of 4 pages, containing 140 live rows and 150 dead rows; 140 rows in sample, 140 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 8 of 8 pages, containing 300 live rows and 300 dead rows; 300 rows in sample, 300 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "t1": scanned 7 of 7 pages, containing 350 live rows and 0 dead rows; 350 rows in sample, 350 estimated total rows(gaussdb pid=1)
VACUUM

5、清理数据

omm=# drop table t1;
DROP TABLE