How to Use Partial Indexes in PostgreSQL

When a PostgreSQL table has many rows and queries often target a smaller group, a partial index helps PostgreSQL find those rows without indexing the whole table. A partial index stores entries only for rows that meet a condition, called the predicate. For example, an index can cover open orders and leave completed orders out.

A partial index helps when queries often match its condition and the matching rows make up a small share of the table. Choose a condition that appears in frequent queries, create an index with a matching WHERE clause, and check whether PostgreSQL uses it.

Create an index for a frequent query

Suppose a service often looks up open orders for one customer and sorts them by creation time. You could create an index like this:

CREATE INDEX orders_open_customer_created_idx
ON orders (customer_id, created_at)
WHERE status = 'open';

The WHERE clause limits the index to open orders. The indexed columns, customer_id and created_at, help PostgreSQL find a customer’s matching orders and return them in creation-time order. The predicate column does not have to appear among the indexed columns. PostgreSQL’s partial-index documentation describes this syntax and the conditions for using these indexes.

A query that matches the predicate and uses the indexed columns could look like this:

SELECT id, created_at
FROM orders
WHERE status = 'open'
  AND customer_id = 42
ORDER BY created_at;

PostgreSQL can use the index because the query’s WHERE condition implies that every returned row has status = 'open'. Replace the example condition with a condition that reflects your own query pattern. A partial index is most useful when that pattern is common and the matching rows make up a substantially smaller portion of the table.

Match the query to the index predicate

PostgreSQL uses a partial index only when its query planner can show that the query condition implies the index predicate. An exact match is the clearest case: an index defined with WHERE status = 'open' can support a query that includes status = 'open'. PostgreSQL also recognizes some simple implications, such as a query condition that is stricter than the predicate. The planner does not prove every logically equivalent expression.

If the index predicate says status = 'open', a query with a different filter expression may not qualify, even if you expect it to select the same rows. Check the SQL sent to PostgreSQL and keep its condition aligned with the predicate.

When PostgreSQL plans a generic prepared plan for status = $1, it does not know the parameter’s value, so it cannot prove that the query implies status = 'open'. If your prepared statement uses a parameter for the predicate value, check the plan under the same conditions as production. Do not assume PostgreSQL will use the partial index.

Check whether PostgreSQL uses the index

Use EXPLAIN to see the plan PostgreSQL chooses:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE status = 'open'
  AND customer_id = 42
ORDER BY created_at;

Look for a plan node that names orders_open_customer_created_idx, such as an index scan or bitmap index scan. PostgreSQL may still choose a sequential scan, which reads the table, when it estimates that scanning the table costs less. A small table or an index that covers a large share of its rows can make that choice reasonable.

To compare estimates with actual results, use EXPLAIN (ANALYZE, BUFFERS). ANALYZE runs the query, so use it only when running that query is safe in your environment. If the plan’s row estimates look inaccurate, refresh table statistics with ANALYZE orders;. Updated statistics help PostgreSQL estimate how many rows match. They do not make the index usable if the query does not imply its predicate.

Weigh smaller indexes against write costs

When the predicate selects a small subset, a partial index stores fewer entries than an index on the same columns across the whole table. This reduces storage and the work PostgreSQL does to maintain the index as rows change. Rows that do not meet the predicate need no entries.

Rows that enter or leave the indexed subset still affect the index. For example, changing an order’s status from closed to open adds it to the partial index; changing it back removes it. Each additional index also adds write work, so create partial indexes for queries that need them rather than indexing every possible status or condition.

Recheck the index when data distribution or query patterns change. If most rows now meet the predicate, the index saves less space and may offer little advantage over a broader index. The PostgreSQL documentation cautions against using many partial indexes instead of choosing an index that fits the workload.

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