PostgreSQL インデックスの効率的な使用

最終更新日 2022年12月28日(水)

Postgres には多くのインデックスの種類と、インデックスのさまざまな使用方法があります。この記事では、利用可能なインデックスの種類の概要を示し、最も一般的なインデックスの種類である B-Tree を使用およびメンテナンスするさまざまな方法について説明します。

インデックスは、比較的少数の行をテーブルから効率的に取得する方法です。インデックスが有用なのは、テーブルから取得する行数が比較的少ない (つまり、行の取得条件である WHERE 句が選択的である) 場合に限られます。B-Tree インデックスはソートの回避にも役立ちます。

インデックスの種類

Postgres では、さまざまなインデックスの種類がサポートされています。

  • B-Tree​ は、CREATE INDEX​ を実行するとデフォルトで設定されます。ほぼすべてのデータベースに、何らかの B-Tree インデックスがあります。B-Tree では、ツリーの各ブランチでデータ量がほぼ同じになるようにバランスを保とうとします。そして、行を見つけるためにトラバースする必要があるレベルの数が常に同じ範囲内に収まるようにします。B-Tree インデックスの使用効果が高いのは、等価クエリと範囲クエリです。すべてのデータ型に対して機能し、NULL 値の取得にも使用できます。B-Tree の設計はキャッシングと非常に相性が良く、それは部分的にしかキャッシュされない場合でも同じです。
  • Postgres 10 よりも前のハッシュインデックス​は等価比較のみに有用ですが、使用機会はありません。これは、トランザクションセーフではなく、クラッシュ後は手動で再構築する必要があり、フォロワーに複製されないためです。したがって、B-Tree の使用に比べてあまり利点がありません。Postgres 10 以降のハッシュインデックスは、ログ先行書き込みが行われ、フォロワーに複製されるようになりました。
  • 汎用転置インデックス (GIN)​ は、インデックスで多くの値を 1 行にマッピングする必要がある場合に有用です。一方、B-Tree インデックスは 1 行に 1 つのキー値がある状況向けに最適化されています。GIN は、配列値のインデックス付けや全文検索の実装に適しています。
  • 汎用検索ツリー (GiST)​ インデックスは、一般的でバランスの取れたツリー構造を構築するために使用でき、等価比較や範囲比較よりも複雑な操作に使用できます。全文検索だけでなく、ジオメトリデータ型のインデックス付けにも使用されます。

この記事で扱うのは、デフォルトの B-Tree インデックスを最大限に活用する方法です。GIN および GiST インデックスの使用方法の例は、contrib パッケージ​を参照してください。

クエリでインデックスが使われていないのはなぜですか?

インデックスを使用しないことを Postgres プランナーが選択する理由は数多くあります。ほとんどの場合、理由が明白でないとしても、プランナーは正しい選択を行います。同じクエリでインデックススキャンを使用する場合と使用しない場合があっても問題ありません。テーブルから取得される行数は、クエリで取得する特定の定数値によって異なる場合があります。したがって、たとえば、"bar" の値が 2 である行の方が偶然にもずっと多い場合に、クエリプランナーがクエリ select * from foo where bar = 1​ にはインデックスを使用するのに対してクエリ select * from foo where bar = 2​ にはインデックスを使用しないとしても、それは適切である可能性があります。この場合、シーケンシャルスキャンの方がインデックススキャンよりもずっと高速である可能性が高いため、クエリプランナーは実際に、そのようにクエリを実行するコストの方が低いと正しく判断しました。

部分インデックス

部分インデックスはテーブルのデータのサブセットのみをカバーします。これは WHERE 句を伴ったインデックスです。インデックスのサイズを減らすことでインデックスの効率を高めるという考えです。インデックスが小さいほど、ストレージ使用量が減り、メンテナンスが容易になり、スキャンが高速になります。

たとえば、サイト上のコメントにユーザーがフラグを付けることを許可し、これによって flagged​ ブール値が true に設定されるとします。その後、フラグの付いたコメントをバッチで処理します。この場合、次のようにしてインデックスを作成する必要があります。

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

このインデックスはかなり小さく保たれ、より複雑なクエリで必要とされる場合に他のインデックスと併用することもできます。

式インデックス

