skip to content

In a PostgreSQL-backed loyalty-points service, a prepared statement is fast for five executions and then turns slow; what changed, and how do you fix it?

level: seniorimportance: should knowfreq 50%

answer

  1. five custom plans first
  2. estimates compared, not timings
  3. $1 in EXPLAIN EXECUTE
  4. plan_cache_mode override

basics

~20 s

After five custom-planned executions, PostgreSQL builds a generic plan and keeps it if its estimated cost is below the custom average. On skewed data that estimate misleads; force_custom_plan, scoped to that role or function, or better statistics fix it.

solid answer

~50 s

With `plan_cache_mode = auto`, PostgreSQL plans the first five executions of a parameterized prepared statement using the actual values. It then builds a generic plan that ignores the values and uses it from then on if its estimated cost is below the average custom-plan cost, which includes a charge for planning. The comparison uses estimates only: on a skewed column, say most ledger rows belonging to a few very active members, the generic plan assumes a typical value, looks cheap, and runs badly for the heavy ones. I confirm it with `EXPLAIN EXECUTE`, where a generic plan shows `$1` instead of the value, and with `generic_plans` and `custom_plans` in `pg_prepared_statements` from the same session. The fix is `plan_cache_mode = force_custom_plan` scoped to that role or function, better statistics, or a separate statement for the heavy case.

code

sql · 12 lines
sql
PREPARE member_balance(bigint) AS
  SELECT sum(points) FROM loyalty_ledger WHERE member_id = $1;

EXECUTE member_balance(17);   -- repeat: runs 1-5 are custom plans

EXPLAIN EXECUTE member_balance(17);  -- '$1' in the plan = generic

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements;

SET plan_cache_mode = force_custom_plan;
EXPLAIN EXECUTE member_balance(17);  -- the value is substituted again

go deeper

for a junior

Recall that prepared statements can run custom plans built for each value or one reusable generic plan.

for a middle

Explain the auto rule: five custom plans, then an estimated-cost comparison, and how EXPLAIN EXECUTE shows which plan is in use.

for a senior

Diagnose a flip on skewed data from the session that owns the statement, and pick a scoped fix: force_custom_plan, statistics, or a split statement.

for a principal

Weigh planning CPU against plan stability across the service, and decide which statements deserve forced custom planning rather than a global switch.

## Custom and generic plans A **prepared statement** is parsed once and executed many times with different parameter values. PostgreSQL creates them from SQL `PREPARE`, from the extended wire protocol when a driver uses named server-side statements, and implicitly for the SQL inside PL/pgSQL functions. Each execution runs one of two plan kinds: - a **custom plan**, made afresh for that execution with the actual parameter values substituted, so the planner can use the statistics for those exact values; - a **generic plan**, made once with placeholders such as `$1`, and reused without planning again. A statement with no parameters always uses a generic plan, since there is nothing to customise. ## The auto rule, step by step Under the default `plan_cache_mode = auto`, the plan cache decides as follows: 1. Executions one to five are planned as **custom plans**, and the cache records each plan's estimated cost plus a charge for the planning itself. 2. On the next execution it builds a **generic plan** and estimates its cost. 3. If the generic plan's estimated cost is **below the average custom-plan cost**, it is used from then on; otherwise custom planning continues. 4. A change to a referenced object's definition, new planner statistics from `ANALYZE`, or a different `search_path` invalidates the cached plan, and the choice is made again with fresh estimates. The decisive detail: every number in step 3 is an **estimate**. Actual run times are never consulted. ## Why the generic plan goes wrong For `member_id = $1`, a generic plan cannot know which member is asked about, so it estimates a typical match count. If a handful of members own a large share of `loyalty_ledger` rows, the typical estimate is small, the generic plan picks an index scan with a nested loop, and its cost looks lower than the average of the five custom plans. For a heavy member that plan reads a large part of the table in random order. The service sees fast calls for five executions and then a step change, sometimes only for some members. In the analogy of a shop that prices a standard parcel once and reuses the label: fine for typical parcels, painful for the one pallet that arrives with the same label. ## Confirming it - `EXPLAIN EXECUTE member_balance(17)`: a generic plan shows `$1` in its conditions, a custom plan shows the value. - `pg_prepared_statements` has `generic_plans` and `custom_plans` counters, but the view lists only the **current session's** statements, so query it from the application's connection or reproduce the sequence in psql. - `EXPLAIN (GENERIC_PLAN)` shows the generic plan for a query text containing `$1` without preparing it; it cannot be combined with `ANALYZE`. - In production, capturing slow executions' plans is a job for query-statistics tooling. ## Fixing it | option | effect | cost | |---|---|---| | `plan_cache_mode = force_custom_plan` | re-plans with real values every execution | planning CPU on every call | | better statistics | custom and generic estimates come closer to reality | may not remove true skew | | split the statement | heavy members get their own statement or query shape | an application change | | `plan_cache_mode = force_generic_plan` | always generic | right only when values do not change the best plan | Two practical points. `plan_cache_mode` is read **when a cached plan is executed, not when it is prepared**, so changing it affects statements that already exist. And it should be scoped narrowly, to the role, function or session that runs the problem statement, rather than server-wide, because many other statements benefit from skipping the planner. Re-preparing the statement is not a fix: the same five-then-generic sequence repeats. ## Why it looks intermittent in a service - **The cache is per session.** Every connection keeps its own prepared statements and its own five-execution count, so a pool of long-lived connections drifts onto the generic plan one connection at a time. - **New connections start fresh.** After a deploy or a pool recycle, the first five calls on each connection are custom-planned again, so latency looks healthy for a while and then degrades. - **PL/pgSQL hides it.** The SQL inside a PL/pgSQL function is prepared implicitly, so a function such as `award_points(member_id)` can flip after its fifth call in a session with no `PREPARE` anywhere in the application code. - **Drivers decide too.** A driver that switches to named server-side statements after some executions adds its own threshold in front of PostgreSQL's five.

  • Why can't a monitoring connection read the application's generic-plan counts from pg_prepared_statements in PostgreSQL?
    The view lists only the prepared statements of the session that queries it. A monitoring connection sees its own statements, not the application's. Read it from the application's connection, reproduce the sequence of executions in psql, or capture the slow executions' plans with query-statistics tooling.
  • When is plan_cache_mode = force_generic_plan the better choice in PostgreSQL?
    When the best plan does not depend on the parameter values, such as a primary-key lookup, but planning is expensive, for example a statement joining many tables, so re-planning wastes CPU. The manual also notes it helps when the generic plan's cost estimate is badly off and keeps it from being chosen even though it would run fine.
  • After ANALYZE refreshes loyalty_ledger statistics, does a PostgreSQL prepared statement keep its cached generic plan?
    No. New planner statistics on a referenced table invalidate the cached plan, so PostgreSQL re-plans before the next use and makes the generic-versus-custom choice with the new estimates. If the skew is still there, the same choice may come back.

A shop prices a standard parcel once and prints the label for every later parcel of that type: quick and right for typical parcels, badly wrong for the rare pallet that turns up under the same label.

  • PostgreSQL times the generic plan and switches back if it runs slower.
  • Changing plan_cache_mode only affects statements prepared after the change.
  • A prepared statement uses a generic plan from its very first execution.
  • Re-preparing the statement fixes the slow plan for good.
  • pg_stat_activity shows whether each prepared statement runs a generic plan.