Prepared statements and PostgreSQL generic plans

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A query can be fast in a SQL console and slow in the application because the two executions are not being planned with the same information. The console query may contain a literal customer ID. The application may use a prepared statement whose generic plan must work for many customer IDs.

Prepared statements are not inherently a performance problem. Reusing parse and planning work can be valuable. The difficult case is a skewed distribution where different parameter values deserve materially different access paths.

Create a skewed tenant workload

This PostgreSQL 18 example requires one persistent session in a disposable database. Prepared statements belong to the session that creates them.

CREATE TABLE prepared_event (
  id bigint PRIMARY KEY,
  tenant_id integer NOT NULL,
  payload text NOT NULL
);
INSERT INTO prepared_event
SELECT g,
       CASE WHEN g <= 95000 THEN 1 ELSE g END,
       repeat('x', 80)
FROM generate_series(1,100000) AS s(g);
CREATE INDEX prepared_event_tenant ON prepared_event(tenant_id);
ANALYZE prepared_event;

PREPARE tenant_rows(integer) AS
SELECT id, payload FROM prepared_event WHERE tenant_id = $1;

Tenant 1 owns most rows; another valid tenant identifier can own one. A custom plan can use the concrete parameter value when estimating selectivity. A generic plan is reusable across values and must make a more general choice.

Compare planning modes explicitly

BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) EXECUTE tenant_rows(1);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) EXECUTE tenant_rows(99999);
ROLLBACK;

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) EXECUTE tenant_rows(1);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) EXECUTE tenant_rows(99999);
ROLLBACK;

These are diagnostic controls, not a recommendation to force one mode globally. Capture actual plans and resource counts. Do not assume the generic plan must be an index scan or that the custom plan must be faster for every value. The point is to observe whether parameter-sensitive selectivity changes the useful plan. On PostgreSQL 18.6 the custom plans differed (a sequential scan for tenant 1, an index scan for the rare tenant), while the forced generic plan used the index for both, estimating 62 rows for tenant 1 where 95,000 came back.

The same statement can also spend meaningful time planning. A custom plan that saves execution time on a large query may be worthwhile; a custom plan for a trivial lookup executed at high frequency may add overhead without a corresponding benefit. Evaluate end-to-end latency and workload volume.

Understand automatic selection

With the usual automatic mode, PostgreSQL initially evaluates custom plans and can compare their estimated cost with a generic alternative. The documented heuristic uses the first five executions for custom plans before that comparison. It is a cost-based heuristic, not a promise that execution six will switch or that measured wall-clock timing directly determines the choice.

The values supplied during those initial executions can therefore be useful context when investigating a session's behavior. A connection that sees mostly one kind of tenant may develop different planning history from another. Inspect rather than assuming every pooled connection behaves identically.

SELECT name, parameter_types, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'tenant_rows';

The view concerns prepared statements visible in the current session. Querying it from an unrelated administration connection does not inventory all statements inside an application pool.

Reference planning modes. A custom plan sees the concrete parameter; a generic plan is shared across values. Pooling determines which session owns the statement and its history.

Reproduce the client, not just the SQL text

Drivers differ in when they create server-side prepared statements. Some use an execution threshold. Some use unnamed statements. Poolers can preserve or remap prepared-statement behavior depending on mode and version. Verify the actual driver and pooler configuration rather than asserting that transaction pooling always supports or always breaks prepared statements.

Record parameter types. An integer parameter, a text parameter with a cast, and a literal of an inferred type may not produce identical expressions. Record the role, search path, row-level security context, and relevant planner settings as well.

DDL changes, statistics updates, and invalidation events can affect cached plans. If a latency change follows a deployment or analyze run, preserve that timing in the investigation. “The SQL did not change” does not mean the planning environment did not change.

Choose a targeted remedy

Refreshing stale statistics is the first step when the model is wrong even for literals. For genuine parameter skew, options include allowing custom planning for the affected operation, separating semantically distinct query paths, or changing the access pattern so plan sensitivity matters less. A tenant-specific endpoint and an all-tenant export may deserve different SQL rather than one maximally generic statement.

Do not interpolate untrusted values into SQL to escape generic planning. Parameter binding remains the right security boundary. A fixed literal can be appropriate for a true endpoint invariant, such as a queued-only partial index predicate, while user data remains parameterized.

A blanket force_custom_plan setting changes planning costs for every eligible statement in scope. Test it locally or at a carefully chosen application boundary first. A blanket generic plan can similarly punish outlier parameters. The right scope is the smallest one supported by evidence.

Validate the parameter distribution

Benchmark the common tenant, a rare tenant, an absent tenant, and any large tenants with different row widths or security filters. Weight results by actual request frequency and consider tail latency, not only an average. A generic plan that is acceptable for most calls may still create a severe outlier for one important customer.

The final diagnosis should name both the query and the planning context: which parameters diverge, whether a generic plan is actually used, how the pool establishes session state, and what the proposed change costs in planning work. That turns a mysterious “fast manually, slow in app” report into a reproducible engineering decision.

Sources and review scope

• PREPARE
• Prepared statement view
• Plan cache configuration
• EXECUTE