式インデックスは、データに対する関数または変更に一致するクエリに有用です。Postgres では、その関数の結果にインデックスを付けて、生のデータ値による検索と同等の検索効率を実現できます。たとえば、サインイン用のメールアドレスを保存することをユーザーに求める一方で、認証では大文字と小文字を区別しない場合を考えます。この場合、メールアドレスはそのまま保存しますが、検索は WHERE lower(email) = '<lowercased-email>'​ 条件で実行することが可能です。そのようなクエリでインデックスを使用するには、次のような式インデックスを使用するのが唯一の方法です。

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

もう 1 つの一般的な例として、特定の日付の行を検索する場合に、datetime フィールドにタイムスタンプが保存されているが、日付にキャストされた値によって検索を行うことを考えます。CREATE INDEX articles_day ON articles ( date(published_at) )​ のようなインデックスは、WHERE date(articles.published_at) = date('2011-03-07')​ を含むクエリで使用できます。

一意インデックス

一意インデックスは、同じ値を持つ複数の行がテーブルに存在しないことを保証します。一意インデックスを作成することには、データの整合性とパフォーマンスという 2 つの理由でメリットがあります。一意インデックスのルックアップは非常に高速です。

データの整合性に関しては、ActiveModel クラスで validates_uniqueness_of​ 検証を使用しても、無効なレコードを作成する同時ユーザーがすでに存在する可能性があり、また存在するため、真の意味での一意性は保証されません。したがって、インデックスまたは一意性制約のどちらかを使用して、常にデータベースレベルで制約を作成します。

一意インデックスと一意性制約の違いはほとんどありません。式インデックスと部分インデックスは一意性制約として作成できないため、一意インデックスの方が下位と考えることができます。式に対する部分的な一意インデックスも可能です。

複数列インデックス

Postgres では複数列インデックスを作成できますが、作成することに意味がある状況を理解することが重要です。Postgres クエリプランナーでは、ビットマップインデックススキャンを実行することによって、複数の単一列インデックスを 1 つの複数列クエリに結合して使用することができます。一般的に、クエリ条件をカバーするすべての列にインデックスを作成でき、ほとんどの場合に Postgres はインデックスを使用します。そのため、複数列インデックスを作成する前に必ずベンチマークを行い、作成を正当化してください。インデックスには常にコストがかかり、複数列インデックスが最適化できるのはインデックス内の列を同じ順序で参照するクエリに限られるのに対し、複数の単一列インデックスの方が、より多くのクエリにパフォーマンスの向上をもたらします。

ただし、複数列インデックスが明確に意味を持つ状況があります。列 (a, b)​ のインデックスは、WHERE a = x AND b = y​ を含むクエリ、または WHERE a = x​ のみを使用するクエリで使用できますが、WHERE b = y​ を使用するクエリでは使用されません。そのため、これがアプリケーションのクエリパターンと一致する場合は、複数列インデックスのアプローチを検討する価値があります。この場合、a​ のみにインデックスを作成すると冗長になることにも注意してください。

B-Tree とソート

B-Tree インデックスのエントリは、デフォルトでは昇順でソートされます。インデックスの異なるソート順を指定することに意味がある場合もあります。たとえば、ページ番号を付けた記事のリストを、公開が最も新しいものが先頭に来るようにソートして表示する場合を考えます。articles​ テーブルに published_at​ 列を指定することができます。未公開の記事の場合、published_at​ の値は NULL です。

この場合、次のようにインデックスを作成できます。

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

Postgres 9.2 以上では、必要なものすべてをインデックスから取得できる (つまり、インデックスのない列に関心がない) 場合、インデックスは必ずしもテーブルを参照する必要はない、という点は注目に値します。これは “インデックスオンリースキャン” と呼ばれる機能です。

published_at​ のソート順でテーブルをクエリして結果を制限するので、同じ順序でインデックスを作成すると多少のメリットが得られます。Postgres は、必要な行をインデックスから正しい順序で検索し、データブロックにアクセスしてデータを取得します。インデックスがソートされていない場合、Postgres がデータブロックをシーケンシャルに読み取って結果をソートする可能性が高くなります。

この手法が主に関係するのは “ヌルを末尾にソート” の動作が必要なときの単一列インデックスです。それ以外の場合、インデックスはどの方向にもスキャン可能なので順序はすでに利用可能であるからです。a ASC, b DESC​ のように複合的なソート順をクエリが要求するときに複数列インデックスに対して使用する場合、さらに関連性が高くなります。

