Partial indexes: the one place skew is an asset

When does indexing only some rows pay, and why can a parameterized query not use the index?

The constraint is that index cost scales with the number of entries maintained, not with table size. Those are the same number only when you index everything.

If a filter predicate selects fraction p of the rows, the partial index is p× the size, intercepts p× the writes, and, because it is smaller, is shorter and more likely to stay in memory. Three wins that compound.

The canonical case: a work queue. The pickups table has 10⁸ rows, of which 10³ are pending. A full index on status is a 10⁸-entry structure whose useful part is scattered through it, and whose maintenance is paid on every insert and every state change. A partial index

CREATE INDEX pickups_pending ON pickups (created_at) WHERE status = 'pending';

holds about a thousand entries, is permanently cached, is two levels deep, and costs nothing on the 99.999% of writes that do not match. The "find work" query stays flat as the table grows by two more orders of magnitude.

Note what just happened. Elsewhere in storage, skew (very uneven distribution of values or access) is the enemy: variance costs more than volume. It ruins cache hit rates and cardinality estimates. Here the skew is the mechanism. The same distributional skew that ruins cache hit rates and cardinality estimates is what makes partial indexes nearly free. This is not a contradiction. Skew is a cost when it is unknown and unexploited, and an asset when it is statically describable.

The boundary condition is logical, not physical, and everyone hits it. To use a partial index, the planner must prove that the query's predicate implies the index's predicate. Implication proving in real planners is syntactic matching plus simple range reasoning. So WHERE status = 'pending' matches an index defined WHERE status = 'pending', but WHERE status = $1 does not, even if $1 happens to be 'pending' at run time, because the proof has to hold for all possible values of the parameter. Your ORM sends parameterized SQL. Your partial index goes unused and you never see an error, just a sequential scan. (This is Priya's first symptom.)

A unique partial index is also how you express "unique among live rows" under soft deletes (rows marked deleted rather than removed):

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

There is no other clean way to say that.