Index Maintenance/ja

原文最終更新日：29 October 2014

とある日に、特に十分積極的にVACUUMを使用していない場合に、多分データベース上で定常的なインデックスの再作成を使って上手く処理する必要があるでしょう。この領域での特に手軽なコマンドはCLUSTERで、クリーンナップの違った種類の手助けとなります。

バージョン8.4以前においてVACUUM FULLは使用しないでください。

インデックスのまとめ

これは、行数、インデックスの数、そしてそれらのそれぞれのテーブルについてのいくつかの情報を取得するサンプルクエリです。（バージョン8.3でのみ動作します；より古いバージョンで使用している場合は、pg_size_prettyを破棄してください）

Performance Snippets

Index summary

Works with PostgreSQL

>=8.1

Written in

SQL

Depends on

Nothing

SELECT
    pg_class.relname,
    pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
    pg_class.reltuples AS num_rows,
    count(indexname) AS number_of_indexes,
    CASE WHEN x.is_unique = 1 THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    SUM(case WHEN number_of_columns = 1 THEN 1
              ELSE 0
            END) AS single_column,
    SUM(case WHEN number_of_columns IS NULL THEN 0
             WHEN number_of_columns = 1 THEN 0
             ELSE 1
           END) AS multi_column
FROM pg_namespace 
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
       (SELECT indrelid,
           max(CAST(indisunique AS integer)) AS is_unique
       FROM pg_index
       GROUP BY indrelid) x
       ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
    AS foo
    ON pg_class.relname = foo.ctablename
WHERE 
     pg_namespace.nspname='public'
AND  pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;

インデックスサイズ／使用量の統計情報

インデックスが走査されたことに沿ったテーブルとインデックスのサイズとどれくらいタプルがフェッチされたを示します。テーブルとインデックス両方のサイズが含まれているもう一つのビューはDisk Usageを参照してください。

Performance Snippets

Index statistics

Works with PostgreSQL

>=8.1

Written in

SQL

Depends on

Nothing

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 )
    as foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
order by 1,2;

インデックスの複製

カラムの同一のセット、演算子クラス、評価式と述語を持った複合インデックスを見つけれられます -- それらを同等にします。普通それらの一つを削除することは安全ですが保証はしません。:)

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) as size,
       (array_agg(idx))[1] as idx1, (array_agg(idx))[2] as idx2,
       (array_agg(idx))[3] as idx3, (array_agg(idx))[4] as idx4
FROM (
    SELECT indexrelid::regclass as idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||
                                         coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) as key
    FROM pg_index) sub
GROUP BY key HAVING count(*)>1
ORDER BY sum(pg_relation_size(idx)) DESC;

インデックスの肥大化

check_postgresを基礎にするもの

REINDEXの一般的なニーズの一つは削除領域がまばらに分布もしくは（バージョン9.0以前の）VACUUM FULLの使用によってインデックスが肥大化し始めるときです。テーブル内の肥大化の量のための予測ツールがcheck_postgresに含まれており、ディレクトリを呼び出すもしくは、より大きな監視システムへ組み入れることができます。 スクリプトはこのコードを基礎としおり、そして／もしくは以下のほかのソースのコンセプトを基礎としています：

• bloat view (Dimitri Fontaine)（訳注：英語サイト）
• Visualizing Postgres - index_byte_sizes view (Michael Glaesemann, myYearbook)（訳注：英語サイト）
• OmniTI Tasty Treats for PostgreSQL - shell and Perl pg_bloat_report scripts（訳注：英語サイト）

新しいクエリ

Btreeインデックスためのより良い肥大化の予測のためにあらたなクエリが作成されました。check_postgresクエリとは似ておらず、Bteeインデックスのディスクレイアウトにのみフォーカスしています。

この話題についてのアーティクル（訳注：英語サイト）を参照してください。

監視スクリプトであるcheck_pgactivityは、この作業を基礎にした監視を含んでいます。

使われていないインデックス

Since indexes add significant overhead to any table change operation, they should be removed if they are not being used for either queries or constraint enforcement (such as making sure a value is unique). How to find such indexes:

なんらかのテーブル上の重要なインデックスが追加されてから操作を変更し、クエリもしくは（値がユニークであるといった）制約条件が使用されないようであればそれらのインデックスは消去するべきです。そんなインデックスの探し方は以下が参考になります：

• インデックスの除去テクニック（訳注：リンク切れしています）
• 使われていないインデックスを探す（訳注：英語サイト）
• 使用されないインデックスの探し方（訳注：英語サイト）
• '使われていないインデックスの監視' by Johnny Morano（訳注：英語サイト）

参考資料

• インデックス統計情報クエリ"SQLアプリケーションのリファクタリング"レビュー（訳注：リンク切れしています）