適切なインデックスの選び方：PostgreSQLインデックスタイプガイド

間違ったPostgreSQLインデックスは、ディスクを無駄にし、書き込みを遅くし、クエリが何百万行ものスキャンを続ける原因となります。適切なインデックスは、WHERE句の演算子、カラムの型、データの形状によって異なります。

通常の等価性や範囲検索にはB-treeから始めましょう。クエリパターンが特定の演算子サポートを必要とする場合は、GIN、GiST、BRIN、SP-GiSTを利用してください。

PostgreSQLにおけるインデックスの重要性

PostgreSQLにおけるインデックス作成の核心は、クエリを満たすために検査する必要があるデータ量を減らすことです。インデックスがないと、PostgreSQLは多くのクエリでフルテーブルスキャンを実行する必要があり、特に大規模テーブルでは非常に遅くなる可能性があります。インデックスは、データベースが関連する行を迅速に見つけられるようにするデータ構造を作成します。インデックスの効果は、以下の要素に大きく依存します。

• 使用するインデックスの種類： 異なるインデックスタイプは、異なるデータ構造やクエリ操作に適しています。
• データ分布： 偏ったデータはインデックスのパフォーマンスに影響を与える可能性があります。
• クエリパターン： データのクエリ方法は重要な要素です。

以下に、最もよく選択されるインデックスタイプを示します。

PostgreSQLインデックスタイプの説明

PostgreSQLはいくつかのインデックスタイプを提供しています。日常的なパフォーマンス作業で最も有用なものは、B-tree、GIN、GiST、BRIN、SP-GiSTです。

1. B-Treeインデックス

B-treeはPostgreSQLのデフォルトであり、最も汎用性の高いインデックスタイプです。=、<、>、<=、>=などの一般的な比較演算子に適しています。B-treeインデックスは、等価性チェック、範囲スキャン、ソート、一意制約、プライマリキーに優れています。

仕組み： B-Treeインデックスは、データをソートされたツリー構造で保存します。ツリーの各ノードにはキーと子ノードへのポインタが含まれています。この構造により、検索、挿入、削除が効率的に行われ、通常は対数時間計算量になります。

ユースケース：

• 等価性検索（WHERE column = value）
• 範囲クエリ（WHERE column BETWEEN value1 AND value2 または WHERE column > value）
• ソート（ORDER BY column）
• 最小値または最大値の検索（ORDER BY column LIMIT 1）
• 一意制約とプライマリキー（暗黙的にB-Treeを使用）

例：

数百万件のレコードを持つusersテーブルを考えます。B-Treeを使用してemailカラムにインデックスを作成すると、特定のユーザーをメールアドレスで検索する際の速度が大幅に向上します。

CREATE INDEX idx_users_email ON users (email);
-- これで、次のようなクエリがはるかに高速になります：
SELECT * FROM users WHERE email = '[email protected]';

ヒント： B-Treeインデックスは一般的に良い出発点であり、多くの一般的なデータベース操作に十分なことが多いです。ただし、全文検索や地理空間データなどの特定のユースケースでは、他のインデックスタイプの方がパフォーマンスが高い場合があります。

2. GIN（Generalized Inverted Index）インデックス

GINインデックスは、配列、JSONドキュメント、全文検索ドキュメント（tsvector）など、複数の項目を含む複合値や値をインデックス化するために設計されています。これらの複合値内の特定の要素の存在を検索するクエリに特に効果的です。

仕組み： GINインデックスは、複合値内の各要素を、その要素を含む行のリストにマッピングします。これは転置インデックスであり、行を直接インデックス化するのではなく、値自体をインデックス化します。これにより、より大きな構造内に特定のアイテムが存在するかどうかを効率的にチェックできます。

ユースケース：

• 全文検索（tsvector vs. tsquery）
• 配列のインデックス化（ANY、@>演算子）
• JSONBデータのインデックス化（?、?|、?&、@>、<@演算子）

例：

文字列のARRAY型のtagsカラムを持つdocumentsテーブルがあるとします。'database'タグが付いたすべてのドキュメントを検索したいとします。

CREATE INDEX idx_documents_tags ON documents USING GIN (tags);
-- 'database'タグを持つドキュメントを検索するクエリ：
SELECT * FROM documents WHERE tags @> ARRAY['database'];
-- またはJSONBの場合：
CREATE TABLE products (id SERIAL PRIMARY KEY, details JSONB);
CREATE INDEX idx_products_details ON products USING GIN (details);
SELECT * FROM products WHERE details ? 'manufacturer';

