Index Only What You Query
A partial index covers only the rows that satisfy a predicate, instead of every row in the table. For example, an index on orders where status is active skips all the completed and cancelled rows. The index holds far fewer entries.
Why It Helps
- Smaller and faster: fewer entries mean a shallower tree and fewer pages to cache and read.
- Cheaper writes: rows outside the predicate are not indexed, so inserts and updates that do not match skip index maintenance.
- Targets skew: when most rows share one common value and queries only want the rare one, indexing just the rare rows is far more selective.
The Catch
The optimizer can use a partial index only when the query's filter implies the index predicate. A query that does not include the condition, or includes a broader one, falls back to a scan or another index. So a partial index fits when your queries consistently carry the same filter, like only ever fetching unprocessed jobs.
Key idea
A partial index covers only rows matching a predicate, making it smaller, faster, and cheaper to maintain, but the optimizer can use it only when the query filter implies that predicate.