PostgreSQLのインデックスについて

インデックスとは

• データベースの性能を向上させるための一般的な方法。
• テーブルにインデックスを作成することで、データ検索時にインデックスの位置を直接アクセスすることができるため、検索速度を上げることができる。
• インデックスが設定されていない場合の検索では、テーブルの最初から順番に1件ずつ探すため、時間がかかり、パフォーマンスが下がる。
• インデックスには検索に特化した様々なデータ構造がある。

※データ構造：コンピュータでデータを扱う際に、データを格納する一定の形式のこと
具体例）配列やリストなどもデータ構造の一種
プログラムを書く際にはどのような処理を行うのかを事前に考え、その処理にとって最適なデータ構造を選択する必要がある。そのためにも、様々なデータ構造の特徴を知っておくことが重要。

メリット	デメリット
インデックスを作成しておけば、データ抽出の際にカラムに設定されたインデックスを検索するので、データベースの負担も軽減し、なおかつ検索効率がアップする。	インデックスを作成しても必ずパフォーマンスが向上するとは限らない。インデックス設定により検索対象のテーブルの更新速度が下がる等のデメリットがある。また、インデックスを設定しても性能が上らないケースもある。

インデックスの特徴

• 検索処理を効率化し、性能を上げるために使用される
• ただし、作成することで必ず性能が向上するという保証はないため、作成時には注意が必要である
• インデックスは未使用であっても整合性が取れるように管理されるため、更新処理が遅くなる可能性がある
• 複数カラムを組み合わせて作成することもできる
• 主キー制約やユニーク制約を定義すると自動で作成される
• インデックスに使用できるインデックスの種類はB-Tree、GiST、GIN、ハッシュである
• インデックスを作成しても、WHERE句などの条件でインデックスの作成されているカラムが使用されていない場合、インデックスは使用されない

様々なインデックス

• PostgreSQLでは、B-tree、R-tree、Hash、GiSTといった複数の種類のインデックスを使用することができる。
• 扱うデータによって使い分ける。
• 一般的に、PostgreSQLではデフォルトのB-treeが使用される。

インデックスの作成

• デフォルトで、B-treeインデックスを作成する。
• インデックスの作成方法は以下の3通り
• 書式：
  • ①CREATE INDEX インデックス名 ON テーブル名(カラム名);
  • ②CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名);
  • ③ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

①インデックスを作成

書式：CREATE INDEX インデックス名 ON テーブル名(カラム名);

tbl_Addressテーブルのzipカラムにidx_zipインデックスを作成

postgres=# CREATE INDEX idx_zip ON tbl_Address(zip); 

②テーブル作成と同時にインデックスも作成

書式：CREATE TABLE テーブル名(カラム名 型,カラム名 型, ... INDEX(カラム名);

tbl_Addressテーブルのzipカラムにidx_zipインデックスを作成

postgres=# CREATE TABLE tbl_Address(
postgres-# UserID INT NOT NULL PRIMARY KEY,
postgres-# Zip CHAR(8) DEFAULT NULL,
postgres-# Ken CHAR(5) DEFAULT NULL,
postgres-# Addr VARCHAR(200) DEFAULT NULL,
postgres-# INDEX idx_zip(zip)
postgres-# );

③後からテーブルにインデックスを追加

書式：ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

tbl_Addressテーブルのzipカラムにidx_zipインデックスを作成

postgres=# ALTER TABLE tbl_Address ADD INDEX idx_zip(zIP);

インデックスの削除

書式：DROP INDEX インデックス名;

postgres=# CREATE INDEX idx_zip; 

インデックスの変更

インデックスの名前を変更

書式：ALTER INDEX インデックス名 RENAME TO 新たなインデックス名;

postgres=# ALTER INDEX idx_zip RENAME TO new_idx_zip; 

インデックスの確認

• テーブルのカラムの情報を取得した時（/d）に、どのカラムに対してインデックスが作成されているのかの情報も表示される。
• インデックス名 インデックスの種類(対象のカラム)
  • 例）"idx_zip" btree (zip)

postgres=# \d tbl_Address

インデックスの一覧を確認

• テーブルに限らず、存在しているインデックスの一覧を確認する。

postgres=# \di