????????VACUUM??ANALYZE???????
#1. ??????????????
postgres=# CREATE TABLE testtable (i integer);
CREATE TABLE
#2. ???????????????
postgres=# CREATE INDEX testtable_idx ON testtable(i);
CREATE INDEX
#3. ?????????????????????????
postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$
DECLARE
min integer;
max integer;
BEGIN
SELECT COUNT(*) INTO min from testtable;
max := min + 10000;
FOR i IN min..max LOOP
INSERT INTO testtable VALUES(i);
END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
#4. ??????????????????(??????)
postgres=# SELECT test_insert();
test_insert
-------------
0
(1 row)
#5. ???????????????????
postgres=# SELECT COUNT(*) FROM testtable;
count
-------
40004
(1 row)
#6. ??????????????й???????????????μ?PostgreSQL???????
postgres=# ANALYZE testtable;
ANALYZE
#7. ??????????????????????????(??????????8k)??
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
#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();
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';
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)