検索時には大変お世話になっているインデックス。 使われなくても文句も言わずに更新され続けるインデックス。 ずっと放置して運用しているといつの間にか肥大化しているインデックス。 今回は、そんなインデックスを再構築する際に気をつけることをまとめてみました。 まずは、インデックスを作成、または再作成するやり方について簡単に。 CREATE INDEX 普通にインデックスを作成。 他のトランザクションはテーブルを読み取ることはできるが、対象テーブル内の行を挿入、更新、削除しようとすると、 インデックス作成が完了するまでブロックされる。 CREATE INDEX CONCURRENTLY 挿入、更新、削除をブロックしないインデックスの作成方法。 テーブルを2回スキャンする必要があり、潜在的にそのインデックスを使用する可能性がある、実行中のすべての トランザクションが終わるまで待機する必要がある。 通

以下の５ステップで，適切なインデックスを作成し，SQLを高速化できる。 （１） パフォーマンスを改善すべきSQL（もしくはカラム）を特定 （１−１） ログを閲覧し，実行秒数の大きいものを抽出する。 （１−２） 統計テーブルを閲覧し，よく利用されるテーブルを特定する。 （２） 該当SQLのプランやコストを確認 （３） 該当カラムに対してインデックスを作成 （４） インデックスが作成されたことを確認 （５） SQLのプランやコストが改善されたことを確認 補足 ※↑ もくじジェネレータ で自動生成 DBはPostgreSQLを想定。 （１）パフォーマンスを改善すべきSQL（もしくはカラム）を特定 まず，インデックスを作成すべきカラムを見極める。 その方法は２つある。 （１−１）ログを閲覧し，実行秒数の大きいものを抽出する。 SQLの実行ログを閲覧する。 たとえば，Ruby on Railsなら，

おもむろに次のようなテーブルを作ってみる。 CREATE TABLE hoge( col1 INT NOT NULL , col2 INT NOT NULL , CONSTRAINT pkey_hoge PRIMARY KEY(col1, col2) ) ; CREATE TABLE foo( col1 INT NOT NULL , col2 INT NOT NULL , col3 INT NOT NULL , col4 INT NOT NULL , CONSTRAINT pkey_foo PRIMARY KEY(col1, col4) , CONSTRAINT uniq1 UNIQUE(col1, col2) , CONSTRAINT uniq2 UNIQUE(col4, col2, col3) , CONSTRAINT ref1 FOREIGN KEY(col1, col4) REFE