The Postgres index that looked right and never got used

I added a composite index on (tenant_id, created_at) for a query that filtered by both columns constantly. It should have been an easy win. EXPLAIN ANALYZE said otherwise — a sequential scan, every time.

The query didn't match the index shape

The filter was WHERE tenant_id = $1 AND created_at::date = $2. That cast on created_at was the whole problem — wrapping an indexed column in a function makes it opaque to the planner unless you index the expression itself, not just the column. The fix was either a matching expression index or, better, rewriting the filter as a range (created_at >= $2 AND created_at < $2 + interval '1 day') so the plain composite index applied directly.

Column order matters more than it seems

Postgres can use a leading prefix of a composite index, not an arbitrary subset. (tenant_id, created_at) serves queries filtering on tenant_id alone or tenant_id AND created_at — but does nothing for a query that filters on created_at alone. Get the order wrong and the index sits there, correct in isolation, unused in practice.

Always read the actual plan

EXPLAIN shows what the planner intends to do; EXPLAIN ANALYZE shows what it actually did, with real timings and row counts. The gap between the two — an estimated 40 rows versus an actual 400,000 — is usually where the real story is. I've stopped trusting an index exists just because I remember creating it.