学习心得

openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint
``
EXPLAIN
relname, relpages, reltuples from pg_class
VACCUMM
ANALYZE

0.进入系统

su - omm
gsql -r

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

Create schema s1;
CREATE TABLE s1.prod_t
(
id integer NOT NULL ,
name character(16),
category character(20)
);
insert into s1.prod_t values(generate_series(1, 100));
SELECT * FROM s1.prod_t;

• 回显

 id  | name | category
-----+------+----------
   1 |      |
   2 |      |
   3 |      |
   4 |      |
   5 |      |
   6 |      |
   7 |      |
   8 |      |
   9 |      |
...  | ...  | ...
   100 |      |
(100 rows)

2.收集表统计信息

select relname, relpages, reltuples from pg_class where relname = 'prod_t';

• 回显

 relname | relpages | reltuples
---------+----------+-----------
 prod_t  |        0 |         0
(1 row)

analyze VERBOSE s1.prod_t;

• 回显

INFO:  analyzing "s1.prod_t"(gaussdb pid=1)
INFO:  ANALYZE INFO : "prod_t": scanned 1 of 1 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
ANALYZE

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

SET explain_perf_mode=normal;
EXPLAIN SELECT * FROM s1.prod_t;

• 回显

SET
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on prod_t  (cost=0.00..2.00 rows=100 width=156)
(1 row)

create index prod_idx on s1.prod_t(id);
EXPLAIN SELECT * FROM s1.prod_t WHERE id < 100;

• 回显

CREATE INDEX
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on prod_t  (cost=0.00..2.25 rows=100 width=156)
   Filter: (id < 100)
(2 rows)

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

update s1.prod_t set id = id + 1 where id < 90;
VACUUM (VERBOSE, ANALYZE) s1.prod_t;

• 回显

UPDATE 88
INFO:  vacuuming "s1.prod_t"(gaussdb pid=1)
INFO:  scanned index "prod_idx" to remove 99 row versions(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "prod_idx" now contains 188 row versions in 2 pages(gaussdb pid=1)
DETAIL:  99 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:  "prod_t": found 99 removable, 188 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL:  88 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 "s1.prod_t"(gaussdb pid=1)
INFO:  ANALYZE INFO : "prod_t": scanned 2 of 2 pages, containing 100 live rows and 88 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
VACUUM

5.清理数据

drop table s1.prod_t;

• 回显

DROP TABLE