Indexes that earn their keep

The B-tree, and why column order matters

Postgres's default index type is a B-tree — a sorted, balanced tree that lets it jump to a matching value in roughly logarithmic time instead of scanning every row. Reach for one whenever a column shows up often in a WHERE, a JOIN, or an ORDER BY on a table big enough that a seq scan hurts.

A composite index covers more than one column, and column order is not cosmetic — it follows the leftmost-prefix rule. An index on (walker_id, status) can serve a query that filters on walker_id alone, or on walker_id AND status together, because both start by narrowing on the leftmost column first. It can NOT efficiently serve a query that filters on status alone — that's like using a phone book sorted by last name to look someone up by first name only. Rule of thumb: put the column used for equality (walker_id = 42) before the column used for a range or sort (status, created_at).