References:

• Prepare statements (PostgreSQL docs)
• plan_cache_mode (PostgreSQL docs)
• EXPLAIN (GENERIC_PLAN)

You use PostgreSQL, have a partial index, and your query is parameterized? You're in for a big surprise!

PostgreSQL has an optimization that trades planning time for execution efficiency. It can silently stop using your partial index after the sixth execution in the same session.

The plan cache lifecycle

PostgreSQL sessions and connection poolers

A PostgreSQL session is a single, persistent TCP connection to a PostgreSQL backend process. All session-local state, including prepared statements and their plan-cache counters, lives on that connection and is discarded when it closes. Since each PostgreSQL backend process has its own memory space, plan-cache counters are neither shared nor visible across sessions.

Application-level connection poolers like HikariCP keep a fixed set of long-lived server connections and hand them out to application threads one at a time, so a prepared statement created on a given connection accumulates executions across many application requests that happen to land on it. The plan-cache behavior described below therefore applies in full.

Planning for parameterized queries

Every PostgreSQL parameterized query sent through a prepared statement goes through two distinct planning regimes. The first five executions use a custom plan: the planner substitutes the actual parameter values, consults column statistics and histogram bounds, and produces a plan tuned to the specific data being queried. Custom plans are expensive to generate but accurate.

Starting with the sixth execution, PostgreSQL builds a generic plan (one that treats all parameters as unknowns: $1, $2, and so on) and compares its estimated cost to the average of the five custom plans. If the generic plan is not significantly more expensive, PostgreSQL switches to reusing it for all subsequent executions in that session. The decision algorithm is described in the PREPARE documentation.

This behavior is controlled by the plan_cache_mode per-session configuration parameter, which defaults to auto. Setting it to force_custom_plan always re-plans with actual values; force_generic_plan always uses the parameter-agnostic plan.

Why partial indexes are uniquely exposed

A partial index carries a fixed predicate in its definition: a set of constant conditions that determine which rows it covers. For the planner to use a partial index, it must prove at planning time that the query's own WHERE clause implies the index predicate. With a custom plan, that proof is straightforward: the planner can see the actual parameter values and verify the match directly.

With a generic plan, the parameters are unknowns. If the index predicate says priority = 'HIGH' and the query says priority = $1, the planner cannot determine whether $1 will equal 'HIGH' at execution time. It must conservatively assume the index might not apply, and so it falls back to treating the partial index as if it does not exist, even though every actual execution passes 'HIGH' for $1.

Regular B-tree indexes have no predicate; they cover all rows unconditionally. The planner can use them in generic plans without proving anything. They may still produce suboptimal cost estimates when parameter selectivity varies widely, but they are not categorically excluded the way partial indexes are. A partial index disappears entirely from the generic plan; a regular index is present but may be costed incorrectly.

A worked example

Setup

Consider a task-management service. The task table holds all pending tasks, along with their priorities and due times. Once a task is complete, it is removed from the table. The table has a partial index to support fetching high-priority tasks.

DROP TABLE IF EXISTS task;

CREATE TABLE task (
    id        UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    priority  TEXT        NOT NULL,
    due_at    TIMESTAMPTZ NOT NULL
) WITH (autovacuum_enabled = off);
-- autovacuum_enabled = off prevents autovacuum from running VACUUM or ANALYZE
-- on this table. Either operation updates pg_class, which PostgreSQL broadcasts
-- as a catalog invalidation to all sessions. Any session holding a prepared
-- statement on this table will silently discard its cached plan and re-plan on
-- the next execution. This keeps the pg_prepared_statements counters
-- deterministic for the demo. NEVER do this in production.

-- covers only the rows we ever query in this hot path
CREATE INDEX idx_task_high_priority
    ON task (due_at)
    WHERE priority = 'HIGH';

-- gather stats on task
ANALYZE task;

A background worker polls for high-priority tasks in batches using a prepared statement:

PREPARE get_tasks(text) AS
    SELECT * FROM task 
    WHERE priority = $1 
    ORDER BY due_at 
    LIMIT 10;

Query plans when the table is empty

Initially, when the table is empty or has very few rows (based on statistics gathered by ANALYZE), PostgreSQL will prefer a sequential scan over an index scan. We can check this using EXPLAIN:

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('HIGH');

                   QUERY PLAN
-------------------------------------------------
 Limit
   ->  Sort
         Sort Key: due_at
         ->  Seq Scan on task
               Filter: (priority = 'HIGH'::text)

You can check the generic_plans and custom_plans counters by querying the pg_prepared_statements view (this view only includes named prepared statements):

SELECT generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'get_tasks';

 generic_plans | custom_plans
---------------+--------------
             0 |            1

As explained above, the first five executions use a custom plan when plan_cache_mode = auto, so the custom_plans counter goes up by 1. Let's run the EXPLAIN query several more times and check the pg_prepared_statements view each time. The results are tabulated below:

From run 6 onward, PostgreSQL switches to a generic plan for this prepared statement. You can still enforce a custom plan by setting plan_cache_mode to force_custom_plan:

SET plan_cache_mode = 'force_custom_plan';

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('HIGH');

                   QUERY PLAN
-------------------------------------------------
 Limit
   ->  Sort
         Sort Key: due_at
         ->  Seq Scan on task
               Filter: (priority = 'HIGH'::text)

SELECT generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'get_tasks';

 generic_plans | custom_plans
---------------+--------------
             3 |            6

Notice the query plan includes priority = 'HIGH'::text rather than priority = $1, confirming a custom plan was used. The custom_plans counter is also incremented by 1.

For the rest of the discussion, let's reset plan_cache_mode to its default value:

