PostgreSQLでインデックスを効率良く利用しよう

どうも、Benoîtです。

TECHSCORE Advent Calendar 2014 の 4 日目の投稿です。

PostgreSQLではいろいろなインデックス種類が存在する。使い方も様々である。インデックス種類の概要のあとに一番使われるB-treeインデックスの使い道や保守の話しを深めていく。

インデックスとは特定の項目を素早く参照できるようにするためのもの。B-treeインデックスに関してはソートに使うこともできる。

インデックスの種類

PostgreSQLはいろいろなインデックスに対応している。

B-treeインデックス、CREATE INDEX を利用するとデフォルトで選択されるインデックスである。Bの字はバランスの意味で、イメージとしてはtree（木構造）のroot（根）からleaf（最下層のノード）までの階層数がなるべく揃うようにバランスを取る。B-treeインデックスはどのデータ型も対応している事、NULL値も扱うことができる事、等価性だけでなく範囲や比較検索に使うことができる点が特徴である。尚、キャッシングとの相性はすごくいい。
Hashインデックスは単純な等価性比較のみを扱うことができる。しかしながら、運用でのデメリットがあることからお勧めはしない。Hashインデックスの操作はWALに記録されない、データベースがクラッシュした後にREINDEXで再構築しなければならない可能性がある。また、最初のバックアップを取得して以降、ストリーミングレプリケーションやファイルベースのレプリケーションでは変更が反映されないため、その後ハッシュインデックスを使うクエリは誤った結果を返すのだ。
Generalized Inverted Indexes（GIN、汎用転置インデックス）は一つ行へ複数の値をマッピングする時に利用する。文書や配列など複数のキーを持つ値を扱うことができる。
Generalized Search Tree（GiST、汎用検索ツリー）は単一種類のインデックスではなく、多くの異なるインデックス戦略を実装することができる基盤である。B-treeインデックスと同じようにバランスされた木構造が作られた上で、等価性と範囲以外のクエリにも対応できる。幾何データ型や全文検索に使う。

ここからはB-treeインデックスの改善を尽くす話しに集中しよう。

何故クエリは私が作ったインデックスを利用しないのか

PostgreSQLのプランナがインデックスを利用しない理由はいろいろ考えられる。理由が少し不明にみえてもプランナは基本正しく決めている。同じクエリでも条件によってインデックスを利用したりしなかったりする。例えば、 select * from foo where bar = 1の場合はインデックスを利用しても、 select * from foo where bar = 2の場合はインデックスを利用しないということもあり得る。2であるbarがすごく多い場合は index scan より sequential scan の方が速い。この場合、プランナは負荷の低い方法を選ぶ。

部分インデックス（Partial）

部分インデックスはテーブルの一部のレコードだけに適用する。WHERE句で指定した条件を満たすレコードに対してインデックスは作成される。対象のレコードを減らすことで、インデックスのストレージが小さく、保守がしやすく、速度が速くなるところが長所である。
例えば、gmail上で starred というスター付きとなるメールに対して処理をする場合は、こういうインデックスが作れる。

CREATE INDEX email_starred_created_at_index ON emails(created_at) WHERE starred IS TRUE;

1	CREATE INDEX email_starred_created_at_index ON emails(created_at) WHERE starred IS TRUE;

式インデックス（Expression）

式インデックスはとある方法、関数などで変換されたデータを検索する時に使う。PostgreSQLでは変換された値に対してインデックスを作成できるので、他のインデックスと同じくらいのパフォーマンスを発揮する。例えば、大文字・小文字が混在するメールアドレスに対して、大文字・小文字を区別せず検索したい場合、要するに WHERE lower(email) = 'email@domain' という条件を利用したい場合、こういうインデックスを作ればいい。

CREATE INDEX users_lower_email ON users(lower(email));

1	CREATE INDEX users_lower_email ON users(lower(email));

もう一つ例えば、TIMESTAMP型で保存されている日付に対してDATE型として検索したい場合は CREATE INDEX articles_day ON articles ( date(published_at) ) 　を作って、 WHERE date(articles.created_at) = date('2011-11-11') で検索できる。

ユニークインデックス（Unique）

