ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
 

    postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
 

    #1. 創(chuàng)建測(cè)試數(shù)據(jù)表。
    postgres=# CREATE TABLE testtable (i integer);
    CREATE TABLE
    #2. 為測(cè)試表創(chuàng)建索引。
    postgres=# CREATE INDEX testtable_idx ON testtable(i);
    CREATE INDEX
    #3. 創(chuàng)建批量插入測(cè)試數(shù)據(jù)的函數(shù)。
    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. 批量插入數(shù)據(jù)到測(cè)試表(執(zhí)行四次)
    postgres=# SELECT test_insert();
     test_insert
    -------------
               0
    (1 row)
    #5. 確認(rèn)四次批量插入都成功。
    postgres=# SELECT COUNT(*) FROM testtable;
     count
    -------
     40004
    (1 row)
    #6. 分析測(cè)試表，以便有關(guān)該表的統(tǒng)計(jì)信息被更新到PostgreSQL的系統(tǒng)表。
    postgres=# ANALYZE testtable;
    ANALYZE
    #7. 查看測(cè)試表和索引當(dāng)前占用的頁(yè)面數(shù)量(通常每個(gè)頁(yè)面為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. 批量刪除數(shù)據(jù)。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 30003
    #9. 執(zhí)行vacuum和analyze，以便更新系統(tǒng)表，同時(shí)為該表和索引記錄高水標(biāo)記。
    #10. 這里需要額外說(shuō)明的是，上面刪除的數(shù)據(jù)均位于數(shù)據(jù)表的前部，如果刪除的是末尾部分，
    #      如where i > 10000，那么在執(zhí)行VACUUM ANALYZE的時(shí)候，數(shù)據(jù)表將會(huì)被物理的縮小。
    postgres=# VACUUM ANALYZE testtable;
    ANALYZE
    #11. 查看測(cè)試表和索引在刪除后，再通過(guò)VACUUM ANALYZE更新系統(tǒng)統(tǒng)計(jì)信息后的結(jié)果(保持不變)。
    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. 再重新批量插入兩次，之后在分析該表以更新其統(tǒng)計(jì)信息。
    postgres=# SELECT test_insert(); --執(zhí)行兩次。
     test_insert
    -------------
               0
    (1 row)
    postgres=# ANALYZE testtable;
    ANALYZE
    #13. 此時(shí)可以看到數(shù)據(jù)表中的頁(yè)面數(shù)量仍然為之前的高水標(biāo)記數(shù)量，索引頁(yè)面數(shù)量的增加
    #      是和其內(nèi)部實(shí)現(xiàn)方式有關(guān)，但是在后面的插入中，索引所占的頁(yè)面數(shù)量就不會(huì)繼續(xù)增加。
    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. 可以看到索引的頁(yè)面數(shù)量確實(shí)沒(méi)有繼續(xù)增加。
    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. 重新批量刪除數(shù)據(jù)。
    postgres=# DELETE FROM testtable WHERE i < 30000;
    DELETE 19996
    #16. 從后面的查詢可以看出，在執(zhí)行VACUUM FULL命令之后，測(cè)試表和索引所占用的頁(yè)面數(shù)量
    #      確實(shí)降低了，說(shuō)明它們占用的物理空間已經(jīng)縮小了。
    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)

    postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';
        relname     | size
    ----------------+------
     testtable_idx | 1240K
    (1 row)
    #2. 刪除數(shù)據(jù)表中大多數(shù)的數(shù)據(jù)。
    postgres=# DELETE FROM testtable WHERE i > 20000;
    DELETE 50006
    #3. 分析一個(gè)該表，以便于后面的SQL語(yǔ)句繼續(xù)查看該索引占用的空間。
    postgres=# ANALYZE testtable;
    ANALYZE
    #4. 從該查詢結(jié)果可以看出，該索引所占用的空間并未減少，而是和之前的完全一樣。
    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     1240K
    (1 row)
    #5. 重建索引。
    postgres=# REINDEX INDEX testtable_idx;
    REINDEX
    #6. 查看重建后的索引實(shí)際占用的空間，從結(jié)果中可以看出索引的尺寸已經(jīng)減少。
    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;
     size
    ------
     368K
    (1 row)
    #7. 最后一點(diǎn)需要記住的是，在索引重建后一定要分析數(shù)據(jù)表。
    postgres=# ANALYZE testtable;
    ANALYZE

    #relpages只能被VACUUM、ANALYZE和幾個(gè)DDL命令更新，如CREATE INDEX。通常一個(gè)頁(yè)面的長(zhǎng)度為8K字節(jié)。
    postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';
     relfilenode | relpages
    -------------+----------
           16412 |       79
    (1 row)
   

    postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i
        WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid
        ORDER BY c2.relname;
        relname    | relpages
    ---------------+----------
     testtable_idx |       46
    (1 row)