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

一、学习目标

学习收集统计信息、执行计划、垃圾收集和checkpoint

二、课后作业

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

create schema charley;
create table charley.store(num  int,name CHAR(16),score int) partition by range (num)
(
        partition store_p1 values less than (10),
        partition store_p2 values less than (20),
        partition store_p3 values less than (30),
        partition store_p4 values less than (40),
        partition store_p5 values less than (50)
);

2.收集表统计信息

select relname, relpages, reltuples from pg_class where relname = 'store';
analyze VERBOSE charley.store;
select relname, relpages, reltuples from pg_class where relname = 'store';

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

SET explain_perf_mode=normal;
EXPLAIN SELECT * FROM charley.store;
create index store_idx on charley.store(num);
EXPLAIN SELECT * FROM charley.store WHERE num<40;

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

update charley.store set num = num + 1 where num < 40;
VACUUM (VERBOSE, ANALYZE) charley.store;

5.清理数据

checkpoint;
drop schema charley cascade;

三、学习心得

每天学习学习一点，进步一点点。