Column order, and the index that answers on its own
An index on (a, b) can seek on a, and an index on (b, a) cannot. And an index that carries every column the query wants never touches the table.
12 min · runs against the events dataset
A composite index is sorted by its whole key, in order — first by the first column, then within each of those by the second, and so on. That single fact produces the left-prefix rule: you can seek on a leading run of the key, and nothing else. An index on (kind, happened_on) answers a question about kind, and a question about kind and happened_on together, and no question about happened_on alone.
Both columns, in key order. The index pins both.
plan
| node | est |
|---|---|
| Bitmap Heap Scan on eventsRecheck Cond: (kind = 'error') AND (happened_on > ('2024-06-01')::date) | 356 |
| → Bitmap Index Scan on events_kind_happened_idxIndex Cond: (kind = 'error') AND (happened_on > ('2024-06-01')::date) | 356 |
The second column alone. The index is no help and the planner does not pretend otherwise.
plan
| node | est |
|---|---|
| Seq Scan on eventsFilter: (happened_on > ('2024-06-01')::date) | 4.4K |
The index that does not need the table
If every column a query touches is already in the index, there is nothing to go back to the table for. Postgres calls that an index-only scan, and the difference is the expensive half of the previous lesson simply not happening.
Only the indexed column is wanted, so the table is never read.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Index Only Scan using events_actor_idx on eventsIndex Cond: (actor_id = 42) | 20 | 20 | 1 | 3 |
One more column, and the heap fetches are back.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Bitmap Heap Scan on eventsRecheck Cond: (actor_id = 42) | 20 | 20 | 1 | 20 | |
| → Bitmap Index Scan on events_actor_idxIndex Cond: (actor_id = 42) | 20 | 20 | 1 | 3 |
The fix is INCLUDE: carry the extra column in the index's leaves without making it part of the key. It does not help the seek, it is not sorted by, and it turns an index scan into an index-only scan — which is often the largest single win available on a hot query.
Did it land?
- Both halves of the key
Return the ids of every 'error' event that happened on or after 2024-06-01, oldest first. There is an index on (kind, happened_on).