ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQLの実行計画を読み解くための参考資料集

はじめに

PostgreSQLは商用DBに比べて書籍が少なく、まとまった情報が入手しにくいです。また、有志の方がPostgreSQLに関する資料を公開していますが、散在しており、せっかくの有益な情報にアクセスしにくい状況にあります。

そこで、本エントリではPostgreSQLの実行計画に焦点を絞り、公開されている有用な資料(書籍含む)をまとめました。読み返したい資料を探しやすくするため、内容のポイントも併せて紹介してます。

本エントリをきっかけに、これらの資料がさらに活用されることを願っています。

前提

各資料の前提としているPostgreSQLのバージョンは異なることにご注意ください。調査対象のPostgreSQLのバージョンが異なれば、状況は変わっているかもしれません。

各資料には内容の重複があり、ほぼ同一内容の場合もあります。重複している内容についてはポイントから割愛することがあります。

資料を読み進めるのにお勧めの順で並べています。このため、シリーズ物の資料でも順序が入れ替わることがあります。

実行計画の解説資料

公式ドキュメント

https://www.postgresql.jp/document/9.6/html/

最新で信頼できる一番のドキュメントは公式ドキュメントです。他の資料を読む前に、まずは該当バージョンの関連章に目を通すことをお勧めします。

上記リンク先は PostgreSQL 9.6 ですが、右上の「他のバージョンの文章」から過去バージョンのページへ移動できます。

実行計画と特に関連の深い章を以下に挙げます。

EXPLAIN
EXPLAIN コマンドと利用可能なオプションの説明があります。

第14章 性能に関するヒント
実行計画の読み解き方とチューニングのヒントがまとめられています。
実行計画を理解する上で、この章はとても重要です。途中で理解できないことがあっても、まずは一通り目を通すことをお勧めします。この後に紹介する資料を読み込んでから改めて本章を読むと、新たな発見があるかもしれません。

67.1. 行数推定の例
プランナの行数推定について説明しています。行数の推定と実際に差があるとき、なぜそのような差が生まれるのかを理解するために、本章の解説が重要となります。

  • テーブル全体の行数推定
  • 度数分布と頻出値(MCV)を用いた選択度の決定
  • 複数の条件を組み合わせた選択度
  • 結合の行数推定

第11章 インデックス
実行計画で適切なインデックスが選択されているかを判断するために、PostgreSQLではどのようなインデックスがあり、それがどのような条件で適用されるかを理解しておきましょう。

PostgreSQLクエリ実行の基礎知識 ~Explainを読み解こう~

実行計画のコスト計算を中心に、わかりやすくまとめています。
資料のデザインはシンプルで、とても読みやすいです。

  • クエリ実行の概要
  • 実行計画の具体的な読み方
  • プラン演算子の説明とコスト計算式
  • 実行プランのヒント機能

PostgreSQL:行数推定を読み解く

PostgreSQLのマニュアルに記載されている行数推定のアルゴリズムをわかりやすく解説しなおしています。
また、複合条件で行数推定が大幅にずれることのある理由を、条件の独立性の観点から説明しています。PostgreSQLマニュアルにも「プランナは2つの条件が独立していると仮定」と記載していますが(67.1. 行数推定の例)、それが何を示すのかをより明確にしています。

  • 行数推定のアルゴリズム
  • 複合条件の独立性に基づく行数推定と実際の乖離

Explaining EXPLAIN 第2回

https://www.postgresql.jp/sites/default/files/2016-12/Explain%E8%AA%AC%E6%98%8E%E8%B3%87%E6%96%99%EF%BC%88%E7%AC%AC20%E5%9B%9E%E3%81%97%E3%81%8F%E3%81%BF%E5%8B%89%E5%BC%B7%E4%BC%9A%EF%BC%89.pdf

プラン演算子の処理及びコスト計算を詳しく説明しています。

  • プラン演算子
    • 処理概説
    • コスト計算
  • work_mem と lossy storage モード
  • 実行プランの強制

本資料で取り上げられているプラン演算子の一覧表を抜粋します。

演算子 関連処理
Seq Scan 表スキャン
Index Scan 索引スキャン
Bitmap Index Scan 索引スキャン
Bitmap Heap Scan 索引スキャン
Subquery Scan 副問合せ
Tid Scan ctid = ...
Function Scan 関数スキャン
Nested Loop 結合
Merge Join 結合
Hash Join 結合
Sort ORDER BY
Hash
Result 関数スキャン
Unique DISTINCT, UNION
Limit LIMIT, OFFSET
Aggregate count, sum, avg, stddev
Group GROUP BY
Append UNION
Materialize 副問合せ
SetOp INTERCECT, EXCEPT

