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

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

omm=# create table update_table
omm-# (
omm(#         c1 int,
omm(#         c2 CHAR(10)
omm(# )
omm-# partition by range (c1)
omm-# (
omm(#         partition update_table_p0 values less than (50),
omm(#         partition update_table_p1 values less than (100),
omm(#         partition update_table_p2 values less than (150),
omm(# 
ABORT                      COMMIT                     END                        MOVE                       ROLLBACK                   UNLISTEN
ALTER                      COPY                       EXECUTE                    NOTIFY                     SAVEPOINT                  UPDATE
ANALYZE                    CREATE                     EXPLAIN                    PREPARE                    SECURITY LABEL             VACUUM
BEGIN                      DEALLOCATE                 FETCH                      REASSIGN                   SELECT                     VALUES
CALL                       DECLARE                    GRANT                      REFRESH MATERIALIZED VIEW  SET                        WITH
CHECKPOINT                 DELETE FROM                INSERT                     REINDEX                    SHOW                       
CLOSE                      DISCARD                    LISTEN                     RELEASE                    START                      
CLUSTER                    DO                         LOAD                       RESET                      TABLE                      
COMMENT                    DROP                       LOCK                       REVOKE                     TRUNCATE                   
omm(# partition update_table_p3 values less than (1100)
);omm(# 
CREATE TABLE
omm=# 
omm=# insert into update_table values(generate_series(1, 1000), generate_series(1, 1000));
INSERT 0 1000

2.收集表统计信息

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

omm=# analyze VERBOSE update_table;
INFO:  analyzing "public.update_table"(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 49 live rows and 0 dead rows; 49 rows in sample, 49 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 5 of 5 pages, containing 851 live rows and 0 dead rows; 851 rows in sample, 851 estimated total rows(gaussdb pid=1)
ANALYZE
omm=# select relname, relpages, reltuples from pg_class where relname = 'update_table';


omm=#    relname    | relpages | reltuples 
--------------+----------+-----------
 update_table |        8 |      1000
(1 row)omm=# 

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

omm=# SET explain_perf_mode=normal;
SET
omm=# EXPLAIN SELECT * FROM update_table;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Partition Iterator  (cost=0.00..18.00 rows=1000 width=15)
   Iterations: 4
   ->  Partitioned Seq Scan on update_table  (cost=0.00..18.00 rows=1000 width=15)
         Selected Partitions:  1..4
(4 rows)

omm=# create index update_table_idx on update_table(c1);
CREATE INDEX
omm=# EXPLAIN SELECT * FROM update_table WHERE c1<150;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Index Scan using update_table_idx on update_table  (cost=0.00..10.88 rows=150 width=15)
   Index Cond: (c1 < 150)
(2 rows)

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

omm=# update update_table set c2 = c1 + 1 where c1 <100;
UPDATE 99
omm=# VACUUM (VERBOSE, ANALYZE) update_table;
INFO:  vacuuming "public.update_table"(gaussdb pid=1)
INFO:  index "update_table_idx" now contains 98 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:  "update_table": found 0 removable, 98 nonremovable row versions in 1 out of 1 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.update_table"(gaussdb pid=1)
INFO:  index "update_table_idx" now contains 100 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:  "update_table": found 0 removable, 100 nonremovable row versions in 1 out of 1 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.update_table"(gaussdb pid=1)
INFO:  index "update_table_idx" now contains 50 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:  "update_table": found 0 removable, 50 nonremovable row versions in 1 out of 1 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 "public.update_table"(gaussdb pid=1)
INFO:  index "update_table_idx" now contains 851 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:  "update_table": found 0 removable, 851 nonremovable row versions in 5 out of 5 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 "update_table_idx" to remove 0.000000 invisible rows(gaussdb pid=1)
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.update_table"(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 49 live rows and 49 dead rows; 49 rows in sample, 49 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 50 live rows and 50 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "update_table": scanned 5 of 5 pages, containing 851 live rows and 0 dead rows; 851 rows in sample, 851 estimated total rows(gaussdb pid=1)
VACUUM

5.清理数据

omm=# drop table update_table;
DROP TABLE
omm=#