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

自己安装的openGauss环境
启动openGauss
gs_ctl -D /gauss/data/db1/ start
登录openGauss
gsql -d postgres -p 26000 -r

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

create schema hc;
create table hc.products
(product_id INTEGER,
product_name Char(30)
)
partition by range (product_id)
(partition p1 values less than (50),
PARTITION p2 VALUES LESS THAN (100),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
insert into hc.products(product_id,product_name) values(1,‘database’),(2,‘schema’),(3,‘relation’);

insert into hc.products values(generate_series(10, 1000));

2.收集表统计信息

select relname, relpages, reltuples from pg_class where relname = ‘products’;
analyze VERBOSE hc.products;
select relname, relpages, reltuples from pg_class where relname = ‘products’;

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

SET explain_perf_mode=normal;
explain select * from hc.products;
create index p_id_idx on hc.products(product_id);
explain select * from hc.products where product_id=2;

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

update hc.products set product_name=‘postgresql’ where product_id=1;
delete from hc.products where product_id=3;
select * from hc.products;
VACUUM (VERBOSE, ANALYZE) hc.products;

5.清理数据

drop schema hc cascade;