注意： GINインデックスは、各要素を再インデックス化する必要があるため、B-Treeインデックスよりも更新が遅くなる可能性があります。ただし、複合型内の要素を含む検索では、優れたクエリパフォーマンスを提供します。

3. GiST（Generalized Search Tree）インデックス

GiSTインデックスは、カスタムインデックスタイプの作成を可能にするフレームワークです。幾何データ型のインデックス化や全文検索に一般的に使用されます。GiSTインデックスは、データが複雑でB-Tree構造にうまく適合しない場合に特に有用です。

仕組み： GiSTは非常に柔軟なインデックス化手法です。データ空間を再帰的に分割することで機能します。内部構造は使用する特定の演算子クラスによって異なりますが、一般的にデータをツリー状の構造に整理します。

ユースケース：

• 幾何データ型（点、線、多角形）の空間クエリ（&&、@>）
• 範囲インデックス化
• GiST演算子クラスを使用した全文検索

例：

空間インデックス化の場合、関心地点（POI）のテーブルがあり、特定の地理的領域内のすべてのPOIを見つけたいとします。

CREATE TABLE pois (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(Point, 4326) -- PostGIS拡張機能を使用
);

-- locationカラムにGiSTインデックスを作成
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- バウンディングボックス内のPOIを見つける（PostGIS関数を使用した例）
SELECT * FROM pois WHERE ST_Intersects(location, ST_MakeEnvelope(lon1, lat1, lon2, lat2, 4326));

ヒント： GiSTインデックスは、複雑なデータ型や空間クエリに強力です。また、部分インデックスにも使用でき、条件に基づいて行のサブセットのみをインデックス化することで、パフォーマンスをさらに最適化できます。

4. BRIN（Block Range INdex）インデックス

BRINインデックスは、データがディスク上の物理的な保存場所と自然な相関関係を持つ非常に大きなテーブル向けに設計されています。個々の行の値ではなく、物理ブロックアドレスの範囲をインデックス化することで機能します。これにより、非常に小さく、作成が高速ですが、インデックス化されたカラムの値が物理的な順序と相関している場合にのみ効果的です。

仕組み： BRINインデックスは、テーブルブロックの範囲に対する最小値と最大値を保存します。クエリ実行時に、PostgreSQLはブロック範囲の最小値/最大値をチェックします。クエリ条件がこの範囲外にある場合、ブロック範囲全体がスキップされ、フルテーブルスキャンが回避されます。これは、タイムスタンプやシーケンスIDなどの自然に順序付けられたデータに最も効果的です。

ユースケース：

• 非常に大きなテーブル
• 物理的な保存順序と強い自然相関を持つカラム（例：created_atタイムスタンプ、自動インクリメントID）
• ブロック内の値の範囲が、そのブロック内の行数よりも大幅に小さい場合

例：

timestampで順序付けられた数十億のエントリを持つログテーブルを考えます。

CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- created_atにBRINインデックスを作成
CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at);

-- 特定の日のログをクエリ：
SELECT * FROM logs WHERE created_at >= '2023-10-26 00:00:00' AND created_at < '2023-10-27 00:00:00';

警告： BRINインデックスは、データが物理的に順序付けられている場合にのみ効果的です。データがランダムな順序で挿入された場合、またはカラムの値が物理的な位置と相関していない場合、BRINインデックスはパフォーマンス上の大きな利点を提供せず、パフォーマンスを低下させる可能性さえあります。pages_per_rangeパラメータを調整して、BRINインデックスの効率を最適化できます。

5. SP-GiST（Space-Partitioned Generalized Search Tree）インデックス

SP-GiSTは、GiSTと同様の別のタイプの一般化検索ツリーですが、空間を不均衡に分割するアルゴリズムに最適化されています。不均一なデータ分布や、四分木やk-d木などの複雑な空間データ構造のインデックス化に特に有用です。

仕組み： SP-GiSTはさまざまな分割戦略を使用するため、さまざまなデータ型やクエリパターンに適応できます。特定の種類のデータ、特に高度にクラスタリングされた、または疎な分布を持つデータセットを扱う場合、GiSTよりも効率的な場合があります。

ユースケース：

• k-d木や四分木を使用した点データ
• ネットワークデータ
• 地理空間データ
• テキスト検索

例：

