PostgreSQL における実行計画の制御方法 7 種 + α

PostgreSQL 実行計画 DB

概要

可能な限り PostgreSQL の選択する実行計画にまかせておきたいものではありますが、スロークエリの実行計画を確認した結果、手動制御の必要が発生する場合も残念ながらしばしばです。そのような場合の際し、PostgreSQLで利用可能な手動による実行計画の制御方法をいくつか挙げます。

紹介する手法は次の通りです。

  1. 統計情報を最新化する
  2. ヒント句を使う
  3. 統計情報を固定化する
  4. enable_XXX パラメータを設定する
  5. join_collapse_limit パラメータを調整し、結合順を明示的に指定する
  6. インデックスを張る
  7. クエリを修正する
  8. その他

1. 統計情報を最新化し、実態に即した情報とする

この手法で解決する見込みは薄いとは思いますが、後の切り分けの便のためにも次のようなケースの可能性は早めに潰しておくと良いです。

  • クエリの実行が（大量の）データの投入直後で、統計情報の自動更新が間に合っていない
  • 自動バキューム機能が無効化されていて、統計情報が自動的に更新されていない

統計情報が実際のデータ状況と乖離している、という点がポイントです。このような状態は実行計画における取得行数の予実の乖離として現れることが多いでしょう。

似た状態ですが難しい問題として、短い間隔において大きなデータ量の増減がある、といったものがあります。自動バキュームの頻度増加や、処理直前に手動で統計情報を最新化するといった方法で対応できれば良いですが、しばしばそれが現実的に間に合わず何かしらの手立てを求められます。どのような手立てを講じるかは要件次第で、これ以降に紹介する手法を採用することもあるでしょう。

2. ヒント句を利用する

PostgreSQL で実行計画の制御と言えばよく出てくるヤツです。

PostgreSQL の周辺ツールの 1 つに pg_hint_plan というモジュールがあります。これを有効化し、クエリにヒントを付与することで実行計画を制御できるようになります。 この手法の特徴は以下の通りです。

  • 外部モジュールのインストール・有効化が必要になる
  • クエリ単位で実行計画の制御を行う
  • （基本）ヒントはコメント内に書くため、SQL の処理自体には手を入れる必要がない
  • 実行計画を直接制御する

上で挙げた特徴について詳細に触れます。

  • まずは外部モジュールという点です。初めにシステムの要件を満足するか確認する必要があります。つまり、PostgreSQL 環境に新たにモジュールを追加する許可が得られるか、利用中の PostgreSQL のバージョンに対応しているか、アプリケーションがコメントでヒントを指定することができる仕組みか、またはマネージドサービスを利用している場合はそもそも本モジュールに対応しているか、などです。これをクリアできない場合、そもそも利用が出来ないので本手法は検討から除外されます。
    • 補足ですが、pg_hint_plan は指定したいヒントを、実行計画を制御したいクエリと併せてヒント用のテーブルに登録する方法を提供しています。この場合、クエリの文字列表現さえ把握できれば自身では変更不可能な SQL でも実行計画を制御することが可能です。
  • 次はクエリ単位の制御という点です。良い面を見ると影響を及ぼす範囲を局所化できるということですが、悪い面を見ると修正が必要なクエリの数に比例して作業量が増加するということです。すでに修正が必要だとわかっている、およびこれからわかりそうなクエリの概数は重要な情報になります。
  • 最後に実行計画を直接制御するという点です。これはうまく使えば目的とする実行計画を実現しやすい一方、うまく使う技術をエンジニアに要求する面があります。少なくとも、PostgreSQLでクエリ処理の際に利用される各アルゴリズム（hashjoinやseqscanなど）の理解が追加で求められます。つまり、一般に技術的に難しい作業となる傾向が強いということです。

参考文献

3. 統計情報を固定化する

これもよく出てくるヤツです。今執筆中に適当に「PostgreSQL 実行計画」みたいにググったら高い順位でヒットしました。実行計画の制御手法の 1 つと捉えて、特徴を踏まえて他の手法と比較検討できるよう頭の中で整理するのが収まりが良いかと思います。

概要としては、うまくいっていた時の統計情報を取得し、クエリの処理の際にはその取得した統計情報を参照させることでデータ量の変動によらずある一定の実行計画を選択させる、というものです。典型的な利用ケースとしては徐々にデータ量が積み重なることで、ある一定のしきい値を超えたときに実行計画が変わり、パフォーマンスが急落するといったことを防ぐ、というようなものがあります。

実現方法としては pg_dbms_stats という PostgreSQL の周辺ツールを利用します。次に示す特徴もこのツールの使用を前提とします。 （未検証ですが、統計情報の更新をある時点で停止する等、他の実現方法も存在するかもしれませんが。）

  • 外部モジュールのインストール・有効化が必要になる
  • DB オブジェクト単位の統計情報の操作により、間接的に実行計画に影響を与える（統計情報の固定が可能なオブジェクト種類など、詳細はドキュメント参照）
  • DB に対する設定のため、SQL に手を入れる必要がない

