Imagine a table with ten million users. You need one person, and all you have is an email address. Without a useful index, the database may have to inspect a lot of rows to find the match. That works for a tiny table. At a larger scale, it can mean a lot of unnecessary work.
I made a short animated explanation of this exact problem: watch the full video.
Think of the index at the back of a book
If you need to find “database normalization” in a thousand-page book, you probably won’t read every page. You’ll check the index, find a page number, and jump there.
A database index does a similar job. It keeps search values in an organized structure and helps the database locate a matching row. The index is a guide to the data; the full user record usually stays in the table. In PostgreSQL, an ordinary index scan finds matching row locations in the index, then fetches the table rows. The PostgreSQL index guide explains both the benefit and the overhead.
For example, the database might look up ghost@example.com, find its location, and fetch that user’s row. The email is fictional; the idea is the useful part.
How does the index find its own value?
Many database indexes use a B-tree. Picture a set of sorted signposts. Each page contains several keys and points toward a smaller range. Looking for 59? At a node containing 20, 40, 60, and 80, the search follows the route between 40 and 60. It rules out the other ranges, then narrows the search again.
The key idea is that the database can skip large parts of the search space. The exact work depends on the index, the query, and the data—not on a magic promise that every lookup takes the same number of steps.
An index has a cost
Indexes take storage. When indexed values change, the database also has work to do to keep the index consistent. So adding an index to every column is rarely a free win.
The query matters, too. If a condition matches most of a table, reading rows in order can be cheaper than looking up many scattered rows through an index. PostgreSQL’s planner compares possible plans and chooses what it estimates will cost less. You can inspect its choice with EXPLAIN; EXPLAIN ANALYZE runs the query and reports what happened. The PostgreSQL EXPLAIN guide shows how a plan can choose an index scan or a sequential scan.
For a multi-column index, column order matters. An index on (country, city) is organized first by country, then city. A query using the leading column can often narrow the search directly; a query using only the later column may need a different plan.
The question to ask before adding one
Instead of asking, “Should I index this column?” start with: “Which query am I trying to make cheaper?” Look at the filter, the number of matching rows, and the actual plan. Then test whether the index helps enough to justify its storage and maintenance.
That’s the practical picture: an index is a map to the row, a B-tree is one way to organize that map, and the query planner decides when taking the map is worth it.
Examples use fictional data. The animated video walks through the book analogy, the B-tree search, selectivity, composite indexes, and query plans: watch it here.
Top comments (0)