Make a PostgreSQL partial index usable by your query

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

A partial index contains only rows satisfying a predicate. It can be a strong fit for a small active subset inside a much larger history table. But PostgreSQL must be able to establish that the query cannot require rows outside that subset. A logically related predicate or a parameter that happens to have the right value at runtime is not always enough.

Think of a partial index as a conditional access path with a proof obligation. First prove the query is eligible. Then ask whether the planner considers the eligible path cheaper than its alternatives. Those are separate reasons an index may not appear in a plan.

Build an active-job example

This PostgreSQL 18 lab uses a disposable database. The fixture has a small queued subset and a larger completed history.

CREATE TABLE partial_job (
  id bigint PRIMARY KEY,
  state text NOT NULL,
  run_at timestamptz NOT NULL,
  payload text NOT NULL
);
INSERT INTO partial_job
SELECT g,
       CASE WHEN g % 100 = 0 THEN 'queued' ELSE 'done' END,
       timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second',
       'job-' || g
FROM generate_series(1,100000) AS s(g);
CREATE INDEX partial_job_ready
ON partial_job(run_at, id)
WHERE state = 'queued';
ANALYZE partial_job;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, run_at
FROM partial_job
WHERE state = 'queued'
  AND run_at < timestamptz '2026-01-02 00:00:00+00'
ORDER BY run_at, id
LIMIT 20;

The query explicitly includes the index predicate. Its other condition narrows the indexed scheduling range. The index does not need state as a key merely to contain queued rows, because the predicate defines membership. Whether an index-only scan is possible for the selected columns is a separate visibility question.

When a row changes from queued to done, the index must reflect that membership change. A partial index reduces the indexed population; it does not make state transitions free.

Compare literals with generic parameters

A prepared query that accepts arbitrary state values cannot generally use a queued-only index in a generic plan, because the same plan must also be valid for done.

PREPARE jobs_for_state(text, timestamptz) AS
SELECT id, run_at
FROM partial_job
WHERE state = $1 AND run_at < $2
ORDER BY run_at, id
LIMIT 20;

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE jobs_for_state('queued', '2026-01-02 00:00:00+00');
ROLLBACK;

For a queue-specific endpoint, keep the invariant literal while parameterizing the genuinely variable bound:

PREPARE ready_jobs(timestamptz) AS
SELECT id, run_at
FROM partial_job
WHERE state = 'queued' AND run_at < $1
ORDER BY run_at, id
LIMIT 20;

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE ready_jobs('2026-01-02 00:00:00+00');
ROLLBACK;

The generic plan can now reason about queued membership independently of the timestamp parameter. On PostgreSQL 18.6 with plan_cache_mode = force_generic_plan, the state-parameter version planned a sequential scan and sort, while this version planned an index-only scan on partial_job_ready. This is not unsafe string interpolation. The fixed state is part of the endpoint's SQL contract; user-supplied values remain bound parameters.

Reference predicate test. A fixed queued condition establishes membership; an arbitrary state parameter does not establish it for every execution.

Keep predicate forms predictable

PostgreSQL recognizes some simple implications, but its planner is not a general symbolic theorem prover. An application expression that a human sees as equivalent may not match the index predicate in the needed way. Prefer clear, consistent predicate forms rather than elaborate rewrites that depend on clever implication reasoning.

Null semantics are a common trap. state <> 'done' excludes nulls; it is not interchangeable with every expression that means “unfinished” in application code. Boolean columns also invite inconsistent forms. Decide whether null is allowed and write the index predicate and query with that decision visible.

A partial index predicate must satisfy index-expression requirements, including immutability of functions used in the definition. A rolling window such as WHERE created_at > now() - interval '7 days' is not a valid self-maintaining recent-rows design. Time passes without updating row membership. Use a stable state predicate, partitioning, or a reviewed maintenance strategy instead.

Do not confuse eligibility with preference

Even when the query implies the predicate, PostgreSQL can choose another path. A tiny table may be cheaper to scan. A query returning nearly every indexed row may favor a different plan. Outdated statistics can distort cost comparisons. Inspect the complete plan and relevant row counts before concluding that predicate matching failed.

Test the actual client protocol. Some drivers initially execute unprepared statements and later switch to server-side preparation. Pooling can change which connection owns the prepared statement. A manual literal query in a database console may therefore miss the behavior that appears only after an application has been running for a while.

Review the lifecycle of the indexed subset

Measure how large the queued population becomes during an outage, not just during normal operation. A queue that is usually tiny can become most of the table when consumers stop. The best plan and maintenance cost may change at exactly the time the system is stressed.

Also inspect whether the index enforces a business rule. A partial unique index can enforce uniqueness only among matching rows, such as one active subscription per account. That is a stronger commitment than a performance index and needs tests for concurrent state changes and null behavior.

A good partial-index review ends with a precise statement: which rows belong, which query clause proves membership, how prepared plans see that clause, and how the subset behaves under backlog or state changes. The compact index is the result of that contract, not a substitute for it.

Sources and review scope

• Partial indexes
• PREPARE
• CREATE INDEX