TIL 31 January 2024

Postgres partial indexes for a WHERE you always use

We had a slow query that filtered on a column where almost every row held the same value, and only a small slice was the one we actually looked for. A plain index on that column barely helped, because the index ended up almost as large as the table. Turns out Postgres lets you index only the rows you care about:

CREATE INDEX index_identities_on_value
  ON identities (value)
  WHERE identity_type = 'email';

The index only stores rows where identity_type = ‘email’, so it is smaller, and the planner still uses it as long as the query carries the same WHERE. The query went from a few seconds to a few milliseconds. One catch: the condition in the query has to match the condition in the index, or Postgres just ignores it.