Don't read the whole book
Finding one customer among 10 million by reading every row is slow. An index is like a book's index: a sorted structure that jumps straight to the right rows. Primary keys and UNIQUE columns get one automatically.
CREATE INDEX and EXPLAIN
CREATE TABLE visits (id serial PRIMARY KEY, phone text NOT NULL, visited_on date NOT NULL); INSERT INTO visits (phone, visited_on) SELECT '98' || lpad((i % 5000)::text, 8, '0'), date '2026-01-01' + (i % 270) FROM generate_series(1, 50000) AS i; ANALYZE visits; EXPLAIN SELECT * FROM visits WHERE phone = '9800001234'; CREATE INDEX visits_phone_idx ON visits (phone); EXPLAIN SELECT * FROM visits WHERE phone = '9800001234';
- Index columns used in WHERE, JOIN … ON (foreign keys!) and ORDER BY on big tables.
- Indexes cost space and slow down writes a little — don't index everything.
- EXPLAIN ANALYZE runs the query and shows real timings.
- Composite indexes (customer_id, ordered_at) help queries that filter by the first column (and sort by the second).