AIテックブログ

Appearance

PostgreSQLのインデックス：仕組みと効果的な使い方 ​

1. インデックスとは？ ​

データベースにおけるインデックスは、テーブル内の特定のカラムの値をキーとして、対応する行の位置情報を保持するデータ構造です。インデックスを利用することで、データベースはテーブル全体をスキャン（フルテーブルスキャン）することなく、目的のデータを高速に見つけ出すことができます。

これは、書籍の巻末にある索引（インデックス）と同じ役割を果たします。索引があれば、特定のキーワードがどのページにあるかをすぐに調べることができます。インデックスがない場合、本を最初から最後まで読まなければ目的の情報を見つけられません。

インデックスのメリット ​

  • 検索速度の向上: SELECT文のWHERE句やJOIN句で指定された条件に合致するデータを高速に取得できます。
  • ソート処理の高速化: ORDER BY句で指定されたカラムにインデックスがあれば、ソート処理を省略できる場合があります。
  • ユニーク制約の保証: 主キー制約やユニーク制約は、内部的にユニークインデックスを利用して実現されています。

インデックスのデメリット ​

  • 追加のディスク容量: インデックスはテーブルとは別にデータを保持するため、その分ディスク容量を消費します。
  • 書き込み性能の低下: INSERT, UPDATE, DELETEなどのデータ更新処理が発生すると、テーブルのデータだけでなくインデックスのデータも更新する必要があるため、処理時間が長くなります。

2. PostgreSQLの主要なインデックスの種類 ​

PostgreSQLは、用途に応じて様々な種類のインデックスを提供しています。

B-Treeインデックス ​

最も一般的に使用されるインデックスタイプです。等価比較（=）や範囲比較（<, >, <=, >=）を効率的に処理できます。B-Tree（バランスツリー）という木構造でデータを保持しており、ソートされた状態のデータを扱うのが得意です。

主な用途:

  • 主キー、ユニークキー
  • WHERE句での等価・範囲検索
  • ORDER BY句によるソート
  • JOINの結合キー
sql
-- usersテーブルのemailカラムにB-Treeインデックスを作成
CREATE INDEX idx_users_on_email ON users (email);

Hashインデックス ​

等価比較（=）のみを高速化するためのインデックスです。B-Treeとは異なり、範囲検索やソートには利用できません。ハッシュテーブル構造を利用しており、キーのハッシュ値に基づいてデータを格納します。

主な用途:

  • 等価比較のみが頻繁に行われるカラム
sql
-- productsテーブルのskuカラムにHashインデックスを作成
CREATE INDEX idx_products_on_sku ON products USING hash (sku);

Note: PostgreSQL 10以前では、HashインデックスはWAL（Write-Ahead Logging）に記録されず、クラッシュ後のリカバリができないため、利用は非推奨でした。PostgreSQL 10以降ではこの問題は解消されています。

GiST (Generalized Search Tree) インデックス ​

B-Treeでは効率的に扱えないような、より複雑なデータ型や検索条件に対応するための汎用的なインデックスです。全文検索、地理空間データ（PostGIS）、幾何データ型などで利用されます。

主な用途:

  • PostGISによる地理空間検索
  • 全文検索でのtsvector型
  • 幾何データ型（point, boxなど）の範囲検索
sql
-- articlesテーブルのcontent_tsvectorカラムにGiSTインデックスを作成
CREATE INDEX idx_articles_on_content_tsvector ON articles USING gist (content_tsvector);

GIN (Generalized Inverted Index) インデックス ​

GiSTと同様に複雑なデータ型を扱いますが、特にJSONBや配列、全文検索のように、一つのカラムに複数の値が含まれる（複合型）場合に強力な性能を発揮します。転置インデックスという構造を持っており、各要素がどの行に含まれているかを効率的に検索できます。

主な用途:

  • JSONB内のキーや値の検索
  • 配列型の要素検索
  • 全文検索でのtsvector型
sql
-- documentsテーブルのdataカラム（JSONB型）にGINインデックスを作成
CREATE INDEX idx_documents_on_data ON documents USING gin (data);

BRIN (Block Range Index) インデックス ​

