The question usually arrives as a security question: “Should I use bind parameters, or is it OK to build the SQL string myself?” The answer to that question is that you should use bind parameters, the string-building approach is how you get a SQL injection, and we can all move on with our lives.

The performance question hiding underneath it is more interesting, and the folk wisdom about it is mostly wrong. “Prepared statements skip planning” is the usual version. It is true only in a narrow set of circumstances, and when it is true it is not always good news. This post is about what PostgreSQL does with a query whose values are baked into the text, what it does with a query whose values arrive separately, and how the plan cache that sits between those two paths makes its decisions. There is a specific moment, the sixth execution of a prepared statement, where the two paths diverge, and a surprising amount of “my query got slow and I didn’t change anything” traces back to it.

Everything below was verified against PostgreSQL 18; the relevant source is src/backend/utils/cache/plancache.c, which is unusually readable and which I recommend.

What “textual substitution” means

When a client sends SELECT count(*) FROM orders WHERE status = 'pending' over the simple query protocol (the thing psql uses, and the thing psycopg2 uses after it has interpolated your values client-side), the backend runs the whole pipeline every time: parse the text into a raw parse tree, analyze it into a Query (resolving names, types, and the operator for =), run the rewriter (views, rules, row-level security), plan it, execute it, and throw all of it away. exec_simple_query() in tcop/postgres.c calls the planner directly. The plan cache is never consulted. Nothing is remembered.

That last sentence surprises people coming from Oracle or SQL Server, both of which maintain a shared, cross-session cache of plans keyed on the SQL text. PostgreSQL has no such thing. There is no global plan cache. What PostgreSQL has is a per-backend cache of plans attached to specific objects (prepared statements, PL/pgSQL statements, SPI plans), and a query that arrives as plain text does not touch it. Two identical SELECTs from two connections are planned twice; two identical SELECTs from one connection, sent as text, are also planned twice.

The upside of this path is that the planner knows everything. The literal 'pending' is a Const node in the query tree. The planner can look it up in the column’s most-common-values list and discover that it matches 0.12% of the table. It can fold WHERE id IN (1, 2, 3) into a scalar array op and estimate it precisely. If orders is partitioned by status, it can prune to one partition at plan time and never so much as lock the others. Every value-sensitive decision the planner is capable of, it makes with the real value in hand.

The downside is that you pay for parsing and planning on every execution. For a single-table lookup that is a few hundred microseconds and nobody cares. For a nine-way join with a few subqueries, planning can take longer than execution, and now somebody cares.

What bound parameters mean

Over the extended query protocol, the client sends the query as SELECT count(*) FROM orders WHERE status = $1 and the value pending arrives separately. This is three messages, not one:

  • Parse carries the SQL text, a statement name (possibly empty), and optionally the type OIDs of the parameters. The backend parses, analyzes, and rewrites the query and stores the result in a CachedPlanSource. It does not plan. If a parameter’s type was not specified (OID 0), parse analysis infers it from context, the same way it would for an untyped literal.
  • Bind carries the parameter values and produces a portal, which is a plan plus values, ready to execute. This is where planning happens, if it happens.
  • Execute runs the portal.

The statement name matters a great deal. A named statement is stored in the backend’s prepared-statement hash table and lives until you DEALLOCATE it or the session ends; it is exactly what PREPARE gives you in SQL, with a different wire encoding. The unnamed statement is a single slot: each new Parse of the unnamed statement destroys the previous one. Most drivers use the unnamed statement by default for a plain parameterized query and only promote to a named statement when they decide the query is being reused (more on that below).

Here is the part the folk wisdom gets wrong. When Bind arrives with values, the backend marks each value with PARAM_FLAG_CONST, and when the planner builds a plan for those specific values (a custom plan), it treats the parameters as constants. The comment in exec_bind_message() says so directly: this “ensures that any custom plan makes full use of the parameter values.” A custom plan for status = $1 with $1 = 'pending' is the same plan, with the same row estimates and the same partition pruning, that you would get from status = 'pending' in the text. You skipped the parse, analyze, and rewrite steps if you reused a named statement, and you skipped nothing at all if you used the unnamed one. But you did not skip planning.

So for the first five executions of any prepared statement, “bound versus textual” is a distinction with no planning consequence. The consequence arrives on the sixth.

