Indexes: from scan to seek

Key points

• An index is a sorted copy of a few columns plus a pointer to the row. It turns reading a million rows into finding twenty.
• A composite index works left to right. (customer_id, created_at) serves queries on customer_id, but not on created_at alone.
• Wrapping a column in a function kills the index. WHERE lower(email) = ? needs an index on lower(email).
• Low-selectivity columns are poor indexes. A boolean index on a 50/50 split is slower than the scan it replaces.
• Every index is extra work on every insert, update, and delete. Unused indexes are pure cost - drop them.
• Never guess. EXPLAIN ANALYZE tells you what the planner really did and how long it really took.

Example

-- Sorted on customer, newest first: one seek, twenty rows read.
CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

-- Uses it: the leading column is in the predicate, and the sort is free.
SELECT id, total FROM orders
 WHERE customer_id = 42
 ORDER BY created_at DESC
 LIMIT 20;

-- Cannot use it: no leading column, so this is a full scan.
SELECT id FROM orders WHERE created_at > now() - interval '7 days';

-- Postgres: carry extra columns so the heap is never touched (index-only scan).
CREATE INDEX idx_orders_lookup ON orders (customer_id) INCLUDE (total, status);

-- Enforce uniqueness in the database, not in application code.
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42;

Index the columns you filter, join, and sort on - then prove it with EXPLAIN ANALYZE.