What is an index and what does it cost?

Most relational indexes are B-trees. They support equality, range, prefix, and ordered scans efficiently. A query can use an index to find a small set of rows without scanning the whole table, but the planner may still choose a sequential scan when the index would touch too much of the table.

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

SELECT id, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;

The trade-offs

• Reads improve when the predicate is selective and the index is used
• Every insert, update, and delete must maintain the index
• Indexes consume disk and memory, which can evict useful pages
• Covering indexes can satisfy a query from the index alone
• Low-selectivity columns such as a boolean often do not benefit from a plain index
• Unused indexes still cost writes, so audit them

Index the columns used for joins, selective filters, and required sort orders. For write-heavy tables, keep the number of indexes small and validate each one against query plans and actual usage statistics.