Partial index
What is a partial index in PostgreSQL?
A partial index covers only the rows that match a WHERE condition, which keeps it small and fast when queries target a well-defined subset of a table.
A partial index is an index built over a subset of a table, defined by a condition in CREATE INDEX ... WHERE. Only rows that satisfy the condition get index entries.
Partial indexes pay off when queries focus on a small slice of a table, such as unfinished tasks or unpaid orders, while most rows are rarely queried. The planner can use the index only when it can prove that the query's WHERE clause implies the index condition, so write the query's condition to match it. A partial unique index can also enforce uniqueness within just that subset.
Example
The example indexes only the 1% of tasks that are not done. EXPLAIN shows the query for open tasks using tasks_open_idx.
CREATE TABLE tasks (id int PRIMARY KEY, done boolean NOT NULL, title text);
INSERT INTO tasks
SELECT g, g % 100 <> 0, 'task ' || g FROM generate_series(1, 100000) AS g;
CREATE INDEX tasks_open_idx ON tasks (id) WHERE NOT done;
ANALYZE tasks;
EXPLAIN SELECT id, title FROM tasks WHERE NOT done;Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).