Indexes

An index is a secondary access structure — most often a B-tree — that lets the engine find rows by key without scanning the whole table. Every index accelerates some reads and taxes every write that touches indexed columns.

| Index shape | Typical use | |-------------|-------------| | Single-column B-tree | Equality/range on one predicate | | Composite `(a, b, c)` | Multi-column filters; left-prefix rules apply | | Covering (includes extra columns) | Index-only scans avoid heap lookups | | Partial `WHERE active` | Smaller index for a hot subset | | Hash / GIN / GiST | Engine-specific: equality, JSON, geo, full-text |

					CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC)
  WHERE status <> 'cancelled';
				

Design from real query shapes: leading column matches the most selective equality filter; avoid indexing low-cardinality flags alone. Monitor write amplification, index bloat, and unused indexes in production.

On interviews: given a slow query, propose an index and explain what writes and storage it costs.

Common pitfalls: indexing every column in `WHERE`; wrong column order in composites; redundant overlapping indexes; missing index on FK columns; creating indexes before measuring plans.

The trade-off is balancing read latency against write throughput, memory, and maintenance (vacuum/rebuild) — add indexes for proven hot paths, not hypothetical filters.

Checklist:

• Quote the exact predicate and `ORDER BY` the index serves.
• Order composite columns by selectivity and left-prefix use.
• Mention covering columns if index-only scan is the goal.
• State write and storage cost explicitly.
• Plan how you verify with `EXPLAIN` after deployment.