PostgreSQL学习手册(数据库维护)(二)
LYZE
#7. 查看测试表和索引当前占用的页面数量(通常每个页面为8k)。
postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx'; www.2cto.com
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 90
#8. 批量删除数据。
postgres=# DELETE FROM testtable WHERE i < 30000;
DELETE 30003
#9. 执行vacuum和analyze，以便更新系统表，同时为该表和索引记录高水标记。
#10. 这里需要额外说明的是，上面删除的数据均位于数据表的前部，如果删除的是末尾部分，
# 如where i > 10000，那么在执行VACUUM ANALYZE的时候，数据表将会被物理的缩小。
postgres=# VACUUM ANALYZE testtable;
ANALYZE
#11. 查看测试表和索引在删除后，再通过VACUUM ANALYZE更新系统统计信息后的结果(保持不变)。
postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 90
(2 rows)
#12. 再重新批量插入两次，之后在分析该表以更新其统计信息。
postgres=# SELECT test_insert(); --执行两次。
test_insert
-------------
0
(1 row)
postgres=# ANALYZE testtable;
ANALYZE
#13. 此时可以看到数据表中的页面数量仍然为之前的高水标记数量，索引页面数量的增加
# 是和其内部实现方式有关，但是在后面的插入中，索引所占的页面数量就不会继续增加。
postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 173
(2 rows)
postgres=# SELECT test_insert();
-------------
0
(1 row)
postgres=# ANALYZE testtable;
ANALYZE
#14. 可以看到索引的页面数量确实没有继续增加。
postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx'; www.2cto.com
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17601 | 157
testtable_idx | 17604 | 173
(2 rows)
#15. 重新批量删除数据。
postgres=# DELETE FROM testtable WHERE i < 30000;
DELETE 19996
#16. 从后面的查询可以看出，在执行VACUUM FULL命令之后，测试表和索引所占用的页面数量
# 确实降低了，说明它们占用的物理空间已经缩小了。
postgres=# VACUUM FULL testtable;
VACUUM
postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';
relname | relfilenode | relpages
---------------+-------------+----------
testtable | 17602 | 118
testtable_idx | 17605 | 68
(2 rows)
四、定期重建索引：
在PostgreSQL中，为数据更新频繁的数据表定期重建索引(REINDEX INDEX)是非常有必要的。对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。
对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。见如下示例：
#1. 此时已经在该表中插入了大约6万条数据，下面的SQL语句将查询该索引所占用的磁盘空间。
postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx'; www.2cto.com
relname | size