openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint 课后作业

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

omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.ttt1
omm-# (
omm(# ca_address_sk integer NOT NULL ,
omm(# ca_address_id character(16),
omm(# ca_street_number character(10) ,
omm(# ca_street_name character varying(60) ,
omm(# ca_street_type character(15) ,
omm(# ca_suite_number character(10) ,
omm(# ca_city character varying(60) ,
omm(# ca_county character varying(30) ,
omm(# ca_state character(2) ,
omm(# ca_zip character(10) ,
omm(# ca_country character varying(20) ,
omm(# ca_gmt_offset numeric(5,2) ,
omm(# ca_location_type character(20)
omm(# );
CREATE TABLE
omm=# insert into tpcds.ttt1 values
omm-# omm-# (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson', 'Parkway', 'Suite 280', 'Fairfield', 'Maricopa County', 'AZ', '86192' ,'UStates', -7.00, 'condo'),
tes', -7.00, 'condo'),, '362', 'Washington 6th', 'RD', 'Suite 80', 'Fairview', 'Taos County', 'NM', '85709', 'United Stat
omm-# (3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington', 'Circle', 'Suite Q', 'Pleasant Valley', 'York County', 'PA', '12477', 'United States', -5.00, 'single family');
INSERT 0 3
omm=#
omm=# insert into tpcds.ttt1 values(generate_series(10, 10000));
INSERT 0 9991

2.收集表统计信息

omm=# select relname, relpages, reltuples from pg_class where relname = 'ttt1';
omm=# relname | relpages | reltuples
---------+----------+-----------
ttt1 | 0 | 0
(1 row)

analyze VERBOSE tpcds.ttt1;
INFO: analyzing "tpcds.ttt1"(gaussdb pid=1)
INFO: ANALYZE INFO : "ttt1": scanned 55 of 55 pages, containing 9994 live rows and 0 dead rows; 9994 rows in sample, 9994 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# select relname, relpages, reltuples from pg_class where relname = 'ttt1';
relname | relpages | reltuples
---------+----------+-----------
ttt1 | 55 | 9994
(1 row)

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

omm=#
omm=# EXPLAIN SELECT * FROM tpcds.ttt1;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on ttt1 (cost=0.00..154.94 rows=9994 width=151)
(1 row)

omm=# SET explain_perf_mode=normal;
SET
omm=# EXPLAIN SELECT * FROM tpcds.ttt1;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on ttt1 (cost=0.00..154.94 rows=9994 width=151)
(1 row)

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

omm=# create index idx_ttt1 on tpcds.ttt1(ca_address_sk);
CREATE INDEX
omm=# EXPLAIN SELECT * FROM tpcds.ttt1 WHERE ca_address_sk<100;
QUERY PLAN
------------------------------------------------------------------------
[Bypass]
Index Scan using idx_ttt1 on ttt1 (cost=0.00..9.90 rows=94 width=151)
Index Cond: (ca_address_sk < 100)
(3 rows)

omm=#
omm=# update tpcds.ttt1 set ca_address_sk = ca_address_sk + 1 where ca_address_sk <100;
UPDATE 93
omm=# VACUUM (VERBOSE, ANALYZE) tpcds.ttt1;
INFO: vacuuming "tpcds.ttt1"(gaussdb pid=1)
INFO: index "idx_ttt1" now contains 10087 row versions in 31 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: "ttt1": found 0 removable, 10087 nonremovable row versions in 55 out of 55 pages(gaussdb pid=1)
DETAIL: 93 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 "tpcds.ttt1"(gaussdb pid=1)
INFO: ANALYZE INFO : "ttt1": scanned 55 of 55 pages, containing 9994 live rows and 93 dead rows; 9994 rows in sample, 9994 estimated total rows(gaussdb pid=1)
VACUUM

5.清理数据

omm=# drop schema tpcds cascade;
NOTICE: drop cascades to table tpcds.ttt1
DROP SCHEMA