Skip to content
Tutorials Center
My learning

Topics

Lesson 67 of 90 ยท Performance Tuning

Prepared Statements and Generic vs Custom Plans

Learn PREPARE and EXECUTE, custom vs generic plans, plan_cache_mode, pg_prepared_statements and pooler caveats, with a live cost-model demo.

Every SQL statement goes through the same stages: the server parses the text, analyzes it against the catalogs, plans it (choosing scans, join orders and indexes) and finally executes the plan. For a big report, execution dominates and the first three stages are noise. For the tiny queries a web application runs thousands of times per second, such as "fetch one user by id", parsing and planning can cost as much as running the query.

A prepared statement lets you do the first stages once and reuse the result with different parameter values. It is also the foundation of safe parameterized queries. But it comes with a subtle trade-off that explains many mysterious performance problems: a plan prepared without knowing the parameter value can be worse than a plan made for the real value. This lesson shows how prepared statements work, how PostgreSQL decides between custom and generic plans, and how to take control when it guesses wrong.

The statements need a PostgreSQL server, so the PostgreSQL blocks are read-only. A live Python simulation models the planner's decision with a small cost model, so you can see the pattern for yourself.

Life of a statement

Parse Check the syntax.
Analyze Resolve names and types.
Plan Choose the cheapest strategy.
Execute Run it and return rows.

Prepared statements repeat only the last step.

1 / 2

PREPARE, EXECUTE and DEALLOCATE

At the SQL level you prepare a statement with $1, $2 placeholders, then run it as often as you like:

SQL
-- PostgreSQL (read-only)
PREPARE orders_by_status (text, date) AS
  SELECT id, total
  FROM orders
  WHERE status = $1 AND placed_on >= $2;

EXECUTE orders_by_status('pending', '2026-01-01');
EXECUTE orders_by_status('shipped', '2026-02-01');

DEALLOCATE orders_by_status;      -- or DEALLOCATE ALL

The parameter types are optional (PostgreSQL can infer them), and a parameter can stand for a value, never for a table or column name. A prepared statement belongs to the session that created it. Other connections cannot see or use it, and it disappears when the session ends. You can list what a session has prepared:

SQL
-- PostgreSQL (read-only)
SELECT name, statement, parameter_types, generic_plans, custom_plans
FROM pg_prepared_statements;

The last two columns (PostgreSQL 14 and later) count how many times each kind of plan was used, which is the quickest way to see what the server decided.

Where prepared statements really live: the protocol

You rarely type PREPARE yourself. Drivers use PostgreSQL's extended query protocol, which splits execution into messages:

  1. Parse: send the SQL text with placeholders. The server parses it and stores it under a name (an empty name means the "unnamed" statement, replaced by the next Parse).
  2. Bind: send parameter values. The server creates a portal, choosing the plan at this point.
  3. Execute: run the portal and stream rows.

A driver that sends the same named statement again can skip the Parse step. Many drivers prepare automatically after a few executions, for example JDBC's prepareThreshold (default 5) and psycopg 3's prepare_threshold (default 5). Some drivers can emulate parameters on the client by pasting escaped values into the SQL text, which gives up server-side reuse. Check your driver's documentation (see PostgreSQL from Application Code).

There is a second benefit that has nothing to do with speed. Parameter values travel separately from the SQL text and are never parsed as SQL, so they cannot change the meaning of the statement. This is why parameterized queries are the main defense in SQL Injection and Safe Queries.

Custom plans and generic plans

When a prepared statement runs, PostgreSQL can use one of two kinds of plan:

  • A custom plan is made for this execution, with the parameter values substituted in. The planner knows the actual value, can consult column statistics for it (see Statistics and the Planner) and can pick the plan that is best for exactly this value. It costs planning time on every execution.
  • A generic plan is made once, with the parameters left as unknown placeholders, and cached. It saves the planning time on later executions, but the planner has to guess about selectivity from averages, such as "a value matches 1 / n_distinct of the rows".

The default policy (plan_cache_mode = auto) is a compromise:

  1. The first five executions use custom plans.
  2. PostgreSQL keeps the average cost of those custom plans, including an estimate of the planning cost.
  3. From the sixth execution on, it also builds a generic plan and compares its estimated cost. If the generic plan is cheaper than the average custom plan, it switches to the generic plan and keeps using it. Otherwise it keeps making custom plans.

Why does this matter? Look at a table of 100,000 orders where 95 percent have status 'done' and only 0.5 percent are 'pending'. For 'pending', an index scan is perfect. For 'done', scanning the whole table is cheaper than visiting 95,000 index entries. A custom plan picks the right strategy per value. A generic plan must pick one strategy for all values, from an average. If your first few executions use common values, the generic plan may look equal or cheaper than the custom average, get adopted, and then be slow when a rare value arrives.

Simulating the decision

The program below is a model, not the real planner. It uses a simple cost formula: a sequential scan costs one unit per row, an index scan costs a fixed start-up plus four units per matching row, and planning costs 50 units. It runs the same 20-statement workload under the three values of plan_cache_mode: six executions with the common value 'done', then fourteen with the rare value 'pending'. The workload is deliberately unlucky, so the failure shows up.