ユニークインデックスは重複した値を許可しないものである。ユニークインデックスの長所は2つ考えれて、データの整合性とパフォーマンス。ユニークインデックスでの検索は基本速い。
アプリケーション側で値が重複しないように制限をかけても同時アクセスの場合に重複した値がデータベースに保存されてしまうことがある。重複した値を許さない場合は必ず、データベースレベルでユニークインデックスか一意制約をかけるべき。

複数列インデックス（Multi-column）

PostgreSQLでは複数列インデックスを扱えるが、それがいつ利用されるか理解することは大事。複数列インデックスがなくてもPostgreSQLのプランナは内部的にビットマップを作成して複数のインデックスを組み合わせて利用できる。どの列でもインデックスの発行が可能だし、プランナはそのインデックスを使ってくれるから、ベンチマークを行って複数列インデックスが必要だったという証明をしてから利用しよう。
複数列インデックスはクエリを最適化できる条件がある。クエリの中、参照されている列はインデックスの中にある列と同じ順番でなければならない事。それと違って、複数の単一列インデックスはその条件に制御されていない。
例えば、アプリケーションに WHERE a = x AND b = y 又は WHERE a = x の条件がよく使われるなら (a, b) 列に複数列インデックスを発行する価値があるかもしれない。但し、 WHERE b = y の場合は通用しない。※複数列インデックスを発行した上で a 列だけにインデックスを貼る事は冗長になるので不要。

B-treeインデックスのソート

デフォルトで、B-treeインデックスは昇順でソートされる。降順でソートするように定義することもできる。ブログとかだと、投稿は最新順で表示する事がよくある。投稿日時は published_at として、公開されていない投稿は published_at が NULL とする。
この場合は、こういうインデックスが作れる。

CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

1	CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

アプリケーション上で投稿が表示される順位に合わせたインデックスを作る事でパフォーマンスが上がる。インデックスにソート条件が含まれていなければ、PostgreSQLはsequential scanを行ってからソートを実施するのでしょう。
今回は空値（NULL）を最後に回したいからこのインデックスを利用する意味があった。インデックスはどの順位からも検索できるので昇順のインデックスが存在すれば降順の検索でも同じインデックスの利用ができる。複数列にバラバラな順位で、例えば ORDER BY a ASC, b DESC 、複数列インデックスにも利用される。

インデックスの保守

PostgreSQLでインデックスは全ての情報を持ちはしない。インデックスが利用されて一致する行が見つかってもPostgreSQLはディスクに情報を取得するのだ。更に、情報の可視性もインデックスになくて、PostgreSQLはディスクに情報を取得する必要がでる。

※ PostgreSQL 9.2 から Index Only Scan が増えてテーブルにアクセスしなくて良い場合もできた。

それを意識して、インデックスを利用する意味がない場合も思いつく。ディスクへのアクセスを十分減らすインデックスでなければならない。例えば、行数の多いテーブルに対して主キーを検索する場合はインデックスの価値がある。sequential scanを行うよりもインデックスの中に該当の行を取り出してからディスクにアクセスして情報を取得する。逆に行数の少ないテーブルで住所の項目を検索する場合、プランナはインデックスを参照せずにsequential scan を行う。その列にインデックスが存在しても利用されないのでは、ストレージや管理コストしか残らないので損になる。

インデックスを作成すべきか判断する場合は、開発環境などデータ数が少ない環境で行わないほうが良い。データが少ない環境だと、プランナはインデックスを利用しない方が効率が良いと判断するため、本番環境に近い環境で確かめるべき。

本番環境にインデックスを発行する際、インデックスが該当テーブルにロックをかける事を忘れてはいけない。より遅い方法だがWriteブロックをかけない CREATE INDEX CONCURRENTLY を利用することもできる。※ CREATE INDEX だけならReadブロックはかからないがWriteブロックがかかる。

最後に、更新・削除が繰り返されるとインデックスは徐々に劣化する。 REINDEX でインデックスを再構築することができる。 REINDEX はインデックスに対するロックがかかるので利用時は注意しよう。 CREATE INDEX と少し違って、Readブロックはかかる事もある、インデックスを使用しない SELECT はブロックしないが、インデックスを使用する SELECT はブロックするのだ。対策としては別名で CONCURRENTLY にインデックスを発行して、古いインデックスを DROP して、新インデックスを前のインデックス名に変えたらいい。手間に見えるし、一時的にインデックスが2つ存在するので、その間はパフォーマンスが劣化するけれど、ロックはかからない。

以上。