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 LIMITOFFSET
Uniq DISTINCT
Aggregate COUNTSUMMAX
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 BYDISTINCT

🏈 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

🚌 参考リンク

📚 おすすめの書籍

🖥 サーバについて

このブログでは「Cloud Garage」さんのDev Assist Program(開発者向けインスタンス無償提供制度)でお借りしたサーバで技術検証しています。 Dev Assist Programは、開発者や開発コミュニティ、スタートアップ企業の方が1GBメモリのインスタンス3台を1年間無料で借りれる心強い制度です!(有償でも1,480円/月と格安)