Python Custom versus generic plans in a cost model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In auto mode the five custom plans for 'done' are sequential scans that each include the planning cost, so their average (100,050) is slightly above the generic plan's cost (100,000). The model switches to the generic plan on execution 6 and then runs a full table scan for every 'pending' lookup, where the custom plan would have used the index. The total cost is more than three times the force_custom_plan total. Real planners have many more factors, but the shape of this failure is the same one people hit in production: "the query is fast in psql but slow from the application after a few calls."

Taking control with plan_cache_mode

The setting plan_cache_mode (PostgreSQL 12 and later) overrides the heuristic:

Value Behavior Use when
auto (default) Five custom plans, then pick by cost Most workloads
force_custom_plan Always plan with the real values Skewed data, rare values matter, planning cost is small compared with execution
force_generic_plan Use the generic plan from the start Very short, uniform queries where planning time dominates
SQL
-- PostgreSQL (read-only)
SET plan_cache_mode = force_custom_plan;                 -- this session
ALTER ROLE reporting SET plan_cache_mode = force_custom_plan;   -- every session of a role
ALTER DATABASE shop SET plan_cache_mode = auto;

Apply it narrowly: to the role or application that has the problematic query, not to the whole cluster. Planning time is real: on a query with many joins or a partitioned table with hundreds of partitions, force_custom_plan can make planning the slowest part.

Looking at the plans

To see the plan a prepared statement gets:

SQL
-- PostgreSQL (read-only)
PREPARE find_orders (text) AS SELECT id FROM orders WHERE status = $1;

EXPLAIN EXECUTE find_orders('pending');

-- PostgreSQL 16+: show the generic plan of a parameterized query without running it
EXPLAIN (GENERIC_PLAN) SELECT id FROM orders WHERE status = $1;

EXPLAIN EXECUTE shows the plan used for that execution. A generic plan is recognizable because it mentions $1 where a custom plan would show the literal value. Reading Query Plans explains the rest of the output. Remember that a partial index such as WHERE status = 'pending' cannot be proved usable by a generic plan, since the planner does not know $1 equals 'pending' (see Partial and Expression Indexes).

When plans are rebuilt

A cached plan is not permanent. PostgreSQL invalidates and rebuilds it automatically when something it depends on changes: a table or index is altered or dropped, ANALYZE updates the statistics, or a relevant setting changes such as search_path. You do not need to re-prepare. This is why an ALTER TABLE can cause one slightly slower call afterwards.

Prepared statements and connection poolers

Because a named prepared statement lives in one server connection, a pooler that hands a different server connection to each transaction can break it: the next transaction may land on a connection that never saw your Parse. Historically that forced users of PgBouncer in transaction mode to disable named server-side prepares in the driver (for example prepareThreshold=0 in JDBC). PgBouncer 1.21 added support for protocol-level prepared statements in transaction pooling (via max_prepared_statements), but the SQL-level PREPARE command is still not tracked. Read your pooler's documentation, and see Connection Pooling for the wider picture.

Practical advice

  • Always parameterize values coming from users, whether or not you also reuse plans.
  • Do not worry about prepared statements for slow queries. If execution takes 200 ms, saving 1 ms of planning is irrelevant.
  • For hot, tiny queries, server-side reuse can cut CPU noticeably. Measure with pg_stat_statements (see pg_stat_statements), which reports planning time if track_planning is on.
  • Suspect a generic plan when a query is fast with a literal in psql but slow from the application and the column is skewed. Check pg_prepared_statements.generic_plans and try force_custom_plan for that role.
  • Keep statistics fresh so both kinds of plan have good estimates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. With plan_cache_mode = auto, how many executions of a prepared statement use custom plans before PostgreSQL considers a generic plan?

    The first five executions get custom plans. From the sixth on, PostgreSQL compares the generic plan's cost with the average custom plan cost.
  2. 2. Why can a generic plan be slow for a rare value in a skewed column?

    Without the actual value, the planner cannot use the value-specific statistics, so one strategy is chosen for all values.
  3. 3. Which statement about a prepared statement is true?

    Prepared statements are per session, and parameters stand for values only, never identifiers.
  4. 4. A query is fast in psql with a literal but slow from the application after several calls. What is a good first check?

    A generic plan adopted after the fifth execution is a classic cause. Compare the plans and try force_custom_plan for that role or session.

Summary

  • A prepared statement is parsed once and executed many times with different parameters. Drivers do this through the extended protocol, and parameters also protect against SQL injection.
  • PostgreSQL uses custom plans (made for the actual values) for the first five executions, then adopts a generic plan if its estimated cost beats the average custom cost.
  • Generic plans save planning time but can be poor for skewed data and cannot use partial indexes that depend on the parameter.
  • plan_cache_mode can force custom or generic plans, and pg_prepared_statements, EXPLAIN EXECUTE and EXPLAIN (GENERIC_PLAN) show what is happening.
  • Cached plans are rebuilt automatically after schema or statistics changes. Poolers in transaction mode need special support for named prepared statements.

Related reading: how the planner estimates costs in Statistics and the Planner, and practical fixes in Query Tuning Patterns.