適切なインデックスタイプの選び方

もうインデックスの理論には触れたし、種類も知ったし、作ったり消したりもできるようになったよね。あと、配列とかJSONBみたいな複雑なデータ型のインデックス化もやった。でも、ここからが本番。自分の用途に一番効くインデックスをどう選ぶかって話。間違った選択をすると、マジで大変なことになるから気をつけて！

イメージしてみて。データベースは図書館、クエリは本を探しに来るお客さん。もし本が床にバラバラに置いてあったら、探すのにめっちゃ時間かかるよね。インデックスは、ちゃんと整理された本棚やカタログみたいなもので、必要な本を一瞬で見つけられるようにしてくれる。

でも、変な本棚やカタログを使ったらどうなる？例えば、範囲検索が必要なのにHASHインデックスを使ったら、本のタイトルで探したいのに出版年だけのカタログしかない感じ。探すのに時間かかるし、みんなイライラ。データベースだと、クエリが遅くなって、システムに負荷がかかるってこと。

今日は、どうやってインデックスを選べばクエリが爆速になって、データベース(PostgreSQL)も疲れないかを話すよ。間違った選択をすると、結果は悲惨：クエリは遅いし、リソースは食うし、図書館員(PostgreSQL)は鬱になる。

インデックス選びのチェックリスト

インデックスを選ぶときは、次の質問に答えてみて：

1. このカラムのデータ型は何？
   • 例えば、数値INTEGER、FLOATならB-TREEインデックスがよく使われるし、配列ならGIN、テキストフィールドは用途によるかな。

2. どんなクエリを一番よく使う？

   • WHERE field = valueみたいなピンポイント検索？それならB-TREEかHASHが合うかも。
   • 配列やJSONBで検索？それならGINをチェック。
   • ジオデータや範囲検索？GiSTを考えてみて。

3. データはどんな感じで変わる？

   • もしINSERTやUPDATEが多いテーブルなら、インデックスを増やしすぎない方がいいよ。オーバーヘッドが増えるから。

4. ユニーク性が必要？

   • それならUNIQUE属性付きのインデックスを使うしかないね。

ケーススタディ：インデックス選びのリアルな例

いくつか実際のシナリオを見てみよう。

1. シンプルなイコール検索

学生データベースで、emailで学生をすぐに見つけたいとき：

SELECT * FROM students WHERE email = 'student@example.com';

ここで大事なのは、イコール検索ってこと。B-TREEインデックスが一番得意なパターンだよ。

CREATE INDEX idx_students_email ON students (email);

もしemailがユニークじゃなきゃダメなら：

CREATE UNIQUE INDEX idx_students_email_unique ON students (email);

2. 範囲検索

今度は、18歳以上の学生を探したい場合：

SELECT * FROM students WHERE age > 18;

範囲検索にもB-TREEはバッチリ。順序検索に特化してるからね。

CREATE INDEX idx_students_age ON students (age);

3. 配列でのフィルタリング

coursesテーブルがあって、あるカラムにコースに登録してる学生IDの配列が入ってるとする。ID123の学生が登録されてるコースを全部探したいとき。

SELECT * FROM courses WHERE student_ids @> ARRAY[123];

こういうクエリにはGINインデックスが最適。配列に特化してるから。

CREATE INDEX idx_courses_students_ids ON courses USING gin (student_ids);

4. JSONBからデータを取り出す

例えば、JSONBデータを持つテーブルがあって、注文情報が入ってるとする。都市が「Moscow」の注文を全部探したい：

SELECT * FROM orders WHERE data->>'city' = 'Moscow';

ここもGINインデックスが活躍。JSONBのキーや値で効率よく検索できる。

CREATE INDEX idx_orders_data ON orders USING gin (data);

5. 地理情報データ

地理情報を扱う場合、例えば指定した半径内のポイントを探したいときはGiSTインデックスを使おう。このタイプはジオメトリや範囲に強い。

CREATE INDEX idx_locations_geom ON locations USING gist (geom);

いろんなインデックスのパフォーマンス比較

実際に、emailで学生を検索する例で比べてみよう。テーブルには100万件のデータがある。インデックスあり・なし、いろんなタイプでクエリを実行してみると：

結論：この場合、B-TREEインデックスを使うとクエリが500倍以上速くなる！

インデックス選びでありがちなミス

一番多いミスは「とりあえず全部インデックス化しとくか」ってやつ。例えば、テーブルの全カラムにインデックスを作ったら、INSERTのパフォーマンスが激落ちした…みたいな。インデックスは万能じゃない。正しく使えば強力だけど、間違うと逆効果。

もう一つの典型的なミスは、タイプの選択ミス。例えば、範囲検索にHASHインデックスを使って、クエリが激遅になるとか。HASHインデックスはピンポイント検索専用だからね。

インデックス選びのおすすめ

• イコール検索やソートが多いならB-TREEを使おう。
• ピンポイント一致でメモリ消費を抑えたいならHASHもあり。
• 配列やJSONBを扱うなら、迷わずGIN。
• 範囲や地理情報にはGiSTを使おう。

そして一番大事なのは、クエリをちゃんと分析すること！EXPLAINやEXPLAIN ANALYZEを使って、PostgreSQLがどうインデックスを使ってるか、どこを改善できるかをチェックしよう。

EXPLAIN ANALYZE
SELECT * FROM students WHERE email = 'student@example.com';

今日はここまで！これで君もインデックス選びのジェダイ。必要ないところにインデックスを作らず、パフォーマンスへの影響をいつも確認しよう！