Indexes — Why the One You Created Is Not Used

In a nutshell

An index is a separate data structure sorted by value. The optimizer not using it is usually the right judgment, and the few cases where it is not right have known causes.

Why this was needed

To find the rows that satisfy a condition, in principle you have to read the whole table. Reading 1 million rows to find a single row among them is a waste. If there is an auxiliary structure that keeps values and locations sorted, like the index of a book, you can find the location with a few comparisons. This is the B-tree index.

How it works

A B-tree is a balanced tree sorted by value. Because it is sorted, it can handle not only equality lookups but also range lookups and sort requirements. Conversely, the moment you wrap a column in something, that sorting becomes useless.

-- 안 탄다: 컬럼에 함수가 걸렸다
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- 탄다: 범위 조건으로 바꿔 컬럼을 그대로 둔다
SELECT * FROM orders
WHERE created_at >= '2026-07-26' AND created_at < '2026-07-27';

In the same category are implicit casts, where a type mismatch causes the column side to be converted, and LIKE '%kim%', whose starting point cannot be fixed. If a cast notation is attached to the column name in the execution plan, or a function call appears in the Filter line, this is the problem.

A composite index has the leading column rule. An index (a, b, c) is a structure sorted by a, then by b among equal a, and then by c among equal b. It is like a phone book sorted by surname and, within the same surname, by given name, so you cannot find anyone knowing only the given name without the surname. The principle for column order is to put equality condition columns first and range condition columns after. This is because the columns after a column with a range condition cannot be used for the search and work only as filters.

Here we must point out the most important premise. It is usually right for the optimizer not to use an index. An index scan has to read the index pages and then visit heap pages at random for each matched row. If the rows to return are a considerable fraction of the whole, it ends up reading the entire table in random order, which is slower than a sequential scan. In fact, if you force it with enable_seqscan = off, it is common for a 231ms query to become four times slower at 1094ms.

There are three variables that set the boundary. They are the setting for the relative cost of random access (random_page_cost), the degree to which index order and heap order agree (correlation), and whether all the returned columns are contained in the index. A covering index is one where columns are deliberately added to satisfy the last one, and then an Index Only Scan, which does not visit the heap, becomes possible.

What it looks like in the field

A partial index is a tool that solves several problems at once. For a workload that queries only the pending status, which is only a few percent of all orders, an index with WHERE status = 'pending' attached shrinks from 42MB to 312KB. It does not only reduce the size: INSERTs and UPDATEs of rows that do not match the condition do not touch this index, so the write cost also drops.

And you must not forget the cost on the other side of an index. Every INSERT and DELETE updates all the indexes of that table. As indexes increase, plan candidates also increase, so even the planning time grows. This is why you must periodically clear out indexes that are not used.

How to read an execution plan

After creating an index, you must not move on with "it must be faster now". Whether it actually uses that index, and how long it took, is told by the execution plan. It looks overwhelming at first, but there are only a few things you really need to read.

The difference between estimate and actual. The plan shows both the number of rows the optimizer expected and the number of rows that actually came out. If the two are far apart, that is the starting point of the problem. Because the optimizer judges by statistics, if the statistics are stale or the condition has a form that statistics cannot express, it chooses a wrong plan. Finding the nodes that are off by more than a factor of 10 first is the first move in reading a plan.

Where did the time go. A plan is a tree, and each node's time includes the time of its children. So you must look not for the slowest node overall but for the node with a large time of its own. And for a node that is executed repeatedly, you must multiply the time of one execution by the number of executions to get the actual cost.

Where were rows filtered out. If many rows were found through the index and then discarded because they did not meet a condition, there is room to try putting that condition into the index. Conversely, if almost no rows were discarded, the index already fits well.

Was it read from disk. Reading what is in the cache and reading from disk differ greatly in speed. A query that is slow the first time and fast from the second time is easily mistaken as "thanks to the index", so when comparing you must measure several times under the same conditions.

One last thing. A plan is an answer about the data and statistics of that moment. It is normal for a sequential scan to win on a small table in a development environment and for an index to win on a large table in production, so when you look at plans you should look at data of a size similar to production.

What you will do in the next lab

You will create several kinds of indexes on real tables and save in files how the execution plan changes. In particular, you will measure for yourself the cases that become slower when an index is forced.