人気の高いオープンソースのリレーショナルデータベース管理システムであるPostgreSQLは、クエリパフォーマンスを最適化し、データ取得効率を高めるために、様々なインデックスタイプを提供しています。この記事では、PostgreSQLで様々なタイプのインデックスを作成する方法を学びます。可能な限り、DMLステートメントと Navicat for PostgreSQL 16 の両方を使用して、無料の "dvdrental"サンプルデータベース にインデックスを適用します。
1. Bツリーインデックス：
Bツリーインデックスは、PostgreSQLのデフォルトのインデックスタイプで、テキスト、数値、タイムスタンプなど、様々なデータタイプに適しています。バランスの取れたツリー構造でデータを整理し、効率的な範囲クエリと等価検索を容易にします。"payment"テーブルの"customer_id"列にBツリーインデックスを作成しましょう：
CREATE INDEX btree_customer_id_idx ON payment(customer_id);
Navicatでは、テーブルデザイナーの“インデックス”タブにインデックスがあります。上記のインデックスを作成するためには、“名前”フィールドに“btree_customer_id_idx”と入力し、“フィールド”に“customer_id”を選択し、“インデックスメソッド”ドロップダウンから“B-Tree”を選択します：
上記の全てのフィールドに値が入力されたbtree_customer_id_idxインデックスは次のとおりです：
“保存”ボタンをクリックしてインデックスを作成します。
2. ハッシュインデックス：
ハッシュインデックスは、等価性チェックに最適ですが、範囲クエリにはそれほど効果的ではありません。ハッシュインデックスは、ハッシュ関数を使用してキーをインデックスエントリにマップします。以下に、“inventory”テーブルの“film_id”列にハッシュインデックスを作成する方法を示します。まずは、DMLステートメントを使用します：
CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);
次に、Navicatを使用します：
3. GiSTインデックス：
汎用検索ツリー(GiST)インデックスは、様々なデータ型と複雑なクエリをサポートしているため、全文検索やジオメトリデータタイプなどのアプリケーションに幅広く使用できます。
ジオメトリ列にGiSTインデックスを作成する例を次に示します：
CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);
4. SP-GiSTインデックス：
スペース分割された汎用検索ツリー(SP-GiST)インデックスは、多次元または階層構造のデータタイプに適しています。バランスの取れていないツリーを効率的にインデックスします。
tsvector列にSP-GiSTインデックスを作成する例を次に示します：
CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);
5. GINインデックス：
汎用転置インデックス(GIN)は、全文検索、配列タイプ、複合データタイプなどのケースに最適です。複数のキーまたはコンポーネントを持つデータタイプに効果的です。全文検索用に、“film”テーブルの“title”列にGINインデックスを作成しましょう：
CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));
以下は、gin_title_idxインデックスが追加されたNavicatの“film”テーブルのインデックス タブです：
6. BRINインデックス：
ブロックレンジインデックス(BRIN)は、個々の行ではなくデータブロックの範囲をインデックスするため、ソートされたデータを含む大きなテーブルに適しています。隣接する値の間に相関関係がある列に効果的です。"rental"テーブルの"rental_date"列にBRINインデックスを作成する方法は次のとおりです：
CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);
Navicatのbrin_rental_date_idxインデックスは次のとおりです：
まとめ
PostgreSQLは、多様なデータタイプとクエリ要件に対応する様々なインデックスタイプを提供しています。各インデックスタイプの特性を理解することで、データベース管理者と開発者は、データベースのパフォーマンスを最適化する際に情報に基づいた決定を下すことができます。一方、Navicat for PostgreSQL 16 などのツールを使用すると、インデックスの操作がはるかに簡単になります。