インデックスの管理とメンテナンス

Postgres のインデックスは、すべての行データを保持するわけではありません。インデックスがクエリで使用され、一致する行が見つかった場合でも、Postgres はディスクにアクセスして行データをフェッチします。さらに、(MVCC に関する記事)​で説明した) 行の可視性情報もインデックスに保存されないため、Postgres もディスクにアクセスしてその情報をフェッチする必要があります。

以上を踏まえると、場合によってはインデックスを使用しても実際には意味がない理由がわかります。インデックスは、ディスクルックアップの数を減らせるほどに選択的でなければ、価値がありません。たとえば、十分に大きいテーブルに対するプライマリキールックアップは、インデックスを有効利用します。クエリ条件に一致するテーブルをシーケンシャルスキャンする代わりに、Postgres はターゲット行をインデックスから見つけて、ディスクから選択的にフェッチすることができます。都市ルックアップテーブルのようにごく小さなテーブルに関しては、都市名で検索する場合でもインデックスは望ましくない可能性があります。その場合、Postgres はインデックスを無視してシーケンシャルスキャンを優先することを決定する可能性があります。Postgres は、テーブルの重要な部分にヒットするクエリではシーケンシャルスキャンを実行することを決定します。その列にインデックスがある場合、決して使用されないデッドインデックスになります。また、インデックスには必ずコストがかかり、具体的にはストレージとメンテナンスの面でコストがかかります。

Heroku アプリケーションのための本番環境、ステージング環境、およびその他の環境の運用について詳しくは、「Managing Multiple Environments​」(複数の環境の管理) の記事を参照してください。

クエリをチューニングするときや、どのインデックスが最も有効かを見極めるときは必ず、本番環境で使用している (または、使用する予定の) データベースにできるだけ近いデータベースを使用してください。インデックスを使用するかどうかは、Postgres サーバーの設定、テーブル内のデータ、インデックス、クエリなど、いくつかの要因に依存します。たとえば、"テストデータ" の小さなサブセットを載せた開発マシン上でクエリにインデックスを使わせようとしてもうまくいきません。Postgres は、データセットが小さすぎるのでインデックス全体を読み取るのはそのオーバーヘッドに見合わないと判断して、ディスクからデータをフェッチします。ランダム I/O はシーケンシャルよりもずっと低速なため、シーケンシャルスキャンのコストは、インデックスを読み取ってディスク上のデータを選択的に探すことによって発生するランダム I/O のコストよりも低くなります。インデックスチューニングの実行は、本番環境か、できるだけ本番環境に近いステージング環境で行う必要があります。Heroku Postgres データベースプラットフォームでは、簡単な手順で​本番データベースを別の環境にコピーできます。

本番データベースにインデックスを適用する準備ができたら、インデックスを作成するとテーブルが書き込みロックされることに注意してください。テーブルが大きい場合、サイトが数時間ダウンする可能性があります。幸いにも、Postgres では CREATE INDEX CONCURRENTLY​ を使用できます。構築にかかる時間はかなり長くなりますが、書き込みをブロックするロックは必要ありません。通常の CREATE INDEX​ コマンドでは、書き込みをブロックするが読み取りはブロックしないロックが必要になります。

最後に、しばらく時間が経つと、テーブルの行が頻繁に更新または削除される場合は特に、インデックスは断片化して最適でなくなります。そのような状況では、REINDEX​ を実行してインデックスのバランスを回復し、最適化することが必要な場合があります。ただし、親テーブルに書き込みロックがかかるため、大きなインデックスの再インデックス処理には注意が必要です。ライブサイトで同じ結果を得るための 1 つの戦略は、同じテーブルと列に対して別の名前で同時にインデックスを構築し、元のインデックスを削除し、新しいインデックスの名前を変更するというものです。この手順は、時間はかかりますが、ライブテーブルに長時間のロックをかける必要がなくなります。

特定のユースケースに合わせて最適化される B-Tree インデックスの作成と、アプリケーションの裏側で拡大を続けるデータベースを管理するためのオプションに関して、Postgres は多くの柔軟性を提供します。以上のヒントは、データベースを正常な状態に保ち、クエリを高速化するために役立ちます。