课后作业

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

create table test1.t_par
(
        id int,
        name varchar(20)
)
partition by range (id)
(
        partition p0 values less than (50),
        partition p1 values less than (100),
        partition p2 values less than (150),
	partition p3 values less than (200),
        partition p4 values less than (maxvalue)
);
insert into test1.t_par values(generate_series(1, 10000));

2.收集表统计信息

--查看系统表中表的统计信息
select relname, relpages, reltuples from pg_class where relname = 't_par';
--使用ANALYZE VERBOSE语句更新统计信息，并输出表的相关信息
analyze VERBOSE test1.t_par;
--查看系统表中表的统计信息
select relname, relpages, reltuples from pg_class where relname = 't_par';

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

EXPLAIN SELECT * FROM test1.t_par;

create index idx_t_par on test1.t_par(id);
EXPLAIN SELECT * FROM test1.t_par WHERE id<100;

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

update test1.t_par set id = id + 1 where id <100;
VACUUM (VERBOSE, ANALYZE) test1.t_par;

5.清理数据

drop schema test1 cascade;