课后作业
1.创建分区表，并用generate_series(1,N)函数对表插入数据
omm=# create schema tao;
CREATE SCHEMA
omm=# create table tao.store
omm(# c1 int,
omm(# c2 CHAR(2),
omm(# c3 name
omm-# (
partition by range (c1)
omm-# omm(# )
omm-# (
omm(# partition p1 values less than (300),
omm(# partition p2 values less than (600),
omm(# partition p3 values less than (maxvalue)
omm(# );
CREATE TABLE
omm=# insert into tao.store values(1,'ti','12'),(2,'pi','15'),(3,'ki','17'),(4,'lo','18'),(5,'bi','19');
INSERT 0 5
omm=# insert into tao.store values(generate_series(6, 1000));
INSERT 0 995
omm=# select relname, relpages, reltuples from pg_class where relname = 'store';
omm=# relname | relpages | reltuples
---------+----------+-----------
store | 0 | 0
(1 row)
2.收集表统计信息
omm=# analyze verbose tao.store ;
INFO: analyzing "tao.store"(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 299 live rows and 0 dead rows; 299 rows in sample, 299 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 300 live rows and 0 dead rows; 300 rows in sample, 300 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 401 live rows and 0 dead rows; 401 rows in sample, 401 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# select relname, relpages, reltuples from pg_class where relname = 'store';
omm=# select relname, relpages, reltuples from pg_class where relname = 'store';
relname | relpages | reltuples
---------+----------+-----------
store | 6 | 1000
(1 row)
3.显示简单查询的执行计划；建立索引并显示有索引条件的执行计划
omm=# SET explain_perf_mode=normal;
SET
omm=# explain select * from tao.store where c1=886;
QUERY PLAN
-------------------------------------------------------------------------
Partition Iterator (cost=0.00..14.50 rows=1 width=71)
Iterations: 1
-> Partitioned Seq Scan on store (cost=0.00..14.50 rows=1 width=71)
Filter: (c1 = 886)
Selected Partitions: 3
(5 rows)
omm=# create index st_c1_idx on tao.store(c1);
CREATE INDEX
omm=# explain select * from tao.store where c1=886;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using st_c1_idx on store (cost=0.00..8.27 rows=1 width=71)
Index Cond: (c1 = 886)
(2 rows)
4.更新表数据，并做垃圾收集
omm=# update tao.store set c1=c1+1 where c1<100;
UPDATE 99
omm=# vacuum (verbose, analyze) tao.store;
INFO: vacuuming "tao.store"(gaussdb pid=1)
INFO: index "st_c1_idx" now contains 398 row versions in 6 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: "store": found 0 removable, 398 nonremovable row versions in 2 out of 2 pages(gaussdb pid=1)
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 "tao.store"(gaussdb pid=1)
INFO: index "st_c1_idx" now contains 300 row versions in 6 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: "store": found 0 removable, 300 nonremovable row versions in 2 out of 2 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: vacuuming "tao.store"(gaussdb pid=1)
INFO: index "st_c1_idx" now contains 401 row versions in 6 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: "store": found 0 removable, 401 nonremovable row versions in 2 out of 2 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: scanned index "st_c1_idx" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "tao.store"(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 299 live rows and 99 dead rows; 299 rows in sample, 299 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 300 live rows and 0 dead rows; 300 rows in sample, 300 estimated total rows(gaussdb pid=1)
INFO: ANALYZE INFO : "store": scanned 2 of 2 pages, containing 401 live rows and 0 dead rows; 401 rows in sample, 401 estimated total rows(gaussdb pid=1)
VACUUM
5.清理数据
omm=# drop schema tao cascade;
NOTICE: drop cascades to table tao.store
DROP SCHEMA
