Your query got slow and nothing about it changed

You run a query and it comes back in eighty milliseconds. You run the same query again with one word changed, and this time it takes nine seconds.

It is the same table. The same index is there, on the right column. Nothing about the query changed.

The database decided to answer it a completely different way, and it did not tell you. This is why that happens, and why the index you added is not the switch you think it is.

The short version

• Without an index the database reads every row in order. That is called a scan, and it is not stupid. Reading in order is what a disk does best.
• An index is not a setting. It is a second copy of your data, sorted, kept in a tree that is four or five layers deep even over millions of rows.
• Using it is two jobs, not one: find the matching values, then go and fetch each matching row. Once enough rows match, the fetching costs more than reading everything, so the database stops using the index. That is usually it being right.
• Which means a rare word is fast and a common word is slow, with the same query and the same index. And the words people type are the common ones.
• The copy has to be kept correct on every write, forever, whether anyone reads it or not.

What happens with no index at all

You ask the database for one row. It does the only thing it can: reads the first row and checks it, then the second, then the third, all the way down.

Here is the part that usually gets left out. A scan is not stupid. Reading in order is what a disk does best. There is no jumping: it asks for a block of rows, gets a block of rows, and keeps going.

So the scan is not the enemy. It is the honest starting point. It is the price of telling the database nothing in advance.

An index is a second copy of your data

An index is how you tell it in advance, and it is not a setting you switch on.

Take one column. Copy every value out of the table. Sort them. Next to each value, write down where the real row lives.

So why is that fast? Not because sorted lists are quick to search. If it really were one long list of ten million values you would look in the middle, keep one side, and repeat, and every one of those steps lands somewhere else that the database has to go and read. Sorting reduced the jumping. It did not remove it.

So it is not a list. It is a tree. The bottom row is your values in order. Above them sits a layer of signposts, each saying everything under this one is between these two values, then another layer above that, until the top is a single node.

The tree is only part of it. The bottom layer is also a chain, each value pointing to the next one and the one before.

The mistake almost everyone makes

An index can cover more than one column. Say you index category and colour, in that order. Sorted like that, all the shoes sit together, and inside the shoes the colours are in order. So the index can find shoes, and it can find red shoes.

And nothing warns you. The index has colour in its name. It looks like it covers colour. It does not.

Why it ignores the index you built

There is a part of the database called the planner. Its job is to decide how to answer your question, and it does not know your data. It estimates. Before it runs anything, it prices both options: the scan, and the index.

The index is two jobs, not one.

So everything depends on how many rows match. Ten rows is ten jumps and that is cheap. A million rows is a million jumps, and at that point reading every row in order is cheaper.

Now watch what that does to the same query.

That estimate comes from a summary of your data that the database keeps and refreshes in the background. If it is out of date, every decision after it is made using last month’s shape of your data. It does not warn you about that either.

There is a slower version of this. Nobody changes the query, the table just grows. A word that used to match a thousand rows now matches most of the table, the estimate crosses the line, the plan flips, and a query that worked for two years is suddenly slow. Nothing about it changed. The data did.

Joins, where the guessing gets worse

Real queries use several tables. When a query uses two tables together the database matches the rows in one against the rows in the other. That is a join, and the planner has to guess how many rows the first join gives back. It uses that guess for the next one, and that one for the next.

Postgres also has a limit. Past eight tables it stops looking for a better order to combine them. Past twelve it stops searching for the best order at all, and instead makes plans at random, keeps the better ones and mixes them. That is a genetic algorithm. So your database has a number where guessing becomes cheaper than thinking, and it is twelve.

No index fixes this. An index finds rows. This is about which tables to combine first, and that is a different problem.

The half nobody asks about

Everything so far has been about whether the database uses your index. There is another question.

When you added that index you did not make the query faster. You made a second copy of your data, and something has to keep that copy correct on every write.

Change one column and every index that mentions that column has to be rewritten too. Delete the row and all of them have to forget it.

I used to think an index could only help. Worst case, I thought, it just sits there and nobody uses it. That is wrong. An index nobody reads still costs you on every single write, forever, and nothing ever tells you.

Building one is not free either. By default, creating an index blocks all writes to that table until it finishes.

So this is what I think people get wrong. It is not that they add an index too early, because adding one is often the right answer. It is that they think it is free.

The real one

I want to be honest about this first: I did not watch it happen, I arrived after.

An online store with an old system underneath it and a search page that was already slow when I got there. The person who built it was not careless. It worked when they built it. The data grew for years and nobody was watching, and by the time it reached me it was just the slow search page.

The first thing I did was look for missing indexes. I added them. The page time barely moved. So I blamed the application code, too many trips to the database and loading more than it needed. That was not it either.

What was actually happening is the thing you already know. Some searches were fine. The popular words, the ones people actually typed, were the slow ones. And the data was spread across a lot of tables, so one search had to reach into a lot of them. I do not remember how many, so I am not going to tell you it was past twelve.

And I do not have before and after numbers for you. Nobody measured it properly, and I am not going to invent a figure to make this sound better. Every article about this ends with two seconds becoming three milliseconds. Mine ends with a page that stopped being the thing people complained about.

Three things I left out on purpose: there are other kinds of index, not just the tree. There is putting a cache in front of all this, which is a real fix. And there is what happens when two people write at the same time.

So, nothing about it changed

The database is guessing how much work each option will be. That guess comes from the shape of your data. Your data changes shape, and the plan follows it.

And an index is not a switch you turn on. It is a second copy of your data, kept correct on every write, and read only when the database thinks it is worth it.

If this is the first one you have read, there are three more that go with it: containers, load balancers and what a database is actually for. Together they are how something you wrote on your laptop ends up serving strangers.