The cache itself

Before the sixth execution, a short tour of what is being cached.

A CachedPlanSource holds the raw parse tree, the analyzed and rewritten query list, the parameter types, and a list of every relation, function, and domain the query depends on. It also holds four numbers that drive everything else: generic_cost, total_custom_cost, num_custom_plans, and num_generic_plans. Hanging off it, optionally, is a CachedPlan called gplan: the generic plan, built without any parameter values, reusable for any values.

Custom plans are also CachedPlan objects, but they are not retained. They are built, reference-counted for the duration of the execution, and freed. The only plan the cache ever keeps is the generic one. “Plan cache” is therefore a slightly generous name; it is a query cache with, at most, one plan attached per query.

Saved plan sources live in CacheMemoryContext, which is backend-local memory that persists for the life of the session. An ORM that prepares a distinct named statement for every one of ten thousand slightly different queries (I have seen this) will accumulate ten thousand query trees per connection, and the generic plans that go with them. This is one of the several ways a PostgreSQL backend grows to several hundred megabytes of RSS without anyone having asked it to.

Invalidation

Cached query trees and generic plans are invalidated through the shared-invalidation (sinval) mechanism. Any relcache event on a relation the query depends on, or a pg_proc or pg_type syscache event on a function or domain it uses, marks the plan source invalid. Some catalogs (pg_namespace, for instance) are coarser: any change there invalidates every cached plan in the backend, on the theory that people do not create schemas very often. The cache also forces re-analysis if search_path differs from when the query was last analyzed, and if row-level security is involved, if the current role has changed.

ANALYZE invalidates cached plans too, in practice: it updates pg_class (reltuples, relpages), and that update generates a relcache invalidation. This is why prepared statements pick up fresh statistics after an ANALYZE even though nothing about the query changed. (If the statistics didn’t move, pg_class is not touched and neither are your plans.)

Invalidation does not destroy the prepared statement. The next execution re-runs parse analysis and rewrite against the retained raw parse tree, replans, and carries on. Your PREPAREd statement survives an ALTER TABLE ... ADD COLUMN on the table it references; it just quietly gets re-analyzed. (Unless it was SELECT *, in which case the re-analysis produces a different result row type, and a protocol-level or SQL-level prepared statement is not allowed to do that: you get ERROR: cached plan must not change result type. Name your columns.) What invalidation does not do is reset the four cost numbers. There is a comment in RevalidateCachedQuery() explaining that the developers considered resetting them and decided that “we’re better retaining our hard-won knowledge about the relative costs.” Hold that thought.

DISCARD PLANS invalidates everything the same way (and likewise leaves the cost history alone). DEALLOCATE and DISCARD ALL remove the statements themselves.

The decision

Every execution of a cached query calls GetCachedPlan(), which calls choose_custom_plan(). That function is short enough to summarize completely.

First, the cases with no decision to make. One-shot plans (PL/pgSQL EXECUTE, SPI_execute() with no saved plan) are always custom. A query with no parameters is always generic, since a custom plan could not differ from it. Utility statements and other things that don’t need planning are always generic. If plan_cache_mode is set to force_generic_plan or force_custom_plan, that wins. If the caller (PL/pgSQL, for example) passed a cursor option forcing one or the other, that wins next.

Then the actual policy:

  1. If fewer than five custom plans have been built, build a custom plan. The source comment calls the number five “arbitrary,” and it is.
  2. Otherwise, compute the average estimated cost of the custom plans so far. Each custom plan’s cost was recorded as the planner’s total_cost for the plan plus a charge for having planned it: 1000 * cpu_operator_cost * (number of range-table entries + 1). At the default cpu_operator_cost of 0.0025 that is 2.5 cost units per entry (joins count as entries), so single digits for most queries. The source describes this estimator as “very crude” and “probably on the low side,” which is candid.
  3. If the generic plan’s estimated cost (recorded without any planning charge, because a generic plan is planned once) is less than that average, use the generic plan. Otherwise, build another custom plan.

