Things you didn't know about indexes
×
2026-04-15
Things you didn't know about indexes
Jon Charter
Let’s start with things you probably did know.
A database index is similar to the index you found at the back of your science textbooks in school. You want to find the pages that talk about Phosphorus? Head to the back and you’ll find an alphabetical list of topics alongside page numbers that reference them.
...<br>Oxygen .................. 42, 88, 103<br>Periodic table .......... 12–15<br>Phosphorus .............. 67, 91<br>Photosynthesis .......... 54, 72, 110<br>Potassium ............... 33, 78<br>...
Let’s imagine we have a database table of Pokémon, like so1:
id | name | type_1 | type_2 | generation | is_legendary | base_attack<br>-----+------------+----------+----------+------------+--------------+-------------<br>1 | Bulbasaur | Grass | Poison | 1 | false | 49<br>4 | Charmander | Fire | NULL | 1 | false | 52<br>...<br>25 | Pikachu | Electric | NULL | 1 | false | 55<br>...<br>150 | Mewtwo | Psychic | NULL | 1 | true | 110
Without an index, finding Pikachu means the database reads every row, one by one, checking the name column for each row. On four rows, that would be nearly instant. On ten million, it could be problematic. That kind of read is called a full table scan, and it’s about as fast as it sounds (not very2).
But, if we add an index on name we get something that conceptually looks a bit like our book index above:
name row<br>------------+-----<br>Bulbasaur → 1<br>Charmander → 4<br>Mewtwo → 150<br>Pikachu → 25
It’s sorted, and so now our database can binary-search for the name and find the given row, rather than scanning the entire table.
Postgres stores this as a B-tree under the hood, but the idea is the same as the textbook: sorted data you can search quickly.
So, let’s index all the things, right?
The cost of indexing
As tempting as it may be to index everything, now we’ve seen what they can do for our queries, there are trade-offs to consider. With indexes, always keep in mind:
Reads get faster, writes get slower.
With our shiny new index, every INSERT, UPDATE or DELETE now has to update the index too. When we add a new Pokémon, the database has to find the right spot in the sorted name index and slot it in. And we can have multiple indexes, so you can multiply that by each one.
Additionally, indexes are real data structures and we must store them. They live on disk and are pulled into memory. A table with eight indexes has nine things to keep warm in cache, not just one.
And let’s not forget the query planner. When you run a query, the planner’s job is to pick the cheapest way to answer it. The more indexes you have, the more options it weighs, so planning time grows and could even exceed execution time on fast lookups.
Why your index isn’t working
You’ve got as far as weighing the trade-offs and deciding an index is right for the job. Excellent. But it’s not working as you’d hoped. You’re not seeing an improvement, or worse, you’re seeing speed diminish.
Let’s cover some common gotchas.
Composite indexes care about order
Looking back at our Pokémon table, you may have decided a good index is one on type_1 and type_2. After all, “show me all the Pokémon that are Water and Flying types” is a perfectly reasonable query.
CREATE INDEX ON pokemon (type_1, type_2);
This index will definitely help with queries like these:
SELECT * FROM pokemon WHERE type_1 = 'Water';<br>SELECT * FROM pokemon WHERE type_1 = 'Water' AND type_2 = 'Flying';
But this one? Not so much:
SELECT * FROM pokemon WHERE type_2 = 'Flying';
Do you find that surprising?
When you create a composite index (type_1, type_2) you are asking the database to create a structure that looks something like this:
Bug → Flying → [Butterfree, Beedrill, ...]<br>→ Poison → [Venonat, Spinarak, ...]<br>→ Water → [Surskit, ...]<br>Electric → NULL → [Pikachu, Raichu, ...]<br>→ Flying → [Zapdos, ...]<br>→ Steel → [Magnemite, ...]<br>Fire → NULL → [Charmander, Vulpix, ...]<br>→ Flying → [Charizard, Moltres, ...]<br>→ Ground → [Numel, ...]<br>Grass → Poison → [Bulbasaur, Oddish, ...]<br>→ ...<br>Water → NULL → [Squirtle, Psyduck, ...]<br>→ Flying → [Wingull, Pelipper, ...]<br>→ Ground → [Wooper, ...]
It sorts first by type_1, and then by type_2 within each group. That makes the index great for queries on type_1, and even better for queries on type_1 AND type_2 together, but won’t be used the way you hope for queries on type_2 alone. Look at Flying: it’s scattered under Bug, Electric, Fire and Water with no single place for the database to jump to.
What we must ask ourselves is: What are the common queries going to be? If we will query on type_2 alone as frequently as type_1, then we should create a second index on type_2.
Functions defeat your index
Case-insensitive search is a feature we often add without thinking. Users don’t care whether they type “Pikachu”,...