Partial Indexes and Prepared Statement Parameters #

A partial index on WHERE status = 'queued' makes the job-poller query instantaneous in psql. From the application the same query takes 900 ms and shows a sequential scan. Nothing differs but the literal: the application sends WHERE status = $1, and a generic plan cannot know that $1 will be 'queued'.

Predicate matching is covered in partial index predicate matching rules. This page covers the interaction with parameters, which is where most “the index is not used in production” reports come from.

The Planner Condition #

To use a partial index, the planner must prove that every row the query wants satisfies the index predicate. The proof is made from the query’s qualifications at planning time:

Literal in the query. WHERE status = 'queued' implies WHERE status = 'queued'. Proof succeeds.
Parameter in a custom plan. When a prepared statement is planned with the actual parameter values — the custom plans built for the first executions — the planner substitutes the bound value, so status = $1 with $1 = 'queued' becomes a constant comparison and the proof succeeds.
Parameter in a generic plan. The value is unknown by construction. status = $1 proves nothing about status = 'queued', so the partial index is not usable, and the planner picks the best alternative — often a sequential scan or a much larger index.

So the same statement can use the partial index for its first five executions and stop using it when the generic plan takes over, exactly as described in prepared statement plan pinning. The symptom is a query that degrades after warm-up, and is fast again after a reconnect.

The same applies to a partial index’s predicate on any expression: an IN list, a range, or deleted_at IS NULL all need a provable implication from the query’s own conditions.

Annotated EXPLAIN Evidence #

CREATE INDEX CONCURRENTLY jobs_queued_run_at_idx
  ON jobs (run_at) WHERE status = 'queued';

With a literal:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM jobs WHERE status = 'queued' AND run_at <= now() ORDER BY run_at LIMIT 10;

Limit  (actual time=0.02..0.03 rows=10 loops=1)
  ->  Index Scan using jobs_queued_run_at_idx on jobs  (actual rows=10 loops=1)
        Index Cond: (run_at <= now())
        Buffers: shared hit=4
Execution Time: 0.05 ms

With a parameter, generic plan:

PREPARE poll (text) AS
  SELECT id FROM jobs WHERE status = $1 AND run_at <= now() ORDER BY run_at LIMIT 10;
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE poll ('queued');

Limit  (actual time=884.2..884.2 rows=10 loops=1)
  ->  Sort  (actual time=884.2..884.2 rows=10 loops=1)
        Sort Key: run_at
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Seq Scan on jobs  (actual rows=3120 loops=1)
              Filter: ((status = $1) AND (run_at <= now()))
              Rows Removed by Filter: 41204110
        Buffers: shared hit=402110 read=18204
Execution Time: 884.6 ms
-- the partial index is invisible to this plan: $1 might be any status

And with a custom plan, the same prepared statement:

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE) EXECUTE poll ('queued');

Limit → Index Scan using jobs_queued_run_at_idx on jobs   Execution Time: 0.06 ms
-- bound value substituted at planning time; the proof succeeds

Step-by-Step Resolution #

Confirm the pattern. Fast with a literal, slow from the application, and $n visible in the plan’s Filter.

Check whether the statement is prepared and generic:

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

Pick a fix by how the predicate is used.
- The predicate is a constant of the workload — the poller always wants 'queued'. Write the literal into the SQL instead of parameterising it. This is the simplest and most robust fix.
- The application cannot inline it — force custom plans for that statement or role:
```
ALTER ROLE worker SET plan_cache_mode = 'force_custom_plan';
```
- Several predicate values are used — create a full index that does not depend on a predicate, for example (status, run_at), and accept the larger index. The planner can then use it for any parameter value.
Verify from the application’s connection, not from psql, using auto_explain.
Re-check after deploys. ORMs and drivers change whether statements are prepared; the same query can flip behaviour with a driver upgrade, as covered in PgBouncer protocol-level prepared statements.

Before and After #

-- BEFORE: parameterised status, generic plan
Seq Scan on jobs  Filter: ((status = $1) AND …)  Rows Removed by Filter: 41204110   Execution Time: 884.6 ms

-- AFTER: literal 'queued' in the poller's SQL
Index Scan using jobs_queued_run_at_idx  Index Cond: (run_at <= now())              Execution Time: 0.05 ms

Choosing between a partial index and a full one #

The partial index is smaller, cheaper to maintain, and — for a queue where 99.99% of rows are not queued — dramatically more efficient. Giving that up to satisfy a driver’s parameterisation is a real loss. But a full (status, run_at) index is parameter-proof and serves every status value, which matters when the same query shape is used for several states.

A middle path suits many applications: keep the partial index for the hot path and write that one query with a literal, since the poller genuinely only ever wants queued jobs, while leaving the general-purpose queries parameterised against a full index. Inlining a value that is a constant of the code — not user input — introduces no injection risk and costs nothing at the driver level.

Where several hot states exist, one partial index per state with literal-bearing queries is still viable, and keeps each index tiny. Beyond a handful of states, the maintenance burden outweighs the benefit and a full composite index is simpler.

Common Pitfalls #

Testing only in psql. Literals there hide the problem. Diagnostic signal: fast in psql, slow in the application. Fix: capture plans from the application role.

Assuming ORMs send literals. Most parameterise everything. Diagnostic signal: $n in logged plans. Fix: use the ORM’s raw SQL escape hatch for the hot query.

Forcing custom plans globally. Every statement re-plans, adding CPU. Diagnostic signal: planning time rising across the workload. Fix: scope to the role or statement that needs it.

Predicate not implied even with a literal. WHERE status IN ('queued','retry') does not imply status = 'queued'. Diagnostic signal: index unused despite a literal. Fix: match the predicate or broaden the index.

Frequently Asked Questions #

Why does my partial index work in psql but not from my application? #

psql sends literals, so the planner can prove the query implies the index predicate. Applications usually send parameters, and once the prepared statement switches to a generic plan the value is unknown, so the partial index cannot be proven usable.

Do custom plans use partial indexes with parameters? #

Yes. When a prepared statement is planned with bound values — as custom plans are — the value is substituted at planning time and the implication can be proven.

How do I keep using a partial index from an application? #

Inline the predicate value as a literal for that query, force custom plans for the statement or role, or replace the partial index with a full index that does not depend on a predicate.

Partial Index Implementation — parent guide: predicate design
Partial Index Predicate Matching Rules — sibling: the implication rules
Generic vs Custom Plan Selection — when the switch happens

Up: Partial Index Implementation