A partial index can keep a frequently queried subset of rows in a smaller structure. An index on job id WHERE status is open is a useful candidate when open jobs are rare and most requests target them. Its availability depends on what the planner can prove about each query, not merely on whether the application happens to pass “open” during one execution.

Prove that excluded rows are unnecessary

The index has no entries for closed jobs, so it cannot by itself answer a query that might need them. PostgreSQL must recognize at planning time that the query condition implies the index predicate. An explicit status = 'open' supplies that fact. A generic parameter status = $1 does not, because the same plan must remain valid when the parameter is closed.

Implication is not always textual identity. PostgreSQL recognizes some simple logical relationships, but it is not a general theorem prover. Standardizing the intended predicate helps prevent refactors from hiding an otherwise usable index. A condition on the indexed id alone cannot establish the status restriction, even if current data or application conventions make the row usually open.

Prepared plans change the evidence available

Prepared statements can use custom plans based on supplied parameter values or generic plans reusable across values. A custom plan for status open can know the needed predicate and consider the partial index. A generic plan with an unknown status cannot safely use that index as the complete answer path. The application’s SQL text has not changed, but the information available during planning has.

This does not mean parameterized queries are incompatible with partial indexes. A query with literal status = 'open' and a parameter only for id retains the predicate in a generic plan. Nor should parameters be replaced by string concatenation: parameter binding remains the correct way to carry untrusted values. Design the query around its real invariant rather than weakening data handling to chase an index.

The default plan policy chooses between custom and generic plans using its own cost considerations. For diagnosis, plan_cache_mode can force either behavior in a session. Such forcing is an experiment, not an instruction to disable generic plans globally. Replanning costs, parameter skew, and execution frequency determine the appropriate tradeoff.

When it breaks

A query can become slower after repeated prepared execution when a reusable plan replaces a parameter-specific one. The symptom may look like random index disappearance. Capture the actual prepared SQL and inspect EXPLAIN EXECUTE with representative values; an ad hoc literal query does not reproduce every prepared-plan condition. Confirm the predicate, plan mode, estimated rows, and runtime work together.

The subset can also stop being small. A backlog that leaves most jobs open reduces the expected size advantage, and status transitions still require index maintenance. Statistics and production-like distributions are needed before claiming lower write cost or better cache residency. A partial index is a workload decision, not a guarantee that the table’s future state will stay favorable.

Check it quickly

Save this as partial-plan.sql and run psql -X -v ON_ERROR_STOP=1 -f partial-plan.sql in a disposable PostgreSQL 18 database. The temporary table has one hundred open jobs among ten thousand rows, and the index includes only that open subset.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE jobs (id integer, status text NOT NULL);
INSERT INTO jobs SELECT g, CASE WHEN g<=100 THEN 'open' ELSE 'closed' END
FROM generate_series(1,10000) AS g;
CREATE INDEX jobs_open_id ON jobs (id) WHERE status='open';
ANALYZE jobs;
PREPARE chosen(text,integer) AS SELECT id FROM jobs WHERE status=$1 AND id=$2;
SET LOCAL plan_cache_mode=force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE chosen('open',7);
EXECUTE chosen('open',7);
SET LOCAL plan_cache_mode=force_custom_plan;
EXPLAIN (COSTS OFF) EXECUTE chosen('open',7);
EXECUTE chosen('open',7);
PREPARE always_open(integer) AS SELECT id FROM jobs WHERE status='open' AND id=$1;
SET LOCAL plan_cache_mode=force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE always_open(7);
EXECUTE always_open(7);
DEALLOCATE chosen;
DEALLOCATE always_open;
ROLLBACK;

On PostgreSQL 18.6, the generic plan with unknown status used a sequential scan. Forcing a custom plan for the same prepared statement and open value used jobs_open_id. The second statement, whose status predicate is a literal and only its id is parameterized, also used that partial index under a generic plan. All three executions returned id seven.

These plans demonstrate why a known predicate matters, not a promise that every machine selects the same access path. The explicit SET LOCAL changes end with rollback, and DEALLOCATE removes the prepared statements. The test does not reproduce automatic plan switching under a real connection pool; that requires observing the application’s actual sessions and repeated execution history.

Sources