Deep dive
Indexes are additional data structures (often **B-trees**) that let the database find rows without scanning the whole table. They help with:
- `WHERE` filters,
- `JOIN` lookups,
- `ORDER BY` / `GROUP BY` (sometimes),
- uniqueness enforcement (unique indexes).
The main trade-off
Every write must update all relevant indexes, so:
- writes get slower,
- storage increases,
- maintenance work grows (stats/vacuum/reindex depending on DB).
Practical tips
- Prefer indexes on columns with good selectivity (many distinct values).
- For composite indexes, column order matters (left-prefix rule): index on `(a, b)` helps queries filtering by `a` (and by `a` + `b`), but not just by `b`.
- Avoid redundant indexes (they add write cost without benefit).
Common pitfalls
- Indexing boolean/low-cardinality columns and expecting big wins.
- Too many indexes on a hot write table.
- Not verifying with `EXPLAIN` whether the planner actually uses the index.