PostgreSQL -- 性能优化的几个小tip

一、回收磁盘空间

在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作。现在新版PostgreSQL是自动执行VACUUM的

创建测试数据：
postgres=# create table arr (id serial, value int, age int)  #创建测试表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer;  #插入100W测试数据
postgres=# select pg_relation_size('arr');   #查看表大小
 pg_relation_size 
------------------
         44285952
(1 row)
postgres=# delete from arr where id<300000;  #删除299999条数据
DELETE 299999
postgres=# select pg_relation_size('arr');   #再次查看表大小，没有变化
pg_relation_size 
------------------
         44285952
(1 row)
postgres=# vacuum full arr;    #vacuum表，再次查看表大小，明显变小了
VACUUM
postgres=# select pg_relation_size('arr');
 pg_relation_size 
------------------
         30998528
(1 row)
postgres=# update arr set age=10000 where id>=300000 and id<600000;   #更新30W条数据
UPDATE 300000
postgres=# select pg_relation_size('arr');  #查看表大小，明显再次增大
 pg_relation_size 
------------------
         44285952
(1 row)           

二、重建索引

postgres=# REINDEX INDEX testtable_idx;           

三、重新收集统计信息

如：
ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
注意：该值的取值范围是0--1000，其中值越低采样比例就越低，分析结果的准确性也就越低，但是ANALYZE命令执行的速度却更快。如果将该值设置为-1，那么该字段的采样比率将恢复到系统当前默认的采样值，我们可以通过下面的命令获取当前系统的缺省采样值。
postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
    从上面的结果可以看出，该数据库的缺省采样值为100(10%)。