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

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

CREATE TABLE store
(
c1 int,
c2 CHAR(2)
)
PARTITION BY RANGE (c1)
(
PARTITION store_p0 VALUES LESS THAN (20000),
PARTITION store_p1 VALUES LESS THAN (40000),
PARTITION store_p2 VALUES LESS THAN (60000),
PARTITION store_p3 VALUES LESS THAN (80000),
PARTITION store_p4 VALUES LESS THAN (100000)
);

insert into store (c1) values(generate_series(1, 99999));

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_index1 ON store USING BTREE(c1);
--显示有索引条件的执行计划
EXPLAIN SELECT * FROM store;

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

UPDATE store set c2='aa' where c1=10;

DELETE FROM store where c1<10000;
SELECT pg_size_pretty(pg_relation_size('store'));

VACUUM FULL store;
SELECT pg_size_pretty(pg_relation_size('store'));

5. 清理数据

DROP INDEX store_index1;
DROP TABLE store;