RESET plan_cache_mode;

Query plans when the data distribution changes

We will now insert some data into the table and run the query again:

INSERT INTO task(priority, due_at)
SELECT 'HIGH', now()
FROM generate_series(1, 10000);

INSERT INTO task(priority, due_at)
SELECT 'LOW', now()
FROM generate_series(1, 10000);

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('HIGH');

QUERY PLAN
---------------------------------------
 Limit
   ->  Sort
         Sort Key: due_at
         ->  Seq Scan on task
               Filter: (priority = $1)

SELECT generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'get_tasks';

 generic_plans | custom_plans
---------------+--------------
             4 |            6

PostgreSQL used a generic plan, a sequential scan. The partial index exists but is not used.

If you run ANALYZE manually on the table and then run the query again:

ANALYZE task;

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('HIGH');

                      QUERY PLAN
-------------------------------------------------------
 Limit
   ->  Index Scan using idx_task_high_priority on task

SELECT generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'get_tasks';

 generic_plans | custom_plans
---------------+--------------
             4 |            7

Here's the complete observation:

In the default plan_cache_mode (= auto), PostgreSQL sticks to the generic plan after 5 executions when the custom plan offers no meaningful benefit. If the data distribution changes afterward, it will keep using the generic plan unless VACUUM or ANALYZE marks the cached plan stale. With autovacuum enabled, this happens when a significant portion of the table has changed (autovacuum_analyze_scale_factor defaults to 10%).

Query plans when another query "poisons" the generic plan

Let's keep the table distribution intact, start a new session, and define the prepared statement again:

PREPARE get_tasks(text) AS
    SELECT * FROM task
    WHERE priority = $1
    ORDER BY due_at
    LIMIT 10;

Now run the following 6 times:

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('LOW');

After the 6th execution, it will switch to the generic plan. If you now check the plan with HIGH, it will still do a sequential scan, and the partial index will not be used:

EXPLAIN (COSTS OFF)
EXECUTE get_tasks('HIGH');

              QUERY PLAN
---------------------------------------
 Limit
   ->  Sort
         Sort Key: due_at
         ->  Seq Scan on task
               Filter: (priority = $1)

Here's the complete observation:

Even if the data distribution does not change, a session can end up on the generic plan if the first 6 queries show no meaningful difference in cost between the generic and custom plans.

Fixing the issue

There are two solutions:

• Setting plan_cache_mode to force_custom_plan: This forces custom plans, which always re-plan with actual parameter values. It works, but it gives up the planning cost savings on every execution. You can scope it per transaction with SET LOCAL, but there's a better option.
• Inlining specific parameters directly in the query: If a parameter always matches the partial index predicate, inline it as a literal. The planner can then verify predicate implication regardless of plan type.

Below, I'll be using jOOQ as an example. jOOQ is a type-safe SQL query builder for Java (and JVM languages). If you're using other query builders or ORMs, check your query builder's documentation on how to inline specific parameters. The approach is not jOOQ-specific; any query builder that supports literal inlining will work the same way.

Consider this Kotlin jOOQ query:

// All conditions bound as parameters → $1, $2
dslContext
    .select(TASK.ID)
    .from(TASK)
    .where(TASK.PRIORITY.eq("HIGH"))      // → bound as $1
    .orderBy(TASK.DUE_AT)
    .limit(batchSize)                     // → bound as $2
    .fetchInto(UUID::class.java)

jOOQ sends this as a parameterized SQL statement. To inline $1, use the inline keyword (see jOOQ documentation on inlined parameters):

import org.jooq.impl.DSL.inline

dslContext
    .select(TASK.ID)
    .from(TASK)
    .where(TASK.PRIORITY.eq(inline("HIGH")))      // → inlined
    .orderBy(TASK.DUE_AT)
    .limit(batchSize)                             // → bound as $1
    .fetchInto(UUID::class.java)

Diagnosing with EXPLAIN (GENERIC_PLAN)

PostgreSQL 16 added the GENERIC_PLAN flag to EXPLAIN, letting you inspect what the generic plan would look like without waiting for six executions. See CYBERTEC's writeup for a detailed walkthrough.

EXPLAIN (GENERIC_PLAN)
SELECT * FROM task
WHERE priority = $1
ORDER BY due_at
LIMIT $2;

If you see an unwanted plan (e.g., a sequential scan or a wrong index) here despite a relevant index existing, the generic plan is the culprit.

On PostgreSQL 15 and earlier, use SET plan_cache_mode = force_generic_plan; before checking the plan.

Summary

PostgreSQL's plan cache switches a prepared statement to a generic plan after five custom executions, provided the generic plan's estimated cost is competitive. Generic plans treat all parameters as unknowns, which means they cannot satisfy the predicate-implication check required to use a partial index. Once the switch happens, the partial index silently disappears from the execution plan, even if every actual call would have matched the index predicate.

The trigger can be subtle: querying with parameters that make the generic plan look cheap (for example, against an empty table) is enough to lock in a plan that ignores your partial index for all future executions in that session. Running the same prepared statement later with different, more selective parameters does not undo this.

VACUUM or ANALYZE resets the cycle by invalidating the cached plan, but that only helps until the generic plan wins the cost comparison again. The durable fix is to inline the parameter values that match your partial index predicates, so the planner can always verify predicate implication regardless of plan type. In jOOQ, this is a single inline() call; other query builders often expose an equivalent mechanism.

To diagnose proactively, use EXPLAIN (GENERIC_PLAN) (PostgreSQL 16+) or SET plan_cache_mode = force_generic_plan (PostgreSQL 15 and earlier) to see whether your query's generic plan silently drops an index you're counting on.