There is a wrinkle in step 3 for the sixth execution specifically. At that point generic_cost is still -1, meaning “not yet known,” so the comparison trivially favors generic. GetCachedPlan() builds the generic plan, records its cost, and then calls choose_custom_plan() again. If the now-known generic cost loses to the custom average, it builds a custom plan instead and uses that, so the generic plan is planned but never executed. The comment calls this “a bit of a wart.” The practical upshot is that when the custom plans are winning, the sixth execution pays for two planning cycles: one to build a generic plan that is immediately discarded, one for the custom plan it uses.

Two properties of this policy explain most of the confusing behavior people see in production.

The decision is made on estimates, never on results. Nothing about actual execution time feeds back into it. If the generic plan’s estimated cost is lower than the custom average, it is used, and if it then runs ten times slower than the custom plans did, PostgreSQL has no way of knowing that and no mechanism for reconsidering.

The decision is sticky, asymmetrically. Once the generic plan wins, no more custom plans are built, so total_custom_cost and num_custom_plans freeze; the average the generic plan beat is the average it will be compared against until something invalidates it. When invalidation does arrive, the generic plan is rebuilt and its new cost compared against that same frozen average, which is the only way the decision flips back to custom. In the other direction, once the generic plan loses it is kept but never rebuilt, so generic_cost stays at whatever it was on the sixth execution, while the custom average keeps accumulating, one plan per execution, diluted by everything since the first. A run of unusually expensive custom plans can push that running average over the stale generic cost and flip the decision to generic, at which point the average freezes again. Nothing resets the history except DEALLOCATE and disconnecting.

Why generic plans are sometimes terrible

A generic plan is built with no parameter values. The planner still has to produce row estimates, and it has rules for doing so blind.

For an equality comparison against an unknown value (status = $1), var_eq_non_const() in selfuncs.c assumes the value is equally likely to be any of the column’s distinct values: selectivity is the non-null fraction divided by n_distinct, capped at the frequency of the single most common value. The comment in the source asks “Is that a good idea?” and does not answer. For a column with five distinct values where one of them is 99.5% of the table, the generic estimate is 20% of the table, which is wrong for every actual value the column contains.

For range comparisons against an unknown value (placed_at > $1), the estimate is DEFAULT_INEQ_SEL, one third of the table. For LIKE $1, DEFAULT_MATCH_SEL, 0.5%. These are constants; they do not consult statistics at all.

And a generic plan cannot prune partitions at plan time, because the pruning key is unknown. Since PostgreSQL 11 the executor can prune at startup instead, once the values arrive, and EXPLAIN ANALYZE reports this as Subplans Removed: N. That works, but the plan still contains a subplan for every partition, every partition is locked when the plan is checked out of the cache, and the planner costed the plan as though it would scan all of them.

Here is what this looks like on an orders table of two million rows, 99.5% of them shipped, joined to a customers table of 100,000:

1PREPARE q(text) AS


2  SELECT c.region, count(*)


3    FROM orders o JOIN customers c ON c.id = o.customer_id


4   WHERE o.status = $1


5   GROUP BY 1;

The custom plan for 'pending' (2,000 rows) is an index scan feeding a hash join, estimated cost 3,043:

1HashAggregate  (cost=3043.34..3043.37 rows=3 width=11)


2  ->  Hash Join  (cost=2887.45..3035.34 rows=1600 width=3)


3        ->  Index Scan using orders_status_idx on orders o  (cost=0.43..144.12 rows=1600 width=4)


4              Index Cond: (status = 'pending'::text)


5        ->  Hash  (cost=1637.01..1637.01 rows=100001 width=7)


6              ->  Seq Scan on customers c  (cost=0.00..1637.01 rows=100001 width=7)

The custom plan for 'shipped' is a parallel sequential scan, estimated cost 35,341, and runs in about 470 ms on this machine. And the generic plan, which you can see directly in PostgreSQL 16 and later with EXPLAIN (GENERIC_PLAN):

1EXPLAIN (GENERIC_PLAN)


2  SELECT c.region, count(*)


3    FROM orders o JOIN customers c ON c.id = o.customer_id


4   WHERE o.status = $1 GROUP BY 1;


5


6Finalize GroupAggregate  (cost=14981.16..14982.04 rows=3 width=11)


7  ->  Gather Merge  (cost=14981.16..14981.98 rows=7 width=11)


8        Workers Planned: 2


9        ->  ...


