Why does the same query take 5 milliseconds on one table and 5 minutes on another? The difference is almost always an index. An index is a separate data structure that the database maintains alongside your table, designed to speed up lookups at the cost of extra storage and slower writes. Think of it like the index at the back of a textbook: instead of flipping through every page to find "B-tree," you look up the term in the index, get the page number, and go directly there.

Full Table Scan vs Index Scan

Without an index, the database has no shortcut. When you run SELECT * FROM users WHERE email = '[email protected]', the database must read every single row in the table and check whether the email column matches. This is called a full table scan (or sequential scan in PostgreSQL). For a table with 10 million rows, that means reading 10 million rows to find one match.

An index on the email column changes this completely. The database looks up "[email protected]" in the index, finds the exact location of the matching row on disk, and reads only that row. Instead of scanning 10 million rows, it reads a handful of index pages and one data page. The difference between O(N) and O(log N) is the difference between a query that scales linearly with table size and one that barely notices growth.

1-- 10 million rows, no index on email
2EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
3
4 Seq Scan on users  (cost=0.00..208333.00 rows=1 width=84)
5                    (actual time=412.885..1980.214 rows=1 loops=1)
6   Filter: (email = '[email protected]'::text)
7   Rows Removed by Filter: 9999999
8 Planning Time: 0.094 ms
9 Execution Time: 1980.245 ms
10
11CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
12
13-- same query, same rows, one new data structure
14EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
15
16 Index Scan using idx_users_email on users
17                    (cost=0.43..8.45 rows=1 width=84)
18                    (actual time=0.038..0.039 rows=1 loops=1)
19   Index Cond: (email = '[email protected]'::text)
20 Planning Time: 0.211 ms
21 Execution Time: 0.061 ms

The line that accounts for the two seconds is Rows Removed by Filter: 9999999. That is work the sequential scan performed and then discarded. The index did not make the database faster at reading a row; it made almost all of those reads unnecessary. The planner's own estimate fell from 208,333 to 8.45, and that number matters beyond this query, because it is what the planner compares against every other strategy once the query grows a join.

When Indexes Help

Indexes shine in specific scenarios:

• Equality lookups: WHERE user_id = 42 goes from scanning every row to a direct lookup
• Range queries: WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31' benefits from sorted index structures that can jump to the start of the range and scan forward
• Sorting: ORDER BY created_at DESC LIMIT 10 can read the last 10 entries from a sorted index instead of sorting the entire table
• Join conditions: JOIN orders ON users.id = orders.user_id uses the index to find matching rows without nested loops over both tables

When Indexes Hurt

Indexes are not free, and blindly adding them causes real problems:

• Write overhead: every INSERT, UPDATE, or DELETE must also update every index on that table. A table with 8 indexes means each write operation triggers 8 additional index maintenance operations. For write-heavy workloads (logging, event tracking, IoT telemetry), excessive indexes can cut write throughput in half
• Storage cost: an index is a separate data structure stored on disk. A table with 10 million rows might be 2 GB, but adding five indexes could add another 3-4 GB of storage. On managed database services billed per GB, this cost adds up fast
• Low selectivity: an index on a boolean column (active = true/false) is usually pointless. If 90% of rows have active = true, the database will scan 9 million rows through the index, which is slower than just reading the table directly because of the extra indirection
• Small tables: a table with 500 rows fits in a few data pages. The overhead of traversing an index tree and then jumping to the data page is often slower than just scanning all 500 rows sequentially

How the Database Uses an Index

The mechanics of an index lookup involve two phases. First, the database traverses the index data structure to find the matching entries. For a B-tree index, this means walking from the root node down to the leaf node, comparing keys at each level to determine which branch to follow. Second, the database uses the row pointers stored in the index to fetch the actual data from the table's data pages on disk. This second step is called a "heap fetch" or "table access by index rowid."

The heap fetch is often the expensive part. If the matching rows are scattered across many different data pages, each fetch requires a separate random disk I/O. This is why high-selectivity indexes (those that match a small percentage of rows) are so much more effective than low-selectivity ones: fewer matching rows means fewer random reads.

The decision to add an index is always a trade-off between read speed and write cost. The right answer depends on your workload: a read-heavy analytics dashboard benefits enormously from indexes, while a high-throughput event ingestion pipeline might perform better with fewer indexes and periodic batch reads.