Partial indexes, and the query that refuses to use them

You add CREATE INDEX ... WHERE deleted_at IS NULL to speed up the soft-delete pattern, and the query planner keeps choosing a sequential scan. The index is fine. The query does not match it.

What a partial index is for

A partial index covers only the rows that satisfy its predicate. If 95 percent of a table is archived or soft-deleted, an index over the live 5 percent is a fraction of the size, faster to scan, cheaper to maintain on writes, and it often fits in memory when the full index would not.

CREATE INDEX orders_open_idx ON orders (customer_id)
WHERE status = 'open';

Unique constraints work the same way, which allows "only one active subscription per user" without blocking old, cancelled rows:

CREATE UNIQUE INDEX one_active_sub ON subscriptions (user_id)
WHERE ended_at IS NULL;

When the planner will use it

PostgreSQL uses a partial index only if it can prove that the query's WHERE clause implies the index predicate. It does simple logical reasoning, not creative reasoning:

• WHERE customer_id = 7 AND status = 'open' matches orders_open_idx.
• WHERE customer_id = 7 alone does not, because rows with other statuses could match the query and are not in the index.
• WHERE customer_id = 7 AND status = $1 with a parameter can fail to match in a generic plan, since the planner does not know the value will be 'open'. Prepared statements that end up with a generic plan hit this. Either write the constant into the query or check with EXPLAIN under the same conditions your application uses.
• Expressions matter: a predicate on lower(status) requires the query to use the same expression.

Index-only scans

If every column the query needs is in the index, PostgreSQL can answer from the index alone, without visiting the table. It still has to check that each row is visible, using the visibility map, so EXPLAIN (ANALYZE) shows Heap Fetches: n. A high number means the visibility map is out of date, and running VACUUM on the table helps. Columns that a query only reads, and does not filter on, can be added with INCLUDE, available since PostgreSQL 11:

CREATE INDEX orders_open_cover ON orders (customer_id) INCLUDE (total, created_at)
WHERE status = 'open';

Practical rules

• Confirm with EXPLAIN, not with hope. The plan's Index Cond and Filter lines show which part of your WHERE the index handled.
• Django exposes this with models.Index(fields=[...], condition=Q(...)) and UniqueConstraint(condition=...).
• Keep the predicate boring. Status columns with a small set of stable values and null checks work well. Predicates that use volatile functions are not allowed.
• A partial index is only as good as the assumption behind it. If the "rare" status becomes common, the index grows and the advantage disappears.

Confirming the index is actually earning its keep

Creating a partial index and assuming it helps is not the same as checking that it does. pg_stat_user_indexes records how many times each index has actually been used by a scan:

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders';

An index with idx_scan at or near zero, weeks after creation, either is not matched by any real query (the predicate mismatch problem above, happening silently in production rather than in a single EXPLAIN you happened to check) or the queries that should use it are not being run in the pattern you assumed. It is also a maintenance cost with no return: every insert and update against the covered rows still has to maintain the index, whether or not anything ever reads from it, so an unused partial index is worth dropping once you have confirmed the mismatch, not left in place on the assumption it is at least harmless.