Index
Partial index
Index built with a WHERE clause so only rows matching the predicate are stored, keeping the index small and selective.
A partial index includes only rows that satisfy a WHERE clause in the CREATE INDEX statement. Common use cases are indexing the small "hot" subset of a table — open orders, soft-deleted rows, recent records — without paying the cost of indexing every dead or archived row.
CREATE INDEX orders_open_idx ON orders (created_at)
WHERE status = 'open';
The planner can use a partial index whenever the query's WHERE clause logically implies the index's predicate. Skip the partial index if the planner cannot prove that match — a query for status IN ('open', 'pending') will not use an index restricted to status = 'open'.
Partial indexes reduce both index size and write amplification, since rows that do not match the predicate skip the index entirely on insert and update.