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

今天学习如何收集统计信息等，是对sql调优的基本操作吧。

学习笔记：

•     生成递增值：generate_series，eg:insert into 表名 values(generate_series(10, 10000));

•     查看表统计信息：pg_class：列relpages(该表磁盘表示的尺寸，以页面计（页面尺寸为BLCKSZ）), reltuples(表中的存活行数)

•     分析获取统计信息：analyze VERBOSE 表名;

      获取执行计划：EXPLAIN query; json格式：EXPLAIN(FORMAT JSON) query;

•     垃圾收集：VACUUM (VERBOSE, ANALYZE) 表名;

课后作业

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

openGauss=# create table tt( a int,b char(30)) partition by range(a) (partition p1 values less than(100),partition p2 values less than (1000));
CREATE TABLE
openGauss=# insert into tt values (generate_series(1,300));
INSERT 0 300

2.收集表统计信息

openGauss=# analyze VERBOSE tt;
INFO:  analyzing "public.tt"(dn_6001 pid=20588)
INFO:  ANALYZE INFO : "tt": scanned 1 of 1 pages, containing 99 live rows and 0 dead rows; 99 rows in sample, 99 estimated total rows(dn_6001 pid=20588)
INFO:  ANALYZE INFO : "tt": scanned 1 of 1 pages, containing 201 live rows and 0 dead rows; 201 rows in sample, 201 estimated total rows(dn_6001 pid=20588)
ANALYZE
openGauss=# select relname,relpages,reltuples from pg_class where relname='tt';
 relname | relpages | reltuples 
---------+----------+-----------
 tt      |        2 |       300
(1 row)

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

openGauss=# SET explain_perf_mode=normal;
SET
openGauss=# explain select * from tt;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..5.00 rows=300 width=128)
   Iterations: 2
   ->  Partitioned Seq Scan on tt  (cost=0.00..5.00 rows=300 width=128)
         Selected Partitions:  1..2
(4 rows)

openGauss=# 

openGauss=# SET explain_perf_mode=normal;
SET
openGauss=# explain select * from tt;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..5.00 rows=300 width=128)
   Iterations: 2
   ->  Partitioned Seq Scan on tt  (cost=0.00..5.00 rows=300 width=128)
         Selected Partitions:  1..2
(4 rows)

openGauss=# 

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

openGauss=# update tt set a=a+1;
UPDATE 300
openGauss=# vacuum(verbose,analyze) tt;
INFO:  vacuuming "public.tt"(dn_6001 pid=20588)
INFO:  index "ind_tt" now contains 197 row versions in 5 pages(dn_6001 pid=20588)
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:  "tt": found 0 removable, 197 nonremovable row versions in 1 out of 1 pages(dn_6001 pid=20588)
DETAIL:  99 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.tt"(dn_6001 pid=20588)
INFO:  index "ind_tt" now contains 403 row versions in 5 pages(dn_6001 pid=20588)
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:  "tt": found 0 removable, 403 nonremovable row versions in 2 out of 2 pages(dn_6001 pid=20588)
DETAIL:  201 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 "ind_tt" to remove 0.000000 invisible rows(dn_6001 pid=20588)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.tt"(dn_6001 pid=20588)
INFO:  ANALYZE INFO : "tt": scanned 1 of 1 pages, containing 98 live rows and 99 dead rows; 98 rows in sample, 98 estimated total rows(dn_6001 pid=20588)
INFO:  ANALYZE INFO : "tt": scanned 2 of 2 pages, containing 202 live rows and 201 dead rows; 202 rows in sample, 202 estimated total rows(dn_6001 pid=20588)
VACUUM

5.清理数据

openGauss=# drop table tt;
DROP TABLE
openGauss=#