【Postgersql】インデックス作成・削除

インデックスとは

参考 www.dbonline.jp

  • DBの検索時には通常全データ探すけど、五十音順で並べたりしているカンペつくって探す範囲を超絞る仕組み
  • Insert、Updateのたびに情報を作り変えるためコストあり。
  • DBによって全項目使わないと有効にならない(某MySQLエンジン)とか途中は抜けてても探すよ(Oracle社製某エンジン)とか差がある。

CREATE INDEX

インデックスを作成するDDL

DBに書き込みロックかけるけど時間は最速の方法

create index [<インデックス名>] on <テーブル名> (列, 列 ... )

create index IDX_USERS_3 on USERS (userid, password);

構成にもよるけど、レコード100万とかだったら10~20分ぐらいとめる。 サービス止めないと実行できない。

大小文字不問で重複チェックかけるインデックス作る場合、lowerとかならつくれるみたい。

create index IDX_USERS_4 on USERS ((lower(MAILADDRESS)));

DB書き込みロックかけないしオン中もいけるけどすごく作るのが遅い方法

実装どうやってんのかわかんないけど、concurrentlyオプションを付与すると、オン中もへーきでインデックス作ることができる。

create index concurrently [<インデックス名>] on <テーブル名> (列, 列 ... )

更新トランザクションがある場合は、index作成の処理のほうを停止。 もうひとつの違いは、全レコードスキャンを2回かけることだそうだ。

参考 https://www.postgresql.jp/document/9.3/html/sql-createindex.html

インデックス確認

インデックスの一覧

全テーブルのインデックス確認

\id

特定テーブルのインデックス確認

\id <テーブル名>

または

\d <テーブル名>

(※使用したPostgreqsl環境では\idが使えなかった。。何故だ)

インデックスの詳細確認

\d <インデックス名>

DROP INDEX

インデックスを削除するDDL

基本

drop index <インデックス名>;

CASCADEオプション(依存ツリーごとばさっと削除)もあるみたい。 Indexに依存するのってトリガーぐらいしか浮かばないけど、色々試した後には使ってみようかな。

drop index IDX_USERS_4 cascade;