openGauss每日一练第19天 | 学习心得体会

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

create table student(id int,name char(20) default 'unknown') 
partition by range(id)
(
partition sutdent_p1 values less than (200),
partition sutdent_p2 values less than (400),
partition sutdent_p3 values less than (600),
partition sutdent_p4 values less than (800)
);
insert into student values(generate_series(1,700))

2.收集表统计信息

analyze verbose student ;
INFO:  analyzing "public.student"(gaussdb pid=1)
INFO:  ANALYZE INFO : "student": scanned 2 of 2 pages, containing 199 live rows and 0 dead rows; 199 rows in sample, 199 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "student": scanned 2 of 2 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "student": scanned 2 of 2 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows(gaussdb pid=1)
INFO:  ANALYZE INFO : "student": scanned 1 of 1 pages, containing 101 live rows and 0 dead rows; 101 rows in sample, 101 estimated total rows(gaussdb pid=1)
ANALYZE

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

omm=# explain  select * from student order by id limit 20;
 Limit  (cost=32.63..32.68 rows=20 width=25)
   ->  Sort  (cost=32.63..34.38 rows=700 width=25)
         Sort Key: id
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
(7 rows)

omm=#          ->  Partition Iterator  (cost=0.00..14.00 rows=700 width=25)
               Iterations: 4
               ->  Partitioned Seq Scan on student  (cost=0.00..14.00 rows=700 width=25)
                     Selected Partitions:  1..4

create index sutdent_id_idx on student(id) local;

omm=# explain  select * from student order by id limit 20;
         Iterations: 4
         ->  Partitioned Index Scan using sutdent_id_idx on student  (cost=0.00..52.75 rows=700 w
idth=25)
               Selected Partitions:  1..4
(5 rows)

                                                QUERY PLAN                                       
         
-------------------------------------------------------------------------------------------------
---------
 Limit  (cost=0.00..1.51 rows=20 width=25)
   ->  Partition Iterator  (cost=0.00..52.75 rows=700 width=25)

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

delete from student where id>600;
vacuum (verbose,analyze) student;

5.清理数据

drop table student;