10              ->  Hash Join  (cost=2887.45..13147.75 rows=166667 width=3)


11                    ->  Parallel Index Scan using orders_status_idx on orders o  (cost=0.43..9823.21 rows=166667 width=4)


12                          Index Cond: (status = $1)


13                    ->  Hash  (cost=1637.01..1637.01 rows=100001 width=7)


14                          ->  Seq Scan on customers c  (cost=0.00..1637.01 rows=100001 width=7)

Estimated 400,000 matching rows (one fifth of two million, split across three processes), estimated cost 14,982. It is a parallel index scan, because at 20% selectivity the planner decided that was cheaper than a sequential scan, and it is a plan that suits none of the values the column contains.

Now run it five times with 'shipped', which is what a real workload against this table mostly does:

1EXECUTE q('shipped');   -- custom, ~470 ms


2EXECUTE q('shipped');   -- custom


3EXECUTE q('shipped');   -- custom


4EXECUTE q('shipped');   -- custom


5EXECUTE q('shipped');   -- custom


6


7SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;


8 name | generic_plans | custom_plans


9------+---------------+--------------


10 q    |             0 |            5

Average custom cost: 35,341 plus a planning charge of 10. Generic cost: 14,982. Generic wins, by a mile, on paper. The sixth execution:

1EXECUTE q('shipped');   -- generic, ~600 ms


2EXECUTE q('pending');   -- generic, ~50 ms (custom plan: ~15 ms)


3


4SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;


5 name | generic_plans | custom_plans


6------+---------------+--------------


7 q    |             2 |            5

The 'shipped' case got slower because it is now reading 99.5% of the table through an index. The 'pending' case got three times slower because the plan launches two parallel workers, each of which builds its own 100,000-row hash table of customers, to join 2,000 rows. And this is now the plan for every execution of q on this connection until something invalidates it, and even then, the cost history says generic wins, so it will be rebuilt as generic.

The regression here is modest because the index on status happens to visit the heap in nearly physical order. Where the plan shape changes more radically (a nested loop that should have been a hash join, an index walk under a LIMIT that should have been a filtered scan, a partitioned table where the generic plan costs every partition), the same mechanism produces the “50x slower after warm-up” tickets that arrive with an EXPLAIN attached showing Index Cond: (col = $1) and a baffled engineer.

Why generic plans are sometimes excellent

None of this is an argument against generic plans. On a column with uniform distribution, the blind estimate is roughly right, the generic plan matches the custom plans, and skipping the planner on every execution is pure profit. For complex queries that is a lot of profit; a plan that takes 40 ms to build and 2 ms to run is exactly the case the plan cache exists for. Primary-key lookups, foreign-key lookups, anything keyed on a unique column: the estimator special-cases unique indexes (“assume there is exactly one match regardless of anything else”), so those generic plans are as good as custom ones and much cheaper to obtain.

The policy is a reasonable bet on average. It is a bad bet on skewed data with plan shapes that depend on the value, and it does not know which kind of data it is looking at.

Who is using the plan cache without telling you

You may believe you are not using prepared statements. You are probably wrong.

PL/pgSQL. Every SQL statement inside a PL/pgSQL function is a saved SPI plan, and every PL/pgSQL variable referenced by that statement is a parameter. SELECT count(*) INTO n FROM orders WHERE status = p_status; is, for the plan cache’s purposes, PREPARE ... WHERE status = $1, with exactly the same five-then-decide policy, and the function’s plans live for the life of the connection. This is the single most common way I encounter the sixth-execution problem: a function that is fast in testing, fast in production for a few minutes after each deploy, and then slow, and the query it runs is fine when you paste it into psql with the values substituted. EXECUTE with a string (and USING parameters) is the exception: those are one-shot plans, always custom, always replanned.

Drivers. psycopg2 interpolates client-side and sends text over the simple protocol; it never touches the cache. psycopg 3 uses the extended protocol for everything and promotes a query to a named prepared statement after it has been executed prepare_threshold times on a connection, default 5. The PostgreSQL JDBC driver does the same with prepareThreshold, also defaulting to 5. asyncpg prepares everything. pgx (Go) caches prepared statements by default. Node’s pg uses the unnamed statement unless you give a query a name, at which point it becomes a named one. If your ORM sits on top of one of these, it inherits the behavior, and most of them don’t document it.

