PostgreSQL インデックス

インデックスは、検索エンジンがデータを検索する速度を向上させる特別なテーブルクエリです。簡単に言えば、インデックスはテーブル内のデータを指し示すポインタです。データベース内のインデックスは、本の索引目次と非常によく似ています。

漢字字典の目次ページ（索引）を例にとると、ピンイン、画数、偏旁部首などで並べられた目次（索引）を使って、必要な漢字をすばやく見つけることができます。

インデックスは SELECT クエリと WHERE 句の高速化に役立ちますが、UPDATE 文と INSERT 文を使用する際のデータ入力は遅くなります。インデックスは作成または削除できますが、データには影響しません。

CREATE INDEX 文を使用してインデックスを作成します。これにより、インデックスに名前を付け、テーブルとインデックスを作成する1つまたは複数の列を指定し、インデックスが昇順か降順かを指定できます。

インデックスは一意にすることもでき、UNIQUE 制約と同様に、列または列の組み合わせで重複エントリを防ぎます。

CREATE INDEX コマンド

CREATE INDEX（インデックスの作成）の構文は次のとおりです：

CREATE INDEX index_name ON table_name;

インデックスの種類

単一列インデックス

単一列インデックスは、テーブルの1つの列のみに基づいて作成されるインデックスで、基本構文は次のとおりです：

CREATE INDEX index_name
ON table_name (column_name);

複合インデックス

複合インデックスは、テーブルの複数の列に基づいて作成されるインデックスで、基本構文は次のとおりです：

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

単一列インデックスでも複合インデックスでも、そのインデックスは WHERE 句のフィルタ条件で非常によく使用される列である必要があります。

1つの列だけが使用される場合は単一列インデックスを選択し、複数の列がある場合は複合インデックスを使用します。

一意インデックス

一意インデックスを使用するのは、パフォーマンスのためだけでなく、データの整合性のためでもあります。一意インデックスは、テーブルへの重複する値の挿入を許可しません。基本構文は次のとおりです：

CREATE UNIQUE INDEX index_name
on table_name (column_name);

部分インデックス

部分インデックスは、テーブルのサブセット上に構築されるインデックスです。サブセットは条件式によって定義されます。インデックスには条件を満たす行のみが含まれます。基本構文は次のとおりです：

CREATE INDEX index_name
ON table_name(column_list)
WHERE condition;

ここで、index_name は作成するインデックスの名前、table_name はインデックスを作成する列を含むテーブルの名前、column_list はインデックスを作成する列のリスト、condition はインデックスに含める行を定義するブール式です。

暗黙インデックス

PostgreSQL では、暗黙インデックスはオブジェクトの作成時にデータベースサーバーによって自動的に作成されるインデックスです。この種のインデックスは通常、主キー制約と一意制約のために自動的に作成されます。テーブルの作成時に列を主キー、一意制約、または外部キーとして宣言すると、PostgreSQL はその列に対して自動的に暗黙インデックスを作成します。これにより、インデックス管理が簡素化され、データベースのパフォーマンスが向上するという利点があります。

例えば、"users" という名前のテーブルを作成する際に、"userid" という名前の列を主キーとして宣言すると、PostgreSQL は "userid" 列に対して自動的に暗黙インデックスを作成します。つまり、新しいレコードを挿入するとき、データベースは自動的に "userid" 列に一意のインデックス値を生成します。

暗黙インデックスの作成と管理は PostgreSQL によって自動的に行われるため、ユーザーが手動で介入する必要はなく、データベース管理がより簡単かつ効率的になります。

例

次の例では、COMPANY テーブルの SALARY 列にインデックスを作成します：

# CREATE INDEX salary_index ON COMPANY (salary);

次に、\d companyコマンドを使用して COMPANY テーブルのすべてのインデックスを一覧表示します：

# \d company

得られる結果は次のとおりです。company_pkey は暗黙インデックスであり、テーブル作成時に作成されます：

サンプルdb=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

次のコマンドを使用すると、\diコマンドでデータベース内のすべてのインデックスを一覧表示できます：

サンプルdb=# \di
                    List of relations
 Schema |      Name       | Type  |  Owner   |   Table    
--------+-----------------+-------+----------+------------
 public | company_pkey    | index | postgres | company
 public | department_pkey | index | postgres | department
 public | salary_index    | index | postgres | company
(3 rows)

DROP INDEX（インデックスの削除）

インデックスは PostgreSQL の DROP コマンドを使用して削除できます。

DROP INDEX index_name;

以下の文を使用して、先ほど作成したインデックスを削除できます：

# DROP INDEX salary_index;

削除後、salary_index がインデックスのリストから削除されていることを確認できます：

サンプルdb=# \di
                    List of relations
 Schema |      Name       | Type  |  Owner   |   Table    
--------+-----------------+-------+----------+------------
 public | company_pkey    | index | postgres | company
 public | department_pkey | index | postgres | department
(2 rows)

どのような場合にインデックスの使用を避けるべきですか？

インデックスの目的はデータベースのパフォーマンスを向上させることですが、インデックスの使用を避けるべき状況がいくつかあります。

インデックスを使用する際は、次のガイドラインを考慮する必要があります：

  • インデックスは小さなテーブルには使用すべきではありません。
  • インデックスは、頻繁に大量の更新または挿入操作が行われるテーブルには使用すべきではありません。
  • インデックスは、多数の NULL 値を含む列には使用すべきではありません。
  • インデックスは、頻繁に操作される列には使用すべきではありません。
その他の拡張