PostgreSQL - インデックス：初めての人向けのガイド

こんにちは、未来のデータベースの魔法使いたち！今日は、PostgreSQLのインデックスの世界に興味深い旅に出発します。コードを書いたことがない人も心配しないでください - 私があなたの親切なガイドになり、一緒にこのトピックをステップバイステップで探求しましょう。あなたのお気に入りの飲み物を手に取り、さあどうぞ！

PostgreSQL - Indexes

インデックスとは？

本題に入る前に、シンプルな類似を考えてみましょう。図書館で特定の本を探しているとします。何も整理されていない場合、あなたはすべての本を探さなければなりません。それは時間がかかりますよね！しかし、図書館にはインデックス - カードカタログやコンピュータシステムのようなもの - があり、必要な本を素早く見つけるのを助けてくれます。

データベースの世界では、インデックスも同じ目的を果たします。インデックスは、データベース検索エンジンがデータの検索を速くするために使用できる特別なルックアップテーブルです。要するに、インデックスはテーブルの操作速度を向上させるデータ構造です。

このチュートリアルを通して使うためのシンプルなテーブルを作ってみましょう：

CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

INSERT INTO students (name, age, grade) VALUES
('Alice', 18, 'A'),
('Bob', 19, 'B'),
('Charlie', 20, 'A'),
('David', 18, 'C'),
('Eve', 19, 'B');

これにより、studentsというテーブルが作成され、4つのカラム：id、name、age、gradeが含まれます。また、操作するためのサンプルデータも追加されています。

インデックスの種類

テーブルができたので、PostgreSQLが提供するさまざまなインデックスの種類を見てみましょう。それぞれのインデックスには強みがあり、異なるシナリオに適しています。

1. B-tree インデックス

B-tree（バランスツリー）は、PostgreSQLのデフォルトのインデックス種類です。それはスイスアーミーナイフのようなもの - 多様で多くの状況に対応できます。

nameカラムにB-treeインデックスを作成してみましょう：

CREATE INDEX idx_student_name ON students USING BTREE (name);

このインデックスは、nameカラムで検索やソートを行うクエリに特に役立ちます。

2. ハッシュインデックス

ハッシュインデックスは、等価比較に最適化されています。それは辞書のように、単語を素早く検索できるものです。

ageカラムにハッシュインデックスを作成してみましょう：

CREATE INDEX idx_student_age ON students USING HASH (age);

このインデックスは、WHERE age = 18のような正確な年齢マッチのクエリを速くします。

3. GiST インデックス

GiST（一般化検索ツリー）インデックスは、全文検索や幾何データ、カスタムデータタイプのインデックスに役立ちます。

全文検索のためのGiSTインデックスの例：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_student_name_gist ON students USING GIST (name gist_trgm_ops);

このインデックスは、nameカラムの部分一致や類似検索に役立ちます。

4. GIN インデックス

GIN（一般化逆索引）インデックスは、単一のカラムに複数の値が含まれる場合、例えば配列やJSONデータに最適です。

テーブルにJSONカラムを追加し、GINインデックスを作成してみましょう：

ALTER TABLE students ADD COLUMN hobbies JSONB;
CREATE INDEX idx_student_hobbies ON students USING GIN (hobbies);

このインデックスは、JSONデータ内の特定の趣味を検索するのに役立ちます。

以下に、これらのインデックス種類をまとめた表を示します：

インデックス種類	最適な用途	例
B-tree	一般用途、ソート	名前での検索やソート
ハッシュ	等価比較	正確な年齢マッチ
GiST	全文検索、幾何データ	名前の部分一致
GIN	配列やJSONデータ	JSONフィールド内の検索

部分的インデックス

時々、データのサブセットに対してインデックスを作成することが必要です。それが部分インデックスです。それは、本の特定の節に対してインデックスを作成するようなものです。

gradeが'A'の学生に対する部分インデックスを作成してみましょう：

CREATE INDEX idx_student_grade_a ON students (name) WHERE grade = 'A';

このインデックスは、特に'A'グレードの学生を探すクエリを速くします。

暗黙のインデックス

PostgreSQLは、特定の状況で自動的にインデックスを作成します。最も一般的なのは、PRIMARY KEYやUNIQUE制約を定義する場合です。

私たちのstudentsテーブルでは、idカラムにPRIMARY KEYとして定義したため、PostgreSQLは自動的にインデックスを作成しました。

DROP INDEX コマンド

インデックスを作成できるように、不要になった場合にインデックスを削除することもできます。以下はインデックスを削除する方法です：

DROP INDEX idx_student_name;

このコマンドには注意してください - インデックスを削除することで、クエリのパフォーマンスに大きな影響を与える可能性があります。

インデックスを避けるべき場合

インデックスはクエリのパフォーマンスを大幅に向上させることができますが、常に最適な解決策ではありません。以下はインデックスを作成する前に考えたい状況です：

小さなテーブル：テーブルが非常に少ない行を持っている場合、フルテーブルスキャンの方がインデックスを使用するよりも速いかもしれません。
頻繁に更新されるテーブル：インデックスはテーブルデータが変更될 때更新される必要があり、書き込み操作を遅くする可能性があります。
選択性の低いカラム：カラムが行の総数に対して非常に少数のユニークな値を持っている場合、インデックスはあまり役に立たないかもしれません。
頻繁にクエリされないテーブル：テーブルが主にデータを書き込むためのものであり、ほとんどクエリされない場合、インデックスの維持コストが利点を上回る可能性があります。

インデックスは芸術と同じように科学であり、特定のユースケースに最適なバランスを見つけるためには実験とパフォーマンステストが必要です。

そして、ここまでPostgreSQLのインデックスについての旅を終えました。このガイドがインデックスを理解するのに役立つことを願っています。実践が完璧を生むことを忘れずに、あなたのデータベースプロジェクトでこれらの概念を試してみてください。

ハッピーアイデックス、あなたのクエリが常に迅速でありますように！

Credits: Image by storyset

前のチュートリアル:

PostgreSQL - トリガー

次のチュートリアル:

PostgreSQL - テーブル変更コマンド