複雑な幾何構造によく使用されますが、一般的なユースケースは大量の点のセットのインデックス化です。

-- 点座標を持つテーブルを想定
CREATE TABLE points (id SERIAL PRIMARY KEY, coord POINT);

-- SP-GiSTインデックスを作成
CREATE INDEX idx_points_coord ON points USING SPGIST (coord);

-- 特定の領域内の点をクエリ
SELECT * FROM points WHERE coord <@ box '((x1,y1),(x2,y2))';

考慮事項： SP-GiSTインデックスは、従来のB-TreeやGiSTでも苦戦する可能性のある特定のデータ構造やクエリパターンに対して、パフォーマンス上の利点を提供できます。ただし、その複雑さのため、特定のベンチマークで利点が示されない限り、最初の選択肢になることはあまりありません。

その他のインデックスタイプ（簡単に）

• ハッシュインデックス： 等価比較（=）のみをサポートします。最新のPostgreSQLリリースではWALログに記録されますが、B-treeインデックスはより多くの演算子と順序付けをサポートするため、通常は最初の選択肢です。
• 部分インデックス： これらのインデックスは、WHERE句を満たすテーブル行のサブセットのみをインデックス化します。クエリが特定のデータサブセットを頻繁にターゲットにする場合、スペースを節約し、パフォーマンスを向上させることができます。
• 式インデックス： 1つ以上のカラムの式や関数にインデックスを作成できます。これは、lower(email)など、WHERE句でそれらの式を頻繁に使用するクエリに便利です。

各インデックスタイプをいつ使用するか？

適切なインデックスを選択することは、PostgreSQLのパフォーマンスチューニングの重要な部分です。以下は、決定に役立つクイックガイドです。

インデックスタイプ	最適な用途	サポートされる演算子	考慮事項
B-Tree	汎用、等価性、範囲、ソート	=, <, >, <=, >=	デフォルト、汎用性が高く、オールラウンダー。
GIN	全文検索、配列、JSONB、複合型	@@, @>, <@, ?, `?	, ?&`
GiST	空間データ、幾何型、全文検索	&&, @>, <@, @@（および演算子クラスによるその他）	柔軟性が高く、複雑なデータ構造に適している、B-Treeより遅い場合がある。
BRIN	物理的に相関するデータを持つ非常に大きなテーブル	<, >, <=, >=, =	サイズが小さく、作成が高速、順序付けられたデータ相関でのみ効果的。
SP-GiST	不均一なデータ、複雑な空間構造	演算子クラスによって異なる（例：空間、ネットワーク）	特定の分割戦略に効率的、調整がより複雑な場合がある。

考慮すべき要素：

1. クエリパターン： 最も頻繁に実行するクエリの種類は？等価性チェック、範囲スキャン、全文検索、空間クエリですか？
2. データ型： インデックス化されるデータの型（例：文字列、数値、配列、JSON、幾何点）は、最適なインデックスの選択に大きく影響します。
3. データ分布： データは自然に順序付けられていますか（タイムスタンプなど）、それともランダムに分布していますか？
4. 更新頻度： インデックス化されたカラムのデータはどのくらいの頻度で更新されますか？GINおよびGiSTインデックスは、B-Treeよりも更新が遅くなる可能性があります。
5. テーブルサイズ： 非常に大きなテーブルの場合、データ相関が存在すればBRINインデックスが有利になることがあります。
6. インデックスサイズとメンテナンス： インデックスに必要なディスク容量と、そのメンテナンスのオーバーヘッドを考慮してください。

インデックスの作成と管理

PostgreSQLは、インデックスを管理するためのシンプルなSQLコマンドを提供します。

• インデックスの作成：

  CREATE INDEX index_name ON table_name USING index_type (column_name [ASC|DESC] [NULLS FIRST|LAST], ...);
  

• インデックスの削除：

  DROP INDEX index_name;
  

• 既存のインデックスの表示：

  \d+ table_name;
  

ベストプラクティス： 本番環境に変更を適用する前に、ステージング環境でインデックスの作成や変更によるパフォーマンスへの影響を常にテストしてください。EXPLAIN ANALYZEを使用して、クエリがインデックスをどのように使用しているかを理解してください。

まとめ

演算子とデータ形状に一致するインデックスを選択し、EXPLAIN ANALYZEでそれを証明してください。インデックスは書き込みパスにも含まれるため、実際のクエリに役立つものを維持し、メンテナンスコストを増やすだけのものは削除してください。