非常に巨大なテーブルに対して、ディスク上の物理的な格納順序とカラムの値の間に相関がある（例：タイムスタンプなど）場合に有効なインデックスです。インデックスサイズが非常に小さく、作成も高速ですが、B-Treeほどの精度はありません。

主な用途:

  • ログデータや時系列データなど、物理的な順序と値が相関する巨大なテーブル
sql
-- logsテーブルのcreated_atカラムにBRINインデックスを作成
CREATE INDEX idx_logs_on_created_at ON logs USING brin (created_at);

3. 効果的なインデックスの作成戦略 ​

インデックスは万能ではありません。効果を最大化するためには、アプリケーションの特性を理解し、戦略的に作成する必要があります。

WHERE句とJOIN句を分析する ​

最も基本的なアプローチは、頻繁に実行されるクエリのWHERE句やJOIN句で使われるカラムを特定することです。EXPLAINコマンドを使って実行計画を分析し、フルテーブルスキャンが発生している箇所を見つけましょう。

sql
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

カーディナリティを考慮する ​

カーディナリティとは、カラム内の値の種類の多さ（ユニーク度）を示す指標です。性別カラムのようにカーディナリティが低い（'男性', '女性'など種類が少ない）カラムにインデックスを作成しても、効果は薄いです。逆に、ユーザーIDやメールアドレスのようにカーディナリティが高いカラムはインデックスの効果が大きくなります。

複合インデックスを活用する ​

複数のカラムを組み合わせた条件で検索することが多い場合は、複合インデックスが有効です。

sql
-- last_nameとfirst_nameで検索することが多い場合
CREATE INDEX idx_users_on_last_name_and_first_name ON users (last_name, first_name);

複合インデックスでは、カラムの順序が非常に重要です。上記の例では、last_nameだけで検索する場合にもインデックスが利用されますが、first_nameだけで検索する場合には利用されません。原則として、最も絞り込み効率の高い（カーディナリティが高い）カラムを左側に配置します。

関数インデックス ​

WHERE句でカラムに関数を適用して検索する場合、通常のインデックスは利用されません。このような場合は、関数インデックスを作成します。

sql
-- emailを小文字に変換して検索する場合
CREATE INDEX idx_users_on_lower_email ON users (LOWER(email));

-- このインデックスは以下のクエリで有効
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

部分インデックス (Partial Index) ​

テーブル内の一部の行だけを対象としたインデックスを作成することもできます。これにより、インデックスのサイズを小さく保ち、効率を上げることができます。

sql
-- 未処理の注文（processed = false）のみを対象にインデックスを作成
CREATE INDEX idx_orders_on_unprocessed ON orders (order_date) WHERE processed = false;

4. インデックスの運用と注意点 ​

定期的なメンテナンス ​

PostgreSQLは、データの更新（UPDATE, DELETE）によって不要になった領域を自動的には解放しません。これにより、インデックスが肥大化（Bloat）し、性能が劣化することがあります。

  • REINDEX: インデックスを再構築し、最適化します。テーブルをロックする時間が長くなる可能性があるため、注意が必要です。
  • VACUUM: 不要領域を回収します。VACUUM FULLはテーブルをロックしますが、通常のVACUUMはロックしません。自動バキューム（Autovacuum）の設定を適切に行うことが重要です。

インデックスの重複を避ける ​

不要なインデックスや重複したインデックスは、書き込み性能を低下させるだけでメリットがありません。定期的にインデックスの利用状況を確認し、不要なものは削除しましょう。

pg_stat_user_indexesビューを参照することで、各インデックスがどれくらい利用されているか（idx_scan）を確認できます。

テスト環境での検証 ​

本番環境にインデックスを追加する前には、必ずテスト環境で効果を検証しましょう。特定のクエリは速くなる一方で、別のクエリの性能が低下する可能性も考慮する必要があります。

まとめ ​

インデックスは、PostgreSQLのパフォーマンスを最大化するための強力なツールです。しかし、その効果を最大限に引き出すためには、各種インデックスの特性を理解し、アプリケーションのクエリパターンに合わせて戦略的に設計・運用することが不可欠です。EXPLAINを活用してクエリの実行計画を分析し、継続的にインデックス戦略を見直していくことが、データベースの健全なパフォーマンスを維持する鍵となります。

AI が自動生成した技術記事をまとめたテックブログ

Copyright © 2024 okdyy75