A queue with a hundred ready jobs and thousands of completed jobs can benefit from an index containing only ready work. PostgreSQL calls this a partial index: its predicate determines which table rows have entries. The benefit depends on the actual query population and write pattern, not simply on making every index smaller.

When it breaks

The direction of implication matters. The query conditions must imply the index predicate, so every row the query might need is represented. A ready-only index cannot answer an unrestricted query about all jobs. Merely having compatible-looking conditions is insufficient, and PostgreSQL does not prove every mathematical equivalence. A predicate written close to the application’s filter is easier for the planner and reviewers to recognize.

Planning also determines what values are known. A custom prepared plan can use the supplied state value to choose a partial index. A generic plan must be valid for every possible parameter value; it cannot assume that a state parameter always means ready. This explains why testing a literal query can look good while an application’s prepared query uses a different access path.

Check it quickly

Run the blocks in one fresh psql -X -v ON_ERROR_STOP=1 session on a disposable PostgreSQL 18 database. The fixture has no full-table index, making the eligible access paths easy to inspect. Create ten thousand jobs, of which exactly one hundred are ready:

CREATE SCHEMA partial_demo;
CREATE TABLE partial_demo.jobs (id integer,state text);
INSERT INTO partial_demo.jobs
SELECT g,CASE WHEN g%100=0 THEN 'ready' ELSE 'done' END
FROM generate_series(1,10000) AS g;
CREATE INDEX ready_jobs ON partial_demo.jobs(id) WHERE state='ready';
VACUUM (ANALYZE) partial_demo.jobs;
SELECT state,count(*) FROM partial_demo.jobs GROUP BY state ORDER BY state;

The grouped counts are 9,900 done and 100 ready. The indexed column is the job identifier, while the predicate uses state; those columns need not be the same. Inspect a literal query:

EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF,SUMMARY OFF,FORMAT JSON)
SELECT id FROM partial_demo.jobs WHERE state='ready' ORDER BY id LIMIT 5;

The tested plan contains an Index Only Scan using ready_jobs beneath Limit, returning five rows. Vacuum established the visibility information needed to avoid heap fetches in this fixture. Partial indexing alone does not promise an index-only scan: selected columns and visibility still matter. The important eligibility evidence is the index name and correct result.

Prepare the same request with a state parameter and force a custom plan for comparison:

PREPARE next_jobs(text) AS
SELECT id FROM partial_demo.jobs WHERE state=$1 ORDER BY id LIMIT 5;
SET plan_cache_mode=force_custom_plan;

EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF,SUMMARY OFF,FORMAT JSON)
EXECUTE next_jobs('ready');

This plan also uses ready_jobs, because the supplied ready value is available when planning. Now request a generic plan for the same prepared statement:

SET plan_cache_mode=force_generic_plan;

EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF,SUMMARY OFF,FORMAT JSON)
EXECUTE next_jobs('ready');

The generic plan scans the table, filters out 9,900 rows, and sorts the matching identifiers before taking five. Its state expression remains a parameter, so the ready-only index cannot cover every possible execution. These forced modes isolate the distinction; they are diagnostic controls, not a recommendation to force one mode globally.

Execute both parameter values, then move one ready job across the predicate boundary:

EXECUTE next_jobs('ready');
EXECUTE next_jobs('done');
DEALLOCATE next_jobs;
RESET plan_cache_mode;
UPDATE partial_demo.jobs SET state='done' WHERE id=100;
SELECT id FROM partial_demo.jobs WHERE state='ready' ORDER BY id LIMIT 5;
SELECT count(*) FROM partial_demo.jobs WHERE state='ready';
DO $$ BEGIN
  IF (SELECT array_agg(id ORDER BY id) FROM
      (SELECT id FROM partial_demo.jobs WHERE state='ready' ORDER BY id LIMIT 5) AS q)
     IS DISTINCT FROM ARRAY[200,300,400,500,600] THEN
    RAISE EXCEPTION 'partial-index membership result mismatch';
  END IF;
END $$;
DROP SCHEMA partial_demo CASCADE;

The first request returns identifiers 100, 200, 300, 400, and 500. The done request returns one through five. After job 100 becomes done, the first five ready identifiers are 200 through 600 in steps of one hundred, and 99 ready jobs remain. The assertion checks that maintenance follows the updated predicate membership. Removing the prepared statement and schema leaves no experimental data behind.

Evaluate the whole workload

Updates that enter or leave the subset change index membership. Even updates outside it still perform ordinary table work; a partial index does not eliminate all write cost. Measure index size, query plans, and maintenance under representative state transitions. If most jobs become ready, the expected advantage may disappear, and a sequential scan can be the right choice.

A partial unique index enforces uniqueness only among rows satisfying its predicate. Rows outside that subset remain unconstrained by that index, which can be useful for historical records but is wrong for a global identifier rule. Also avoid predicates based on a moving current time: index expressions and predicates require immutable functions, and yesterday’s membership would not automatically change as the clock advances. Prefer an explicitly maintained business state when that state defines the real query contract.

Sources