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

学习目标
学习openGauss收集统计信息、打印执行计划、垃圾收集和checkpoint

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

create table store
(
        c1 int,
        c2 CHAR(2)
)
partition by range (c1)
(
        partition store_p0 values less than (1000),
        partition store_p1 values less than (2000),
        partition store_p3 values less than (3000),
        partition store_p4 values less than (4000),
        partition store_p5 values less than (5000)
);
insert into store values (1, 'a');

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

2.收集表统计信息

–查看系统表中表的统计信息

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

—使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息

analyze VERBOSE store;

–查看系统表中表的统计信息

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

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

–使用默认的打印格式

SET explain_perf_mode=normal;

–显示表简单查询的执行计划

EXPLAIN SELECT * FROM store;

–以JSON格式输出的执行计划（explain_perf_mode为normal时）

EXPLAIN(FORMAT JSON) SELECT * FROM store;

–有索引条件的执行计划

create index store_idx on store(c1);
EXPLAIN SELECT * FROM store WHERE c1<10;

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

update store set c1 = c1 + 1 where c1 <10;
VACUUM (VERBOSE, ANALYZE) store;

5.清理数据

drop table store;