各特徴についてです。

  • まず外部モジュールなので、例に漏れずインストール・有効化が必要になります。上のヒント句の項目でも述べたように、そもそも利用可能かどうかという点は確認しましょう。例えば、記事執筆時点(20200401)で少なくとも AWS の RDS で使えないことは判明しています（一方 pg_hint_plan は利用可能）。
  • 2 点目ですが、ポイントとなるのはクエリ単位の操作でないということです。つまり、そのオブジェクトを利用するすべてのクエリに影響を与えます。基本クエリ単位よりも粒度の粗い操作になるでしょう。この点も良し悪しですが、オブジェクトとしてはデータベース、スキーマ、テーブル、列のいずれかが選択可能なので、ある程度対象のコントロールは可能です。良しに転ぶように検討する余地はあるでしょう。

参考文献

4. enable_XXX パラメータを設定する

少しニッチな話になってきます。

PostgreSQL の設定可能なパラメータの中に「enable_hashjoin」や「enable_seqscan」のように、enable という接頭辞にもつパラメータあります。これらのパラメータは、PostgreSQL が問い合わせを処理する際に該当のアルゴリズム（結合であれば NestedLoop や HashJoin）を利用できるか否か、を制御します。デフォルトでは全て on となっていて、これはプランナがすべての結合手法やデータ走査手法を利用できることを意味します。

ただし、例えば明らかにハッシュ結合が選択されるべき箇所でネステッドループ結合が選択されているような、効率の悪いアルゴリズムが選択されることあります。そのような場合にこのパラメータを設定することで対象のアルゴリズムが実行計画に入り込むことを抑制することが出来ます。先の例では「enable_nestedloop = off」とすることでネステッドループ結合以外の結合手法を利用するよう、プランナを直接仕向けることができます。

特徴として次のような点があります。

  • 外部モジュールが必要ない
  • セッション、もしくは DB 全体をスコープとする設定となる
    • セッション単位で制御する場合、SQL 文の実行により設定の ON/OFF をスイッチする
  • DB に対する設定のため、SQL に手を入れる必要がない

特徴についてもう少し。

  • 大きな特徴なスコープです。本パラメータは postgresql.conf で設定する他のパラメータと同じく、グローバルに適用するか、あるいはセッション単位で適用するかです。小さくともセッション単位での制御になるので、ある点大雑把な制御になります。この点は例えばセッション中で元のパラメータに戻す、といったことは可能ですが、追加のクエリ文の発行（SETコマンド）が必要になるので、アプリケーションの作りとしてそういった操作が可能かどうかは考慮する必要があります。アプリケーションの特性と相談しつつ、採用の可否を判断するとよいでしょう。

参考文献

5. join_collapse_limit パラメータを調整し、結合順を明示的に指定する

さらにもう少しニッチな話になります。

PostgreSQL では、カッコ（）を使うことでクエリに結合順を明示することができます。ただし、デフォルトの設定では PostgreSQL は可能な結合順の組み合わせの全探索（テーブル数が多いと遺伝的探索）を試み、最小のコストで実行可能な結合順を選択するため、カッコを記載しても効果があるとは限りません。PostgreSQL 文書で「すべての JOIN 句に対してとりあえず結合順を制限させることができます」と表現されている内容です。

この、クエリに対し明示した結合を強制するには「join_collapse_limit」パラメータの値を 1 に設定します。この設定により、プランナは結合順に関する探索を行わず、クエリに記載された順序で結合を処理する計画を選択するようになります。

特徴としては次のような点があります。

  • 外部モジュールが必要ない
  • セッション、もしくは DB 全体をスコープとする設定となる
  • クエリ中に出現するテーブル全ての結合順を考慮する必要がある

特徴についてもう少し。

  • スコープについては enable_XXX の場合と同様です。
  • 最後に挙げた点です。「クエリに記載した順序で結合を処理する」ということは、もちろん逆にいうとすべてのテーブルの結合順に関して人間側で面倒を見てやらなければならないということです。これは SQL 一般の知識を超え、各テーブルのデータ量やカーディナリティに関する勘所が求められるということでもあります。よって、一般的に難易度およびコストが高い対応方法になる可能性が高いので、用意できるリソースも含めて検討するとよいでしょう。

以下、結合順を明示的に指定したクエリと、実行計画の変化例です。

  • クエリ（パラメータ指定なし : join_collapse_limit = 8 の状態） -> クエリ上は b, c の結合を先に指定している。
explain
select *
from A a join (B b join C c on b.id = c.id) on a.id = b.id;
  • 実行計画 ->　 join_collapse_limit の値を調整していないため、a, b の結合の後に c が結合される計画となっている
