How to Use Partial Indexes in PostgreSQL

If most queries ignore many rows in a PostgreSQL table, a regular index still uses space to index them. A partial index covers only rows that meet a condition, such as orders that have not been billed. That condition is called the index’s predicate.

Use a partial index when your queries repeatedly search a stable subset of rows. Write those queries so PostgreSQL can match their WHERE conditions to the index predicate, then check whether the planner uses the index.

Create an index for the rows you query

The basic syntax adds a WHERE clause to CREATE INDEX:

CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;

For example, suppose an orders table contains billed and unbilled orders, and your queries look up unbilled orders by order number:

CREATE INDEX orders_unbilled_order_nr_idx
ON orders (order_nr)
WHERE billed IS NOT TRUE;

The index contains order_nr values only for rows where billed IS NOT TRUE. The query can use it when it includes a condition PostgreSQL recognizes as implying the index predicate:

SELECT *
FROM orders
WHERE billed IS NOT TRUE
  AND order_nr = 123;

The indexed column and the predicate column do not need to match. Here, PostgreSQL indexes order_nr but uses billed to decide which rows belong in the index. The predicate can refer to other columns in the same table, subject to the rules in the CREATE INDEX documentation.

Make the query condition match the predicate

PostgreSQL uses a partial index only when it can determine that the query’s WHERE clause implies the index predicate. The planner recognizes some simple implications, such as when the query condition is more restrictive than the index predicate. For other cases, write the predicate itself in the query. The PostgreSQL documentation on partial indexes explains this matching requirement.

For example, if the index predicate is billed IS NOT TRUE, include that condition in the query. A query that filters only on order_nr does not tell PostgreSQL that it needs only unbilled rows, so it cannot rely on the partial index to find all matching orders.

Parameterized conditions also affect predicate matching. If the index predicate is status = 'open', a query that uses status = $1 does not establish that the requested status is always 'open'. Put the fixed condition in the query itself if you want PostgreSQL to match that predicate:

SELECT *
FROM tickets
WHERE status = 'open'
  AND customer_id = $1;

Here, the parameter applies to customer_id; the query still states the partial index condition directly.

Choose a subset that stays useful

Partial indexes work best when their predicate selects a useful, relatively stable group of rows. For example, an index on unbilled orders helps when queries repeatedly search that subset. It also avoids storing entries for billed orders. PostgreSQL does not need to maintain entries for billed orders when their other columns change, so the index uses less storage and requires less update work.

Before creating an index, check how many rows meet its predicate and whether your queries need those rows. If the predicate includes most of the table, a regular index may serve more queries. If the distribution changes, the partial index may become less useful; PostgreSQL’s documentation recommends reassessing the index when common values or data patterns shift.

The predicate must use columns from the table being indexed. It cannot contain subqueries or aggregate expressions. Functions and operators in the index definition must also be immutable, meaning they return the same results for the same inputs. PostgreSQL enforces these restrictions when you create the index, as described in CREATE INDEX.

Enforce uniqueness for only part of a table

A partial unique index enforces uniqueness among rows that meet its predicate. For example, if usernames need to be unique only for active users, create:

CREATE UNIQUE INDEX active_users_username_idx
ON users (username)
WHERE active;

PostgreSQL then rejects duplicate usernames among active users while allowing the same username on rows outside that subset. The predicate defines which rows the uniqueness rule covers. The PostgreSQL index documentation describes partial indexes as a way to apply uniqueness to a subset of a table.

Check whether PostgreSQL uses the index

Creating a partial index does not guarantee that PostgreSQL will choose it. The planner estimates how many rows the query will return and how much work each plan requires, then compares the plans. A query that returns a large share of the table may be faster with a sequential scan, which reads the table directly.

Run EXPLAIN on the query to inspect the planned access path. Use EXPLAIN (ANALYZE, BUFFERS) in a suitable test environment to run the query and see actual execution time and buffer activity. Compare results before and after adding the index, using representative data and query conditions. Performance depends on the table’s data distribution and the workload; results from one dataset do not predict results for another.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com