A partial index (called a filtered index in SQL Server) is an index that only includes rows satisfying a specified WHERE predicate. Instead of indexing every row in the table, it indexes only a subset — making the index dramatically smaller, faster to scan, and cheaper to maintain.

Syntax

-- PostgreSQL partial index
CREATE INDEX idx_pending_orders ON orders(created_at)
  WHERE status = 'pending';

-- SQL Server filtered index
CREATE INDEX idx_pending_orders ON orders(created_at)
  WHERE status = 'pending';

This index only contains rows where status = 'pending'. If only 2% of orders are pending, the index is ~50x smaller than a full index on created_at.

When the query planner uses a partial index

The database will only use a partial index when the query's WHERE clause implies the index predicate. For the index above:

• WHERE status = 'pending' AND created_at > '2024-01-01' — YES, the query explicitly filters on status = 'pending', which matches the index predicate.
• WHERE created_at > '2024-01-01' — NO, the query does not restrict to pending orders, so the partial index might miss non-pending rows.
• WHERE status = 'pending' AND priority = 'high' — YES, the query's WHERE implies status = 'pending', so the partial index can be used (the priority filter is applied after the index scan).

Why partial indexes are powerful

1. Smaller size: fewer rows means fewer B+ tree pages, using less disk and buffer pool memory.
2. Faster scans: scanning a 1 MB index is faster than scanning a 50 MB index.
3. Cheaper maintenance: INSERTs/UPDATEs/DELETEs only touch the index when the row matches the predicate. An INSERT with status = 'shipped' does not write to the partial index at all.
4. Hot subset pattern: many applications have a "hot" subset of active rows (pending orders, unread messages, open tickets) that queries focus on, while the vast majority are "cold" (completed, read, closed). A partial index on the hot subset is ideal.

Unique partial index

A powerful use case: enforce uniqueness only on a subset of rows.

-- Each user can have at most one 'active' subscription
CREATE UNIQUE INDEX uniq_active_sub ON subscriptions(user_id)
  WHERE status = 'active';

This allows multiple rows per user (old subscriptions with status = 'cancelled'), but enforces that at most one row per user has status = 'active'. This is impossible to express with a standard unique constraint.

Limitations

• Not all databases support partial indexes (MySQL does NOT; PostgreSQL and SQL Server do).
• The predicate must be a simple expression (no subqueries, no volatile functions).
• The query planner must be able to prove the query's WHERE clause implies the index predicate — complex conditions may not be recognized.