Nested Loop  (cost=0.00..1691540.90 rows=288579 width=24)
  Join Filter: ((b.id)::text = (c.id)::text)
  ->  Nested Loop  (cost=0.00..137553.60 rows=25538 width=16)
        Join Filter: ((a.id)::text = (b.id)::text)
        ->  Seq Scan on a  (cost=0.00..32.60 rows=2260 width=8)
        ->  Seq Scan on b  (cost=0.00..32.60 rows=2260 width=8)
  ->  Seq Scan on c  (cost=0.00..32.60 rows=2260 width=8)
  • クエリ（join_collapse_limit パラメータを指定）-> クエリ上は b, c の結合を先に指定している。パラメータの指定のための SET コマンド以外は上のクエリと同じ。
set join_collapse_limit = 1;
explain
select *
from A a join (B b join C c on b.id = c.id) on a.id = b.id;
  • 実行計画 -> クエリに明示した結合順(b,c の結合が先)の計画が選択されている
Nested Loop  (cost=0.00..1691540.90 rows=288579 width=24)
  Join Filter: ((a.id)::text = (b.id)::text)
  ->  Nested Loop  (cost=0.00..137553.60 rows=25538 width=16)
        Join Filter: ((b.id)::text = (c.id)::text)
        ->  Seq Scan on b  (cost=0.00..32.60 rows=2260 width=8)
        ->  Seq Scan on c  (cost=0.00..32.60 rows=2260 width=8)
  ->  Seq Scan on a  (cost=0.00..32.60 rows=2260 width=8)

参考文献

6. インデックスを張る

性能問題に際してインデックスを検討したときに、わざわざ実行計画の変化という観点で考えることはあんまりないんじゃないかとは思いますが、作成したインデックスが新しく利用されるということは実行計画にも変更があることには違いないので挙げておきます。実行計画中 SeqScan を見たとき、インデックスアクセスの可否の確認を簡単に行うとよいでしょう。

特徴や検討内容は、インデックスを考えるときと同様の内容です。

7 .クエリを修正する

クエリの書き方でも実行計画は変化します（項番 5 で書いた内容は本内容の一部とも言えます）。例えば、自テーブルの行間比較においてサブクエリを使っていたところをウィンドウ関数に書き直すことで実行計画を変え、より効率的なクエリとすることができます（この場合、テーブルスキャン回数の削減が期待できる）。

どのようなクエリが非効率で改善の余地があるか、またどのように修正すればいいかという点は千差万別で、それの入門のみで本が 1 冊あるぐらいなので、個々の内容について詳しく紹介はしません（できません...）。

特徴としては難易度が高いほか、SQL（アプリケーション）の修正になるという点が重要かと思います。今まで挙げてきた対応方法はすべて、結果セットなどの実行結果自体には全く影響がないものでした。ただしクエリの修正となると、不適切な修正を入れてしまった場合実行結果が変わることがあります。よってデグレーション回避のため回帰試験が必要になると思われます。逆に言えば DB 側には手を入れずに済むので、パラメータや新しい依存の導入など、典型的にオペレーションを複雑化する要因が入り込まないのは、これは良い点として挙げられるでしょう。

参考文献 mickmack.hatenablog.com

8. その他

力尽きたのでその他、聞いたことくらいはあるけど未検証の方法をまとめていくつか紹介します。

  • AWS の場合、Aurora PostgreSQL のクエリ計画管理機能を利用する
  • プランナコスト定数を調節する
    • プランナが統計情報からコストを見積もる際、読み取りのコストはページ 1 つにつきいくつで、タプル 1 つ（レコード 1 件と思ってください）の処理のコストはいくつでといった具合に、各コスト変数ごとに相対的な処理コストというものが定数で定められています。
    • ただし、実際の処理の相対的なコストは環境によって変動するため、この値を実際に即した数値に設定することで、理論上はより実態に即したコスト見積もりを行わせることが可能です。
      • 例えば SSD は HDD に比べて読み取りが早いため、CPU 処理とのコスト比率では SSD の方は HDD に比べてより小さい数値を設定する、などです。
    • ・・・ただし、この相対的な数値をどうやって測るかが難しい上、効果が間接的なのでどの程度効くのかどうか見通しが立ちづらいこともあり、検討が難しい手法です。
    • https://www.postgresql.jp/document/11/html/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
  • default_statistics_target 設定パラメータの値を大きくする
  • ALTER TABLE SET STATISTICS を使用して、特定の列に対して収集される統計情報を増やす
    • analyze コマンドにより収集・格納される列単位の統計情報に、頻出値や度数分布に関する情報があり、この情報のエントリを最大いくつ保持するかを指定することが可能です。
    • default_statistics_target パラメータを指定することでグローバルに、もしくは ALTER TABLE SET STATISTICS で列単位で細かく設定することが可能です。
    • https://www.postgresql.jp/document/11/html/planner-stats.html

終わりに

まとめて挙げてみると制御方法は案外あるなーって気もしてきますが、実際には技術的、環境的、リソース的な制約のもとで現実的に選択可能な対応方法が限られることになるかと思います。それに対応が可能であっても何かしらの手間の発生は避けられないので、根本的に、適切な統計情報を入力とできるようにアプリを設計し、不必要・非効率な結合を強制しないようなクエリを実装するのを日々心がけるのが良いあり方なのかなと考えています。