PostgreSQLのプリペアドステートメント、6回目で実行計画が変わる件についての備忘録

PostgreSQL のプリペアドステートメントを使っていると、6回目の実行から急に遅くなることがある。
また、実行計画の内容が途中から変わっていることがある。
知らないとハマるので、備忘録としてまとめておく。

原因

PostgreSQL はプリペアドステートメントの実行回数をカウントしており、
6回目の実行から「カスタム計画」ではなく「汎用計画」を使う可能性がある。

・1〜5回目の実行

カスタム計画（Custom Plan） を使用
実行時のパラメータを考慮し、最適な実行計画を選択

・6回目の実行以降

PostgreSQL が カスタム計画と汎用計画のコストを比較
1～5回目のコストと比較し汎用計画のコストが低いと判断されると、それ以降汎用計画（Generic Plan）を使用
要するに、PostgreSQL は「カスタム計画を毎回作るより、汎用計画を使ったほうがトータルで効率的」と判断することがある。

実例

テスト環境作成
結果をわかりやすくするために極端なレコード分布とする

-- テスト用TBL作成
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    category_id INT,
    name TEXT
)

-- INDEX作成
CREATE INDEX idx_category ON test(category_id);

-- 大量データ（カテゴリ 1 に5万レコード）
INSERT INTO test (category_id, name)
SELECT 1, md5(random()::text) FROM generate_series(1, 50000);

-- 少数データ（カテゴリ 2-9 各1レコード）
INSERT INTO test (category_id, name)
SELECT generate_series(2, 9), '少数データ';

PREPARE文作成

PREPARE stmt (int) AS
SELECT * FROM test WHERE category_id = $1;

下記条件で6回実行

EXPLAIN ANALYZE EXECUTE stmt(1);

1～5回目の実行計画

Seq Scan on test  (cost=0.00..1093.10 rows=50003 width=40) (actual time=0.007..2.231 rows=50000 loops=1)
  Filter: (category_id = 1)
  Rows Removed by Filter: 8
Planning Time: 1.029 ms
Execution Time: 2.986 ms

6回目以降の実行計画

Index Scan using idx_category on test  (cost=0.29..385.18 rows=12502 width=40) (actual time=0.014..2.668 rows=50000 loops=1)
  Index Cond: (category_id = $1)
Planning Time: 0.075 ms
Execution Time: 3.435 ms

対策

1. plan_cache_mode を force_custom_plan にする

PostgreSQL 14 以降なら、カスタム計画を強制できる。

SET plan_cache_mode = force_custom_plan;

これを設定すれば、毎回カスタム計画が使われる ので、意図しない汎用計画の切り替えを防げる。

2. プリペアドステートメントを使わない

postgreSQLのバージョンによっては上記が使用できない場合がある。
そもそもの実装内容を変更することで防ぐこともできる。

まとめ

PostgreSQL のプリペアドステートメントは、6回目の実行から汎用計画に切り替わる可能性がある
対策として plan_cache_mode = force_custom_plan を設定するか、プリペアドステートメントの使用を見直す
PostgreSQL の仕様を知らずにプリペアドステートメントを使うと、思わぬパフォーマンス低下にハマることがある。
実行計画を確認しながら、適切な方法を選ぶのが大事。