Functions, and the index that only holds some rows
Wrapping a column in a function throws its index away, because the index stores the column and you asked about something else.
10 min · runs against the events dataset
An index on email stores email. A query about lower(email) is asking about a different value, and no amount of wanting makes the sorted thing sorted by it. The plan says so plainly, and this is the single most common way a query that “has an index” does not use it.
The column itself.
plan
| node | est |
|---|---|
| Bitmap Heap Scan on eventsRecheck Cond: (email = '[email protected]') | 20 |
| → Bitmap Index Scan on events_email_idxIndex Cond: (email = '[email protected]') | 20 |
The same column, one function later.
plan
| node | est |
|---|---|
| Seq Scan on eventsFilter: (lower(email) = '[email protected]') | 40 |
The fix is to index the expression instead. An index on lower(email) stores exactly what the query asks for, and the plan changes back — same mechanism, different key.
The index that only holds what you look at
A partial index carries only the rows its predicate accepts. On a table where you constantly query one small slice — jobs still pending, accounts not yet deleted — the index is a fraction of the size, a fraction of the write cost, and every bit as fast for the query it was built for. The planner will only use it where it can tell the predicate holds.
An index over the first number is a great deal smaller than one over the second.
| errorsbigint | all_eventsbigint |
|---|---|
| 82 | 8000 |
1 row · 8,000 examined · 91 pages read
Did it land?
- The function that costs you the index
Return the ids of every event whose email is '[email protected]', regardless of how it was capitalised in the query — oldest id first. There is no index on lower(email), so find the way to ask that keeps the index you do have.