#opengauss的第19天
1.创建分区表，并用generate_series(1,N)函数对表插入数据
omm=# create table t1(id bigint,name char(40),crt_time timestamp without time zone) PARTITION BY RANGE(id)
omm-# (
omm(# PARTITION P1 VALUES LESS THAN(100),
omm(# PARTITION P2 VALUES LESS THAN(200),
omm(# PARTITION P3 VALUES LESS THAN(300),
omm(# PARTITION P4 VALUES LESS THAN(400),
omm(# PARTITION P5 VALUES LESS THAN(500),
omm(# PARTITION P6 VALUES LESS THAN(600),
omm(# PARTITION P7 VALUES LESS THAN(700),
omm(# PARTITION P8 VALUES LESS THAN(800),
omm(# PARTITION P9 VALUES LESS THAN(900),
omm(# PARTITION P10 VALUES LESS THAN(maxvalue)
omm(# );
CREATE TABLE
omm=# insert into t1 select id ,md5(random()::text),date_trunc(‘second’,(now()::timestamp without time zone+random()*100::interval)) from generate_series(1,1000) as id;
INSERT 0 1000
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 2 of 2 pages, containing 99 live rows and 0 dead rows; 99 rows in sample, 99 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 101 live rows and 0 dead rows; 101 rows in sample, 101 estimated total rows(gaussdb pid=1)
ANALYZE
omm=#
select relname, relpages, reltuples from pg_class where relname = ‘t1’;
relname | relpages | reltuples
---------±---------±----------
t1 | 20 | 1000
3.显示简单查询的执行计划；建立索引并显示有索引条件的执行计划
omm=# \d t1
Table “public.t1”
Column | Type | Modifiers
----------±----------------------------±----------
id | bigint |
name | character(40) |
crt_time | timestamp without time zone |
Range partition by(id)
Number of partition: 10 (View pg_partition to check each partition range.)
omm=#
omm=# explain select * from t1;
-> Partitioned Seq Scan on t1 (cost=0.00…30.00 rows=1000 width=57)
Selected Partitions: 1…10
(4 rows)
omm=# QUERY PLAN
Partition Iterator (cost=0.00…30.00 rows=1000 width=57)
Iterations: 10
omm=# explain select * from t1 where id=999;
-> Partitioned Seq Scan on t1 (cost=0.00…14.50 rows=1 width=57)
Filter: (id = 999)
Selected Partitions: 10
(5 rows)
omm=# QUERY PLAN
Partition Iterator (cost=0.00…14.50 rows=1 width=57)
Iterations: 1
omm=# create index idx_id on t1(id);
CREATE INDEX
omm=# explain select * from t1 where id=999;
omm=# QUERY PLAN
Index Scan using idx_id on t1 (cost=0.00…8.27 rows=1 width=57)
Index Cond: (id = 999)
4.更新表数据，并做垃圾收集
omm=# update t1 set id=10 where id%2=0;
UPDATE 500
omm=# VACUUM (VERBOSE, ANALYZE) t1;
INFO: vacuuming “public.t1”(gaussdb pid=1)
INFO: index “idx_id” now contains 599 row versions in 9 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 0 removable, 599 nonremovable row versions in 7 out of 7 pages(gaussdb pid=1)
DETAIL: 49 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 100 row versions in 9 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 0 removable, 100 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 50 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: index “idx_id” now contains 101 row versions in 9 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 0 removable, 101 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
DETAIL: 51 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_id” to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing “public.t1”(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 7 of 7 pages, containing 550 live rows and 49 dead rows; 550 rows in sample, 550 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : “t1”: scanned 2 of 2 pages, containing 50 live rows and 51 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
VACUUM
5.清理数据
omm=# CHECKPOINT;
CHECKPOINT
omm=# drop table t1;
DROP TABLE
omm=# \d
No relations found.
