---
title: カスタムプランと汎用プラン
tags:  #postgresql #sql実行プラン  
author: [Fujii Masao](https://convert.docswell.com/user/fujii_masao)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/VEN5QN9DJ8.jpg?width=480
description: PostgreSQLのカスタムプランと汎用プランについて説明します。
published: December 16, 17
canonical: https://convert.docswell.com/s/fujii_masao/5981PP-postgresql_custom_and_generic_plans_pgunconf09
---
# Page. 1

![Page Image](https://bcdn.docswell.com/page/VEN5QN9DJ8.jpg)

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


# Page. 2

![Page Image](https://bcdn.docswell.com/page/Y79YDR28E3.jpg)

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


# Page. 3

![Page Image](https://bcdn.docswell.com/page/G7863W5ZJD.jpg)

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


# Page. 4

![Page Image](https://bcdn.docswell.com/page/L7L9VNY1ER.jpg)

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


# Page. 5

![Page Image](https://bcdn.docswell.com/page/4EMZGXN57W.jpg)

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


# Page. 6

![Page Image](https://bcdn.docswell.com/page/PER6ZNDZJ9.jpg)

デモ
●
テーブル作成
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;


# Page. 7

![Page Image](https://bcdn.docswell.com/page/P7XM9N11JX.jpg)

デモ
●
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)


# Page. 8

![Page Image](https://bcdn.docswell.com/page/37KGPN2MED.jpg)

デモ
●
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)


# Page. 9

![Page Image](https://bcdn.docswell.com/page/LJ3NXV42J5.jpg)

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


# Page. 10

![Page Image](https://bcdn.docswell.com/page/8JDW28Q6EG.jpg)

デモ
●
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の汎用プランが選択される。


# Page. 11

![Page Image](https://bcdn.docswell.com/page/VEP918GZ78.jpg)

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


