PostgreSQLのIndex入門

PostgreSQLでのSELECTなどで対象のレコードを早く検索するための「Index(インデックス、索引)」についてのまとめです。

🗻 お勧めスライド：PostgreSQLクエリ実行の基礎知識

PostgreSQLについて丁寧な解説がされているスライドです。PostgreSQLの実行計画を理解するのにすごく参考になりました！

😼 Index作成までの流れ

• いつ
  • 新規テーブルの作成時
  • DBのパフォーマンス・チューニングの際
• どうやって
  • SQLの実行ログから、実行回数が多い & 実行に時間がかかるSQLを探す
  • EXPLAINで実行計画を元に最適なindexを探す
  • 代替案としてサマリテーブルを作ったり、キャッシュをもつことも検討

🐹 Index作成

SQLのCREATE INDEXでIndexを作成できます。

-- レコードがユニークではないインデックスの場合
CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- レコードがユニークなインデックスの場合
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

😎 実行計画の取得：EXPLAIN

DBはSQLクエリを解析して、最も効率のよい問い合わせ計画(実行計画)を作成し、エグゼキュータがデータを取得します。
SQLにEXPLAINを付けるとDBの作成した実行計画を取得できます。

EXPLAIN SELECT
  カラム名1,
  カラム名2,
  ...
FROM
  テーブル名
WHERE
  条件...;

DBのパフォーマンスを上げるためには、データの性質によって最適な実行計画を立てるようにインデックスで手助けすることが必要です。

🐞 実際の行数の取得：EXPLAIN ANALYZE

実際の実行結果等の取得はEXPLAIN ANALYZEを使います。

EXPLAIN ANALYZE SELECT
  カラム名1,
  カラム名2,
  ...
FROM
  テーブル名
WHERE
  条件...;

実行時間や、検索した行数、ループの回数を取得できます。

🐯 EXPLAINの概要

EXPLAINコマンドで出てくる項目の概要です。

Cost

• コストは複数の実行計画から、最適な実行計画を選定をするための指標
• 初期コストは最初の行を返すまでの値。トータルコストは最後の行を返すまでの値。重要なのはトータルコスト
• コストは推定値。値はシーケンシャルI/Oで1ページを読み込むコストを1.0とした場合の相対値

オプション	説明	既定値
seq_page_cost	シーケンシャル読み込み1回	1.00
random_page_cost	ランダム読み込み1回	4.00
cpu_tuple_cost	行の処理1回	0.01
cpu_index_tuple_cost	インデックスの処理1回	0.005
cpu_operator_cost	計算1回	0.0025

Rows

• 推定された行数の表示
• 実際の数値とかけ離れている場合はAnalyze/Vacuumが必要

Width

• このレベルでの推定された入力のサイズ。重要ではない

Explain演算子

• Explain演算子とは、内部的な処理の計算タイプ
• プランナーが統計情報と、work_memの大きさをもとに最適なプランを推定する

分類	演算子	概要
テーブルスキャン	Seq scan	インデックスを使用せず、テーブルを最初から最後までシーケンシャルにアクセス。候補行が多い場合に有効
	Index scan	インデックスを使用してスキャン。候補行が少ない場合に有効。ランダムアクセス
	Bitmap scan	ビットマップを使用してスキャン。インデックスを使ってBitMapを作りORやAND演算に利用する
	Index only scan	問い合わせがインデックスに含まれるカラムのみでのスキャン。テーブルにアクセスしないので高速だが、Visibility Mapが有効でないとだめ
	Tid scan	検索結果がタプルID(ctid)のスキャン。ctid(行の物理的位置)を条件に設定した場合に有効
その他スキャン	Function scan	関数がデータを収集した結果をスキャン
テーブルの結合	Nested Loop	Nested Loop結合を行う(後述)
	Merge Join	ソート・マージ結合を行う
	Hash Join	ハッシュ結合を行う
検索結果に対し作用	Group	GROUP BY
	limit	LIMIT、OFFSET
	Uniq	DISTINCT
	Aggregate	COUNT、SUM、MAX…
	Group Aggregate	集合関数にGROUP BYを適用してより大きな結果セットを得る
	Result	非テーブルの問い合わせ
	Append	UNION
	SetOp	INTERSECT(積)、EXCEPT(和)
そのほかの処理補助	Sort	明示的なORDER BY、暗黙的なSort-Merge Joinなど

プランナーの動作：問い合せ最適化インサイド

プランナーの中身の動作がわかりやすくまとまっています。

NestLoop：二重ループ

• 外側1行毎にループを1週
• 外側ループ回数が少ない場合に有効
• 内側ループはIndexScanが望ましい

MergeJoin

• ソート済の2つのテーブルを同時にスキャン
• 双方の行数が多い場合に有効
• B−treeインデックスがあると望ましい

HashJoin

• 内側ループのハッシュを作成、外側ループの行と突き合わせ
• ハッシュがメモリに収まる場合に有効

Sort

• データが作業メモリ(work_mem)に含まれればクイックソート
• 作業メモリに収まらなければマージソート。DISK I/Oが発生するため低速

Hash

• ハッシュ関数によって型値を整数化

Aggregate

• 複数行を1行に集約
• GROUP BYやDISTINCT

😸 Indexを貼るコツ

• 複合INDEXを貼るときは、極力UNIQUEになるように
• ある程度汎用的に作る、あまり使わない条件は含めない
• 複合インデックスにsort順を含める際は最後のカラムにする
• INDEXを貼るときはカラム型の見直し、SQLのチューニングも合わせてやる
• 更新頻度が多いテーブルにはあまり貼らない(Indexの更新コストを意識する)
• IndexはNULL値を含まないので注意

🏈 PostgreSQL 9.6対応ドキュメント

PostgreSQL 9.6対応の200ページ以上の電子書籍的なドキュメント。説明も丁寧でわかりやすいです。

PostgreSQL_Internals_1_for_PostgreSQL96_ja_20170211-1.pdf

🎳 参考リンク

• PostgreSQLの実行計画を読み解くための参考資料集
• PostgreSQLスキルアップノート
• 新しい実行プラン・タイプによるPostgreSQL 8.1の性能向上
• インデックスオンリースキャンを試す
• MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け）

🖥 VULTRおすすめ

「VULTR」はVPSサーバのサービスです。日本にリージョンがあり、最安は512MBで2.5ドル/月($0.004/時間)で借りることができます。4GBメモリでも月20ドルです。 最近はVULTRのヘビーユーザーになので、「ここ」から会員登録してもらえるとサービス開発が捗ります！

📚 おすすめの書籍