When working with PostgreSQL, many developers assume that once a prepared statement is created, the database immediately reuses the same execution plan for every call. While that seems intuitive, PostgreSQL actually follows a more nuanced strategy. This behavior is designed to balance planning overhead with execution efficiency — and it means that prepared statements don’t always reuse query plans by default.
The Role of Prepared Statements
Prepared statements allow you to separate query parsing and planning from execution. The main benefit is performance: once a query is planned, reusing that plan should, in theory, save time. However, in PostgreSQL’s Prepared statements, whether a plan is reused or not depends on both the query and its execution history.
The Learning Phase: First Five Executions
When you first execute a prepared statement, PostgreSQL doesn’t jump straight into reusing a cached plan. Instead, it goes through a “learning phase” that lasts for the first five executions:
- Executions 1 through 5: A new custom plan is generated every time.
- Each custom plan is tailored specifically for the parameter values supplied during that execution.
- The planner tracks the estimated cost of these plans but does not reuse them yet.
At this stage, there’s no plan reuse at all — PostgreSQL is gathering enough information to make a smart decision later.
The Decision Point: Execution Six
On the 6th execution, PostgreSQL evaluates whether switching to a generic plan makes sense:
- The system compares:
- The average cost of the five previous custom plans, and
- The cost of a single generic plan.
- Scenario A – Generic plan wins:
If the generic plan is cheaper overall, PostgreSQL starts reusing it from the 6th execution onward. Every future execution (7, 8, 9, …) uses that same cached generic plan. - Scenario B – Custom plan stays cheaper:
If custom plans prove more efficient, PostgreSQL sticks with them. Each new execution will trigger planning again, and no plan reuse ever happens for that prepared statement.
Controlling the Behavior with plan_cache_mode
You don’t have to rely entirely on PostgreSQL’s heuristics. The plan_cache_mode parameter lets you override this behavior for a session:
- SET plan_cache_mode = 'auto'; (default) — Uses the learning phase + decision process described above.
- SET plan_cache_mode = 'force_custom_plan'; — Always creates a fresh custom plan for every execution.
- SET plan_cache_mode = 'force_generic_plan'; — Uses a generic plan right from the first execution, skipping the learning phase.
When working with PostgreSQL, many developers assume that once a prepared statement is created, the database immediately reuses the same execution plan for every call. While that seems intuitive, PostgreSQL actually follows a more nuanced strategy. This behavior is designed to balance planning overhead with execution efficiency — and it means that prepared statements don’t always reuse query plans by default.
The Role of Prepared Statements
Prepared statements allow you to separate query parsing and planning from execution. The main benefit is performance: once a query is planned, reusing that plan should, in theory, save time. However, in PostgreSQL’s Prepared statements, whether a plan is reused or not depends on both the query and its execution history.
The Learning Phase: First Five Executions
When you first execute a prepared statement, PostgreSQL doesn’t jump straight into reusing a cached plan. Instead, it goes through a “learning phase” that lasts for the first five executions:
- Executions 1 through 5: A new custom plan is generated every time.
- Each custom plan is tailored specifically for the parameter values supplied during that execution.
- The planner tracks the estimated cost of these plans but does not reuse them yet.
At this stage, there’s no plan reuse at all — PostgreSQL is gathering enough information to make a smart decision later.
The Decision Point: Execution Six
On the 6th execution, PostgreSQL evaluates whether switching to a generic plan makes sense:
- The system compares:
- The average cost of the five previous custom plans, and
- The cost of a single generic plan.
- Scenario A – Generic plan wins:
If the generic plan is cheaper overall, PostgreSQL starts reusing it from the 6th execution onward. Every future execution (7, 8, 9, …) uses that same cached generic plan. - Scenario B – Custom plan stays cheaper:
If custom plans prove more efficient, PostgreSQL sticks with them. Each new execution will trigger planning again, and no plan reuse ever happens for that prepared statement.
Controlling the Behavior with plan_cache_mode
You don’t have to rely entirely on PostgreSQL’s heuristics. The plan_cache_mode parameter lets you override this behavior for a session:
- SET plan_cache_mode = 'auto'; (default) — Uses the learning phase + decision process described above.
- SET plan_cache_mode = 'force_custom_plan'; — Always creates a fresh custom plan for every execution.
- SET plan_cache_mode = 'force_generic_plan'; — Uses a generic plan right from the first execution, skipping the learning phase.
This flexibility allows you to fine-tune behavior for specific workloads, where you already know whether parameter-specific planning is necessary.
Under the Hood: How PostgreSQL Implements This
This behavior isn’t just theory — it’s encoded directly into PostgreSQL’s source code. The logic that controls when a prepared statement can reuse a query plan is found in the file:
src/backend/utils/cache/plancache.c, inside the function choose_custom_plan.
Here’s the relevant snippet:
/* Generate custom plans until we have done at least 5 (arbitrary) */
if (plansource->num_custom_plans < 5)
return true;
This check enforces the “five custom executions first” rule. Only after num_custom_plans reaches 5 will PostgreSQL consider comparing the average cost of those custom plans against the generic plan cost.
If the generic plan is found cheaper, PostgreSQL switches to reusing it from the 6th execution onward. Otherwise, it continues generating custom plans indefinitely.
Why This Matters
Understanding this lifecycle is critical for performance tuning:
- Queries with highly selective parameters may always benefit from custom plans, even if that means more planning work.
- Queries where parameters don’t change the cost much are good candidates for generic plans.
- Knowing when PostgreSQL chooses one approach over the other can help you avoid surprises when performance doesn’t improve the way you expect after switching to prepared statements.
Prepared statements in PostgreSQL don’t simply “cache once and reuse forever.” Instead, they follow a measured approach: test with custom plans for five executions, decide on the 6th whether a generic plan is more efficient, and proceed accordingly.
This behavior ensures that PostgreSQL remains adaptive — avoiding inefficient plans in cases where parameter values heavily influence query performance, while still reaping the benefits of reuse when a generic plan is good enough.
This flexibility allows you to fine-tune behavior for specific workloads, where you already know whether parameter-specific planning is necessary.
Under the Hood: How PostgreSQL Implements This
This behavior isn’t just theory — it’s encoded directly into PostgreSQL’s source code. The logic that controls when a prepared statement can reuse a query plan is found in the file:
src/backend/utils/cache/plancache.c, inside the function choose_custom_plan.
Here’s the relevant snippet:
/* Generate custom plans until we have done at least 5 (arbitrary) */if (plansource->num_custom_plans < 5) return true;
This check enforces the “five custom executions first” rule. Only after num_custom_plans reaches 5 will PostgreSQL consider comparing the average cost of those custom plans against the generic plan cost.
If the generic plan is found cheaper, PostgreSQL switches to reusing it from the 6th execution onward. Otherwise, it continues generating custom plans indefinitely.
Why This Matters
Understanding this lifecycle is critical for performance tuning:
- Queries with highly selective parameters may always benefit from custom plans, even if that means more planning work.
- Queries where parameters don’t change the cost much are good candidates for generic plans.
- Knowing when PostgreSQL chooses one approach over the other can help you avoid surprises when performance doesn’t improve the way you expect after switching to prepared statements.
Prepared statements in PostgreSQL don’t simply “cache once and reuse forever.” Instead, they follow a measured approach: test with custom plans for five executions, decide on the 6th whether a generic plan is more efficient, and proceed accordingly.
This behavior ensures that PostgreSQL remains adaptive — avoiding inefficient plans in cases where parameter values heavily influence query performance, while still reaping the benefits of reuse when a generic plan is good enough.