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

openGauss每日一练第19天课后作业

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

create table store (

prod_id int not null,

price numeric(10,2),

amount int)

partition by range (prod_id)

(   partition store_p1 values less than (1000),

    partition store_p2 values less than (2000),

    partition store_p3 values less than (3000),

    partition store_p4 values less than (4000),

    partition store_p5 values less than (maxvalue));

insert into store values

(56,1300.99,15),

(1201,98.88,12),

(2340,9.99,34),

(4928,5.36,6),

(3870,6431.99,3);

insert into store values(generate_series(10, 5000));

2.收集表统计信息

select relname, relpages, reltuples from pg_class where relname = 'store';

analyze VERBOSE store;

select relname, relpages, reltuples from pg_class where relname = 'store';

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

SET explain_perf_mode=normal;

EXPLAIN SELECT * FROM store;

create index store_idx on store(prod_id);

EXPLAIN SELECT * FROM store WHERE prod_id<1000;

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

update store set prod_id = prod_id +1 where prod_id <2000;

VACUUM (VERBOSE, ANALYZE) store;

5.清理数据

drop table store cascade;