PostgreSQL Partial Indexes Explained (With Examples)

Most production tables are not queried uniformly: a small, well-defined slice of rows absorbs most of the traffic, while the rest is historical data that is rarely touched. PostgreSQL partial indexes let you index only that slice. Instead of paying to index every row, you attach a predicate to CREATE INDEX and pay only for the rows your queries actually need. This guide explains how partial indexes work, when they beat full indexes, how the planner decides to use them, and the pitfalls to avoid.

A partial index stores entries only for rows that satisfy its WHERE predicate.

PostgreSQL partial indexes store entries for only the rows that satisfy a predicate — a WHERE clause attached to the CREATE INDEX statement. Rows that do not match are simply absent from the index, which makes it smaller, faster to scan, and cheaper to update. Partial indexes are sometimes called conditional indexes for exactly this reason.

The table itself is unchanged: every row stays fully stored and queryable. A partial index is purely an access-path optimization for the subset of data your workload actually touches — a pattern the official documentation recommends for tables where most queries hit the same minority of rows.

How a partial index differs from a full index

A full index contains an entry for every row. A partial index contains entries only for rows matching its predicate, and that single difference drives everything else:

• Size: it scales with the number of matching rows, not the table size.
• Write overhead: index maintenance only happens for rows that satisfy the predicate.
• Maintenance: smaller indexes also give VACUUM less work to do and speed up scans.
• Applicability: the planner considers it only for queries whose conditions imply the predicate — more on that below.

Correctness is never in question: when a row starts or stops matching the predicate, PostgreSQL maintains the index automatically. Partial indexes never go stale with respect to the data.

CREATE INDEX ... WHERE syntax with a real example

Consider an orders table where a small slice of rows is active at any moment and the rest are historical:

CREATE INDEX idx_orders_active_created
    ON orders (created_at)
    WHERE status = 'active';

A query that targets active orders contains the predicate as one of its conditions, so the planner can use the index:

SELECT order_id, customer_id, created_at
FROM orders
WHERE status = 'active'
  AND created_at > now() - interval '7 days';

The predicate may reference any columns and any immutable expressions, the index itself can be multi-column, and any access method works — including the default b-tree. The full grammar is in the CREATE INDEX reference.

Indexing a hot subset of rows

The classic case: queries almost always target a small, well-defined slice — open carts, unshipped orders, pending jobs — while the bulk of rows are historical. Index only the hot rows:

CREATE INDEX idx_jobs_pending
    ON jobs (scheduled_at)
    WHERE status = 'pending';

Workers polling for due jobs scan a tiny index instead of a full one, and the constant churn of completed jobs never touches it.

Enforcing uniqueness on a subset of rows

A unique partial index enforces uniqueness only within the subset — ideal for one active X per Y rules and soft deletes:

CREATE UNIQUE INDEX idx_sessions_one_active
    ON sessions (user_id)
    WHERE ended_at IS NULL;

This guarantees at most one open session per user while allowing unlimited historical rows — something a plain unique constraint cannot express. The same trick frees an email address for reuse once a user is soft-deleted:

CREATE UNIQUE INDEX idx_users_email_alive
    ON users (email)
    WHERE deleted_at IS NULL;

Handling sparse data and NULL-heavy columns

When most rows hold NULL values in a column and queries only care about rows where a value exists, index just those rows. For very sparse data, this keeps the index a small fraction of the full size while still serving every query that filters on the column:

CREATE INDEX idx_invoices_po_number
    ON invoices (po_number)
    WHERE po_number IS NOT NULL;

A partial index only helps if the planner can prove from your query that only matching rows can be returned. That proof — and its limits — explains most cases of a partial index not being used.

Predicate-matching requirements

The planner uses a partial index only when the query's WHERE clause contains conditions that imply the index predicate. An exact match is the easy case: predicate status = 'active', query condition status = 'active'. The implication logic handles simple cases such as equality and range constraints and basic AND/OR combinations, but it is deliberately conservative. It will not, for example, infer status = 'active' from status <> 'delivered', even though a human can.

Practical rule: repeat the index predicate verbatim in your queries. A redundant extra condition costs nothing and makes the match unambiguous.

Immutable expressions only: why NOW() fails

Everything in the predicate must be immutable, because it is frozen into the index at creation time. This statement fails:

CREATE INDEX idx_orders_recent
    ON orders (created_at)
    WHERE created_at > now() - interval '30 days';
-- ERROR:  functions in index predicate must be marked IMMUTABLE

now() returns a different value on every call, so PostgreSQL cannot guarantee the predicate stays true. For a rolling time window, use a constant cutoff that you rebuild on a schedule, or a full index on the column with the date filter left to the query.

Verifying usage with EXPLAIN and pg_stat_user_indexes

Confirm the planner picks the index:

EXPLAIN
SELECT order_id FROM orders
WHERE status = 'active'
  AND created_at > now() - interval '7 days';

Look for Index Scan using idx_orders_active_created in the execution plan. For ongoing usage counts, check the statistics views:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

An idx_scan of 0 since the last statistics reset is a strong signal the index is dead weight. The guide to examining index usage covers this workflow in depth.

When a full index is the better choice

• Query patterns vary. If queries filter the column in many different ways, a partial index serves only some of them and you end up maintaining both.
• The subset is most of the table. If the predicate matches the majority of rows, the savings are marginal and a full index is simpler.
• Many sibling subsets. Five partial indexes, one per status value, add up to roughly the cost of one full index plus extra catalog complexity.

A useful mental model: reach for a partial index when a clear, stable minority of rows carries most of the workload.

• Predicate/query mismatch. The most common reason a partial index is ignored is a query that does not imply the predicate. Include the predicate condition explicitly and verify with EXPLAIN.
• Mutable predicates. Functions such as now(), or any function not marked IMMUTABLE, are rejected in index predicates.
• Overlapping partial indexes. Every extra index costs disk, memory, and planning time; consolidate subsets that overlap heavily.
• Orphaned predicates. Workloads drift. Review pg_stat_user_indexes periodically and drop indexes with near-zero scans.
• Wrong expectations on size. Savings track the fraction of rows excluded, not the table's absolute size.

In DBO Studio's schema browser, each index shows its full definition — including the WHERE predicate — next to its size, which makes oversized or duplicated indexes easy to spot. Run EXPLAIN ANALYZE from the query editor to confirm the planner chooses the partial index, and compare plans before and after adding one.

You can download DBO Studio to work with PostgreSQL, MySQL, SQLite, SQL Server, and more; the release notes list recent improvements to the index and plan views.