Reading every row
Without an index, a query looking for one email address has exactly one option. Read every row in the table and check.
Postgres, a relational database, calls that a sequential scan, and its cost grows in step with the table. At 10,000 rows nobody notices. At 50 million it is seconds of disk reading per query, and your database spends its life re-reading the same table to answer the same question.
Picture an index as a second copy of a few chosen columns, kept in sorted order, where each entry points back at its row. Sorted data can be searched by halving, so instead of 50 million comparisons you do a few dozen.
The phone book comparison survives because it is exact. Sorted by surname, and useless the moment you want to search by street.
The lesson is literal. An index on email finds emails and does absolutely nothing for a query about dates.
EXPLAIN, and when an index does not help
EXPLAIN is worth making muscle memory, because it is how you see any of this. Run it on your query and the database tells you whether it scanned the table or used an index, with real timings and row counts.
Slow query? Explain first, guess second. Most incidents that begin as the database is slow end five minutes later with one missing index.
The planner will sometimes ignore a perfectly good index, and expect it to be right. If your filter matches 40 percent of the table, hopping from the index out to millions of scattered rows costs more than reading the table straight through.
Indexes only pay you back on selective filters, the ones that pick out a small slice. An index on a true-or-false column that splits the table down the middle is dead weight: all of the write cost and none of the read benefit.
Worked example
Tom gets paged because login is timing out at his edtech startup. The users table has grown to 8 million rows, and EXPLAIN ANALYZE on the login query shows Seq Scan on users, 2,300 ms, filtering 8 million rows to find one. Nobody ever indexed email; the table held 5,000 rows when the query was written and nothing was slow. He runs CREATE INDEX CONCURRENTLY idx_users_email ON users (email), which takes four minutes without blocking traffic. The same EXPLAIN now reads Index Scan, 0.4 ms. Login latency at p99, the number the slowest one request in a hundred comes in under, drops from 3.1 seconds to 90 ms and the pager goes quiet. On Monday he pulls the ten slowest queries from pg_stat_statements and finds three more missing indexes, each one the same story at a different table size.