問合せ最適化インサイド

少し古い資料(2006年)となりますが、プランナの動作をコンパクトかつわかりやすくまとめています。

  • Executor概要
  • Planner
    • 前処理
      • 式の簡略化
      • 式の事前評価
      • 関数出力の安定性
    • パス探索
    • 後処理
      • パスの選択と実行計画への変換
  • 最適化
    • 情報
      • 統計情報 (Statistics)
      • 選択度 (Selectivity)

PostgreSQL実行計画のハッシュノードに出力されるバケット数とバッチ数の解説

http://taityo-diary.hatenablog.jp/entry/2017/05/29/055949

PostgreSQLの実行計画のハッシュノード(Hash)に出力されるバケット数(Buckets)とバッチ数(Batches)について、解説を行っています。

データベースパフォーマンスアップの教科書 基本原理編

データベースパフォーマンスアップの教科書 基本原理編

データベースパフォーマンスアップの教科書 基本原理編

DB(この本ではPostgreSQLに限定していません)がどのように処理を行うかを解説しています。処理の中身を理解することで、実行計画のコスト計算式の背景を理解したり、実行計画の妥当性を判断したりできるようになります。

使ってみませんか?pg_hint_plan

PostgreSQLでHINT句を使えるようにする外部モジュール(pg_hint_plan)の紹介です。外部モジュールのため、本番環境で導入するハードルは高いかもしれませんが、調査及び検討してみてはいかがでしょうか。

pg_hint_plan プロジェクトページは以下にあります。
https://ja.osdn.net/projects/pghintplan/

pg_hint_plan 1.1.0 ドキュメントはwebで公開されています。
http://pghintplan.osdn.jp/pg_hint_plan-ja.html
なお、1.2 のドキュメントはモジュールのパッケージ内に含まれています。

より深く知るオプティマイザとそのチューニング

オプティマイザをコスト計算式(コストモデル)の観点から説明しています。
数式が多めなため、苦手な方は他の資料を優先して確認するとよいかもしれません。

Explaining Explain ~ PostgreSQLの実行計画を読む ~

http://lets.postgresql.jp/documents/technical/query_tuning/explaining_explain_ja.pdf/view

"Explaining Explain"シリーズ(全3回)の1回目に当たります。
他資料と内容の重複が多いため、知識の整理を兼ねて読んでみてはいかがでしょうか。

Explaining Explain 第3回

https://www.postgresql.jp/sites/default/files/2016-12/Explaining_Explain_3.PDF

第1回及び第2回の資料をベースに、実行計画のサンプルを用いて説明しています。

PostgreSQLの実行計画を読み解こう -PostgreSQL SQLチューニング入門-

https://www.slideshare.net/satoshiyamada71697/postgresql-45223468

他資料と内容の重複が多いため、知識の整理を兼ねて読んでみてはいかがでしょうか。

全般的な解説資料

実行計画に特化していませんが、章を割いて解説していたり、参考になる情報が掲載されている資料を紹介します。

PostgreSQL Internals (1) PostgreSQL 9.6 対応版

http://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_Internals_1_for_PostgreSQL96_ja_20170211-1.pdf

通称「篠田の虎の巻」の一つ、"PostgreSQL Internals (1) PostgreSQL 9.6 対応版"です。PostgreSQLの内部構造について、マニュアルに記載のない動作に関する情報も含めてまとめられています。実行計画についても触れられており、最新の情報が反映されています。

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 Software Design plus

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

PostgreSQL 9.3 をベースに、基本編・設計/計画編・運用編・チューニング編に分けて解説しています。この内1章を実行計画に割いて解説しています。

SQLパフォーマンス詳解

http://use-the-index-luke.com/ja

SQLのインデックスとチューニングについて、ベンダ毎(もちろん、PostgreSQLも含まれます)に解説を行っています。

SQLパフォーマンス詳解」にはweb版と書籍版があり、上記リンク先はweb版となります。書籍版を購入の際は以下のエントリも参照ください。
「SQLパフォーマンス詳解」(原文タイトル:SQL Performance Explained)の紹介と購入時のポイント - ぱと隊長日誌

PostgreSQL Wiki 日本語版

https://wiki.postgresql.org/wiki/Main_Page/ja

英語圏のコミュニティによって作成されたPostgreSQLの記事を和訳で読むことができます。

Let's Postgres

http://lets.postgresql.jp/

PostgreSQL全般の記事がまとめられています。