カスタムプランと汎用プラン

カスタムプランと汎用プラン @fujii_masao

PREPARE ● ● PREPARE文を使うことで、SQLの構文解析結果やプランをPostgreSQLサー バにキャッシュできる 以降、そのキャッシュを利用することで、構文解析やプラン生成の処理をス キップでき、性能改善できる -- PREPARE文でSELECTの構文解析結果やプランを登録 -- $1はバインド変数で、実際の値はEXECUTE時に指定 PREPARE hoge AS SELECT * FROM test WHERE i = $1; -- バインド変数$1に1を指定して、PREPARE結果を利用してSQL実行 EXECUTE hoge(1);

カスタムプランと汎用プラン カスタムプラン (custom plan) ● バインド変数の実際の値を考慮して生成されるプラン ● SELECT * FROM test WHERE i = 1に対してプラン生成されるイメージ ● PREPAREでプランをキャッシュできない ● EXECUTE時に指定されたバインド変数の値を考慮して、毎回プランが生成さ れる 汎用プラン (generic plan) ● バインド変数の実際の値を考慮せずに生成されるプラン ● SELECT * FROM test WHERE i = $1で$1の値が不明なままプランが生成され るイメージ ● PREPAREでプランをキャッシュできる ● バインド変数が不明なまま生成したプランなので、EXECUTE時に指定される 値によっては最適でないプランである可能性がある

PREPAREとプラン選択 ①PREPARE実行後、最初の5回のEXECUTE実行では、カスタムプランが必ず 選択される ➔ つまり、PREPAREでプランをキャッシュしたと思っても、最初5回のSQL実 行では毎回プランは生成される ②6回目以降のEXECUTE実行では、「それまでに実行されたカスタムプランの 平均コスト」と「汎用プランのコスト」が比較され、小さい方のプランが選 択される ● もし汎用プランが選択された場合は、それ以降ずっと汎用プランが選択され る ➔ つまり、この時点からプランはキャッシュされる ③カスタムプランが選択された場合は、それ以降のEXECUTE実行でも②のコス ト比較とプラン選択が行われる ➔ つまり、汎用プランが選択されるまではカスタムプランが選択され続け、プ ランはキャッシュされない

バインド変数がない場合 ● バインド変数がないSQLをPREPAREした場合は、最初のEXECUTEから常に 汎用プランが選択されて、プランはキャッシュされる PREPARE hoge2 AS SELECT * FROM i = 9;

デモ ● テーブル作成 CREATE TABLE test (i int, j int); CREATE INDEX test_idx ON test(i); -- i=1のレコードを1万件格納 INSERT INTO test VALUES (1, generate_series(1, 10000)); -- i=2〜10のレコードを1件ずつ格納 INSERT INTO test VALUES (generate_series(2, 10), 0); VACUUM ANALYZE test;

デモ ● PREPARE PREPARE hoge AS SELECT * FROM test WHERE i = $1; ● EXECUTEを5回実行 EXPLAIN ANALYZE EXECUTE hoge(1); EXPLAIN ANALYZE EXECUTE hoge(1); EXPLAIN ANALYZE EXECUTE hoge(1); EXPLAIN ANALYZE EXECUTE hoge(1); EXPLAIN ANALYZE EXECUTE hoge(1); QUERY PLAN -------------------------------------------------------------------------------------------------------Seq Scan on test (cost=0.00..170.11 rows=10000 width=8) (actual time=0.020..2.639 rows=10000 loops=1) Filter: (i = 1) Rows Removed by Filter: 9 Planning time: 0.171 ms Execution time: 3.298 ms (5 rows)

デモ ● 6回目以降のEXECUTE EXPLAIN ANALYZE EXECUTE hoge(1); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------Index Scan using test_idx on test (cost=0.29..41.80 rows=1001 width=8) (actual time=0.083..10.003 rows=10000 loops=1) Index Cond: (i = $1) Planning time: 0.222 ms Execution time: 11.131 ms (4 rows)

PREPAREと統計情報の更新 ● ➔ 統計情報が更新されると、キャッシュしていた汎用プランが適切でなくなる 可能性があるので、キャッシュはクリアされて、再度カスタムプランと汎用 プランのコスト比較とプラン選択が行われる ②に戻る

デモ ● i=1のレコードを5万件追加してVACUUM ANALYZE INSERT INTO test VALUES (1, generate_series(1, 50000)); VACUUM ANALYZE test; ● EXECUTE EXPLAIN ANALYZE EXECUTE hoge(1); ➔ 最初はSeqScanのカスタムプランだけど、 いずれIndexScanの汎用プランが選択される。

注意 ● ● 一度でもコストが大きい汎用プランが生成されてしまうと、 統計情報が更新されて以前の汎用プランが破棄されても、 そのコストがリセットされずに残ってしまって、 その汎用プランのコストとカスタムプランのコストが比較され、 常にカスタムプランが選択されてしまう問題がある キャッシュされている汎用プランが破棄されるときに、 そのコストがPostgreSQL内部で残ってしまうのが バグかどうかは不明。いずれコミュニティに報告したい