How Database Indexes Work (and Why Yours Might Be Useless)

A database index is a sorted structure where each entry holds a column value and a row locator. In Postgres heap tables, that locator is a ctid (physical page address). In InnoDB, it's the primary key value — meaning every secondary index lookup requires a second B-tree descent to reach the actual row.

The second fetch is random I/O, and it's the reason the query planner ignores your index once a query touches roughly 5-15% of the table. Low-cardinality columns (booleans, status fields with 3 values) produce indexes the planner will almost never use. Partial indexes targeting the rare value are the workaround.

Every index also carries a write penalty: maintenance on every INSERT, UPDATE, and DELETE, plus expression recomputation and MVCC-driven bloat in Postgres. Functions wrapping columns, type mismatches, and leading wildcards silently block usage. Each has a specific fix.