PostgreSQL のプリペアド文で汎用的な実行計画を選択するタイミング

PostgreSQL ではプリペアド文を繰り返し実行していると、汎用的な実行計画を作成することがあります。これにより、実行計画作成のオーバーヘッドを回避できます。

「汎用的な実行計画」と「独自の実行計画」のいずれを使うのかはマニュアルで説明されています。

デフォルト(すなわち、plan_cache_modeがautoに設定されている場合)では、パラメータのあるプリペアド文に対して、汎用的な計画を使うか独自の計画を使うかを、サーバは自動的に選択します。これに対する現在の規則は、最初の5回が独自の計画で実行され、その計画の推定コストの平均が計算される、というものです。それから汎用的な計画が作成され、その推定コストが独自の計画のコストの平均と比較されます。そのコストが独自の計画のコストの平均よりもそれほど高くなければ、再計画を繰り返すことが望ましいと考えて、その後の実行は汎用的な計画を使います。
https://www.postgresql.jp/document/13/html/sql-prepare.html

汎用的な実行計画を選択されたかは EXPLAIN で確認できます。

プリペアド文に対してPostgreSQLが使用する問い合わせ計画を検証するためには、EXPLAIN、例えば
EXPLAIN EXECUTE stmt_name(parameter_values);
を使用してください。汎用的な計画が使用される場合には、$nというパラメータ記号が含まれ、独自の計画が使用される場合は提供されたパラメータの値で置換されます。
https://www.postgresql.jp/document/13/html/sql-prepare.html

実行計画の選択について、マニュアルの説明をよりわかりやすく書き直した記事がありました。

プリペアド文は、汎用的な実行計画を作成し、その実行計画を利用するかどうかをPostgreSQL内部で自動的に判断しています。具体的には以下のように動作します。
1）プリペアド文で準備したSQLを繰り返し実行する時に、最初の5回目までは、毎回、パラメーターに合わせた実行計画（実行計画A1からA5とします）を作成します。
2）6回目以降に実行する時は、パラメーターの値が未設定である汎用的な実行計画（実行計画Bとします）を作成し、今まで作成した実行計画A1からA5までの平均コストと、実行計画Bのコストを比較して、今後、汎用的な実行計画を利用するか否かを判定します。
3）実行計画Bのコストが実行計画Aの平均コストよりもそれほど大きくない場合、それ以降のSQL実行で実行計画を毎回作成せず、汎用的な実行計画（実行計画B）を利用します。
あるいは、実行計画Bのコストが実行計画A1からA5までの平均コストよりもかなり大きい場合は、パラメーターに合わせた実行計画を毎回作成して利用します。
パフォーマンスチューニング9つの技～「探し」について～｜PostgreSQLインサイド : 富士通

これらの説明から、6回目の実行でそれ以降に用いられる実行計画が確定するように読めるかもしれません。ですが、実際には7回目以降でも「独自の実行計画」から「汎用的な実行計画」に切り替わることがあります。

PostgreSQL 14.2 のソースコードから引用します（コメントの除外などを行っています）。
src/backend/utils/cache/plancache.c

static bool
choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
{
    double    avg_custom_cost;

    // 中略

    if (plansource->num_custom_plans < 5)
        return true;

    avg_custom_cost = plansource->total_custom_cost / plansource->num_custom_plans;

    if (plansource->generic_cost < avg_custom_cost)
        return false;

    return true;
}

コードの custom は「独自の実行計画」、generic は「汎用的な実行計画」を指しています。

「独自の実行計画」が選択される度にコストと回数が total_custom_cost と num_custom_plans に加算されます。この平均コストが avg_custom_cost です。

5回目までは「独自の実行計画」が選択されます。

6回目以降は「独自の実行計画」の平均コストと「汎用的な実行計画」のコストを比較します。そこで「汎用的な実行計画」のコストのほうが低ければ、「汎用的な実行計画」が選択されます。この判定は6回目に限らず、7回目以降でも行われます。

一度でも「汎用的な実行計画」が選択されると「独自の実行計画」に戻ることはありません。なぜなら、「汎用的な実行計画」の選択では total_custom_cost と num_custom_plans に加算されず、条件判定で常に「汎用的な実行計画」が選択されるためです。

実際の挙動を確認します。PostgreSQL 14.2 で検証しました。

=# CREATE TABLE tab1 (id integer PRIMARY KEY);

=# INSERT INTO tab1 SELECT generate_series(1, 1000);

=# VACUUM ANALYZE;

=# PREPARE idplan (integer) AS SELECT * FROM tab1 WHERE id > $1;

=# EXPLAIN EXECUTE idplan(0); -- 5回繰り返し
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on tab1  (cost=0.00..17.50 rows=1000 width=4)
   Filter: (id > 0)
(2 rows)

=# EXPLAIN EXECUTE idplan(0); -- 6回目（汎用的な実行計画に切り替わる）
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..14.10 rows=333 width=4)
   Index Cond: (id > $1)
(2 rows)

idplan(0) だけを繰り返すと、6回目で汎用的な実行計画（$1 というパラメータ記号が含まれていることからわかります）に切り替わりました。

=# DEALLOCATE idplan;

=# PREPARE idplan (integer) AS SELECT * FROM tab1 WHERE id > $1;

=# EXPLAIN EXECUTE idplan(999); -- 10回繰り返し
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..4.29 rows=1 width=4)
   Index Cond: (id > 999)
(2 rows)

=# EXPLAIN EXECUTE idplan(0); -- 6回繰り返し
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on tab1  (cost=0.00..17.50 rows=1000 width=4)
   Filter: (id > 0)
(2 rows)

=# EXPLAIN EXECUTE idplan(0); -- 7回目（汎用的な実行計画に切り替わる）
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.28..14.10 rows=333 width=4)
   Index Cond: (id > $1)
(2 rows)

idplan(999) だけを繰り返すと、独自の実行計画のままです。これは汎用的な実行計画よりも独自の実行計画のコストが低いためです。

idplan(999) を10回繰り返した後、 idplan(0) を繰り返すと7回目で汎用的な実行計画に切り替わりました。この結果より汎用的な実行計画の切り替わりは6回目に限らないとわかります。

なお、PostgreSQL 12 からプランナオプションの plan_cache_mode で「汎用的な実行計画」と「独自の実行計画」の選択を制御することもできます。

plan_cache_modeをforce_generic_planまたはforce_custom_planに設定して、サーバにそれぞれ汎用的な計画または独自の計画を使うように強制することで、この発見的手法を置き換えることができます。汎用的な計画の実際のコストが独自の計画のものよりもずっと多い場合でも、汎用的な計画を選べるようになりますので、汎用的な計画のコスト推定が何らかの理由でひどく外れる場合に、この設定は主として有用です。
https://www.postgresql.jp/document/13/html/sql-prepare.html

ぱと隊長日誌

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

PostgreSQL のプリペアド文で汎用的な実行計画を選択するタイミング