PostgreSQL 16からプリペアド文の汎用的な実行計画を確認できるようになりました EXPLAIN (GENERIC_PLAN)

#PostgreSQL

quiver

2023.05.31

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

PostgreSQL 16からプリペアド文の汎用実行計画を EXPLAIN (GENERIC_PLAN) で確認できるようになりました(3c05284d83)。

この機能をご紹介します。

test=> select version();
                                                  version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 16beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

test=> EXPLAIN (GENERIC_PLAN) SELECT * FROM test WHERE id = $1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on test  (cost=0.00..41.88 rows=13 width=4)
   Filter: (id = $1)
(2 rows)

プリペアド文とは?

PostgreSQLでは、パラメータが異なるだけのSQLを効率よく実行するために、構文解析結果や実行計画をキャッシュして再利用するプリペアド文という仕組みがあります。

test=> CREATE TABLE test(id int);
CREATE TABLE

test=> PREPARE stmt AS SELECT * FROM test WHERE id = $1; -- $1 はバインド変数
PREPARE

test=> EXECUTE stmt(1);  -- $1 に 1 を指定
 id
----
(0 rows)

プリペアド文の実行計画は以下の2種類があります。

カスタム計画(custom plan)
- バインド変数の値を基に実行のたび(=キャッシュされない)に実行計画を生成
汎用計画(generic plan)
- バインド変数の値に依存せずに実行計画を生成しキャッシュ

このプリペアド文は、force_generic_plan や force_custom_plan のようにプランを強制するオプションを有効にしない限り、最初の5回の実行はカスタム計画が選択され、6回目以降はそれまでのカスタム計画の平均と汎用計画を天秤にかけて、コストの小さい計画が選択されます。

今回のアップデートは、6回目以降で用いられる汎用計画を簡単に確認できるというものです。

Ref : カスタムプランと汎用プラン | ドクセル by @fujii_masao

PostgreSQL 15までのプリペアド文の汎用実行計画を確認する方法

バインド変数が確定しているプリペアド文の実行計画を確認するのは簡単です。

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE id = 1;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..41.88 rows=13 width=4) (actual time=0.004..0.005 rows=0 loops=1)
   Filter: (id = 1)
 Planning Time: 0.044 ms
 Execution Time: 0.020 ms
(4 rows)

test=> EXPLAIN ANALYZE EXECUTE stmt(1);
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..41.88 rows=13 width=4) (actual time=0.004..0.004 rows=0 loops=1)
   Filter: (id = 1)
 Planning Time: 0.060 ms
 Execution Time: 0.017 ms
(4 rows)

一方で、バインド変数が確定していないパラメーター付きのプリペアド文の場合、汎用実行計画を確認できませんでした。

test=> EXPLAIN ANALYZE SELECT * FROM test WHERE id = $1;
ERROR:  there is no parameter $1
LINE 1: EXPLAIN ANALYZE SELECT * FROM test WHERE id = $1;
                                                      ^

そのため

force_generic_plan を有効にして、汎用計画を強制する
バインド変数に NULL を指定して汎用とみなす

といった回避策がとられていました。

PostgreSQL 16 から EXPLAIN (GENERIC_PLAN) が登場

PostgreSQL 16 からは、EXPLAIN コマンドが拡張され、汎用計画を取得する EXPLAIN (GENERIC_PLAN) が追加されました。

GENERIC_PLAN

Allow the statement to contain parameter placeholders like $1, and generate a generic plan that does not depend on the values of those parameters. See PREPARE for details about generic plans and the types of statement that support parameters. This parameter cannot be used together with ANALYZE. It defaults to FALSE.

https://www.postgresql.org/docs/16/sql-explain.html

test=> EXPLAIN (GENERIC_PLAN) SELECT * FROM test WHERE id = $1;
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on test  (cost=0.00..41.88 rows=13 width=4)
   Filter: (id = $1)
(2 rows)

ドキュメントにもあるように、GENERIC_PLAN は ANALYZE と併用できません。 ANALYZE は実際にクエリを実行する必要があり、一方で、GENERIC_PLANはパラメーターが確定していないためにクエリ実行できないからです。

test=> EXPLAIN ANALYZE (GENERIC_PLAN) SELECT * FROM test WHERE id = $1;
ERROR:  syntax error at or near "GENERIC_PLAN"
LINE 1: EXPLAIN ANALYZE (GENERIC_PLAN) SELECT * FROM test WHERE id =...

また、(GENERIC_PLAN) の前後のカッコを省略すると、シンタックスエラーになります。ご注意ください。

test=> EXPLAIN ANALYZE GENERIC_PLAN SELECT * FROM test WHERE id = $1;
ERROR:  syntax error at or near "GENERIC_PLAN"
LINE 1: EXPLAIN ANALYZE GENERIC_PLAN SELECT * FROM test WHERE id = $...