What an index is, and what it costs
A B-tree is a sorted structure with a real height. Reading it is cheap; going back to the table for each row it found is not, and that is the whole story.
12 min · runs against the events dataset
An index is a second copy of one column — or one expression — kept in sorted order, with a pointer back to the row it came from. Finding a value in it is a descent of a few levels rather than a walk of the whole table. That part is genuinely cheap, and it is not where the cost of an index scan lives.
The cost lives in the second half: for every row the index found, the engine has to go back to the table and read the page that row is on. If the matching rows are scattered, that is one page read per row — and past a few percent of the table, reading every page in order is simply cheaper.
Twenty rows out of eight thousand. Notice how few pages it read.
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 same index, asked for almost everything. It is not used at all.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Seq Scan on eventsFilter: (actor_id > 5)Rows Removed by Filter: 100 | 8K | 7.9K | 1 | 91 |
Selectivity is the whole question
An index earns its place when the predicate it answers is selective — when it eliminates most of the table. An index on a boolean eliminates half at best, which is why one is usually dead weight. An index on a primary key eliminates all but one row, which is why one is almost always worth having.
Four fifths of this table is two values. An index on `kind` alone would rarely help.
| kindtext | eventsbigint |
|---|---|
| view | 3030 |
| click | 3029 |
| signup | 1515 |
| purchase | 344 |
| error | 82 |
5 rows · 8,000 examined · 91 pages read
And every index makes writes slower
Every index is a second structure that every insert, update and delete has to maintain. That cost is invisible in a SELECT plan and entirely real in production — an index added to fix one report is paid for by every write to that table, forever. The tuning gym scores exactly this trade.
Did it land?
- One actor's events
Return the id and duration of every event belonging to actor 137, oldest id first. There is an index on actor_id; the budget assumes you let the planner use it.
- Without touching the table
Return the actor_id of every event for actor 200 — that column and nothing else, ordered by actor_id. The index already carries everything you need, so the table should never be read.