index 的管理與維護

index 是資料庫系統相當重要的功能，好的 index 可以增加 Query 的效率。

這裡會記錄一些與 index 管理與維護有關的內容

查看 index size 與使用的狀況

可以透過這個 SQL 顯示資料庫內 index size 與使用的狀況

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

Index Maintenance - PostgreSQL wiki (opens new window)

清除 index 使用狀況的統計資料

如果想要重設 index 的使用狀況，可以用以下的 SQL 重設統計資料

select pg_stat_reset();

PostgreSQL: Documentation: 10: 28.2. The Statistics Collector (opens new window)