Note what the driver thresholds do and do not mean. Below the threshold, each execution is a fresh Parse of the unnamed statement, which creates a fresh CachedPlanSource with num_custom_plans = 0, so it is always a custom plan; you are getting textual-substitution planning with extended-protocol packaging. Above the threshold, the driver’s named statement starts accumulating executions toward the server’s own count of five. So with the defaults in psycopg 3 or JDBC, the generic plan arrives around the tenth or eleventh execution of a given query on a given connection, not the sixth. The exact count is not important. What matters is that it is some small number, after which the plan can change without anything else changing.

PgBouncer. In transaction pooling mode, prepared statements historically did not work at all, because the statement lived on a server connection the client might never see again, and the standard advice was to set prepareThreshold=0 (or the equivalent) and live without them. PgBouncer 1.21 added max_prepared_statements, which tracks the client’s named statements and re-prepares them on whichever server connection is in use. If you enabled that (some managed platforms will do it for you), your application started using the plan cache at that moment, possibly without anyone noticing.

Recognizing it

The tell in an EXPLAIN or auto_explain output is a parameter symbol where a value should be: Index Cond: (status = $1) rather than Index Cond: (status = 'pending'::text). Custom plans fold the values in as constants; only a generic plan shows $1. If you have auto_explain logging slow queries and the slow plans show $1 while your manual EXPLAIN shows a different plan with the literal, you have found it.

pg_prepared_statements (PostgreSQL 14 and later) shows generic_plans and custom_plans counts for every named statement in the current session, which is useful for exactly one session, your own. There is no cross-session view of this. Be aware that EXPLAIN EXECUTE goes through GetCachedPlan() like any other execution and counts toward the five.

EXPLAIN (GENERIC_PLAN) (PostgreSQL 16 and later) takes a query with $n parameters and shows you the generic plan without preparing anything or supplying values. It is the fastest way to answer “what would this look like blind.”

For PL/pgSQL, auto_explain with auto_explain.log_nested_statements = on is the only practical way to see the plans at all.

What to do about it

plan_cache_mode (PostgreSQL 12 and later) has three values: auto, which is the policy above; force_custom_plan, which plans with the real values every time; and force_generic_plan, which builds the generic plan on the first execution and uses it forever. It is an ordinary GUC, so it can be set per session, per role, per database, or, most usefully, per function:

1ALTER FUNCTION count_orders_by_status(text) SET plan_cache_mode = force_custom_plan;

This is the correct fix for the PL/pgSQL case. The function’s queries are replanned on every call, which costs a few hundred microseconds each, and the plans are always right for the values. Do this for functions whose queries filter on skewed columns; do not do it globally, because you will throw away the plan cache’s benefits for every primary-key lookup in the system to fix a handful of bad actors.

For driver-level prepared statements, the equivalent is to prepare selectively. psycopg 3 accepts prepare=False per execute() call; JDBC’s prepareThreshold can be set per connection. Turning prepared statements off entirely is the sledgehammer, and it works, but the statements that were benefiting stop benefiting.

Sometimes the right fix is to the query. A query whose plan shape is stable across all parameter values makes a fine generic plan. A query whose ideal plan flips between a nested loop and a hash join depending on a single parameter is a query that should not be prepared, or, if the two cases are really two different workloads, should be two different queries.

And sometimes the right fix is to the data. A status column where one value is 99.5% of the table is often better served by a partial index on the interesting 0.5% (WHERE status <> 'shipped'), which the generic plan cannot use (it does not know the value) and the custom plan can. That is not a criticism of the plan cache; it is an observation that the plan cache and a partial index are answering the same question from different directions, and the partial index also makes the table smaller to scan.

What you should not do is conclude that bind parameters are slow. They are not. Planning is planning; a custom plan for $1 = 'pending' costs exactly what the textual version costs and produces exactly the same plan. The only thing that changes on the sixth execution is that PostgreSQL stops planning, on the strength of a comparison between two estimates, one of which was made without looking at the data. When that guess is right, you get planning for free. When it is wrong, you get a plan that nobody chose, and the fix is to tell PostgreSQL to keep choosing.