Indexing and query plans in practice

Making one slow query fast before adding hardware.

Before you add replicas, check whether the database is just working too hard on each query. A classic story: the order-history page for the delivery app takes 3 seconds. The table holds 20 million orders, and the query asks for one customer's last 20 orders. Without help, the database reads every row to find the matching ones. A query plan shows what it chose to do, and an index lets it jump straight to the rows it needs.

-- Slow: customer_id has no index
EXPLAIN SELECT * FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

-- Plan (simplified):
--   Seq Scan on orders   rows=20,000,000   cost: high
--   Filter: customer_id = 4821
--   Sort by created_at

A sequential scan reads all 20 million rows, filters them down to perhaps 200 for this customer, sorts those and keeps 20. Most of the work is wasted. The fix is an index on (customer_id, created_at). An index is a sorted structure, usually a B-tree, that maps column values to row locations. Looking up a value takes a handful of steps: with fan-out of a few hundred per page, 20 million entries sit about 3 or 4 levels deep.

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

-- Plan after:
--   Index Scan using orders_customer_time
--   Index Cond: customer_id = 4821
--   rows=20   (already in the right order, no sort)