Indexes, EXPLAIN and the slow query

Nearly every slow query I have investigated fell into one of three categories: the index is missing, the index exists but cannot be used, or the index is used and the database still goes to the table for the rest.

The three have different diagnoses and different fixes, and all three show up in EXPLAIN.

What an index is, on the inside#

A relational database index is a B+tree. Each node occupies a disk page and holds hundreds of keys with pointers. With a branching factor of around three hundred, a three level tree addresses twenty seven million records.

Three disk accesses in the worst case, and the first two levels are almost always in memory, so in practice it is one.

The "plus" variant means the data lives only in the leaves, and the leaves are linked to each other in a list. That keeps the internal nodes lean and turns a range scan into walking a linked list.

That is why a database index is a tree and not a hash: a hash solves equality in constant time and solves neither ranges nor ordering. And ranges and ordering are most of your queries.

The cost of an index#

An index speeds up reads and slows down writes, because every insert, update and delete has to update every relevant tree. An index also takes disk and memory.

The consequence: an unused index is pure cost. And almost every production database has several.

On Postgres, the query is straightforward:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Indexes with zero scans since the last statistics reset are candidates for removal. Check first that they are not unique constraints: those have another job.

Column order decides everything#

A composite index follows the phone book principle: sorted by surname and then by first name.

You find all the "Silvas" quickly. You find "Silva, John" quickly. But if all you know is the first name "John", the book does not help: you would have to read all of it.

An index on (a, b) serves:

• a filter on a
• a filter on a and b

It does not serve a filter on b alone.

The practical rule for building a composite index: put the equality columns first, then the range column, and last the ones used only for ordering.

What stops an index from being used#

There are patterns that void the index even when it exists. The most frequent ones:

A function on the column. WHERE upper(email) = 'X' does not use the index on email. Fix: build an index on the expression, or normalise on write.

LIKE with a leading wildcard. WHERE name LIKE '%silva' cannot use a B-tree, because the tree is sorted by the start of the string. That is what trigram indexes or full text search are for.

Comparing different types. A varchar column compared against a number forces a cast and kills the index.

Low selectivity. If the condition returns half the table, scanning the table is faster than jumping from index to table thousands of times. The database knows this and picks the Seq Scan on purpose. In that case, the Seq Scan is not the problem.

Index only scan: the underrated optimisation#

When the database uses an index, it usually finds the pointer and then goes to the table for the missing columns. That is two accesses.

If the index contains every column the query needs, the database answers without touching the table. That is an index only scan, and it is one access instead of two.

On Postgres you get it with INCLUDE:

CREATE INDEX idx_orders_customer
ON orders (customer_id, created_at)
INCLUDE (status, amount);

The INCLUDE columns do not take part in the ordering: they are simply stored in the leaf. It is the difference between paying for one access and paying for two, on every execution.

In my experience it is the index change with the best effort to payoff ratio there is, and the least applied.

How to read an EXPLAIN ANALYZE#

You want to look at three things, in this order.

First: the access type. A Seq Scan on a large table with a selective filter is a sign of a missing index. Index Scan is good. Index Only Scan is great. Bitmap Heap Scan shows up when many rows match and the database decides to sort the disk accesses, usually reasonable.

Second: estimate versus reality. The plan shows how many rows the database expected and how many came back. If it expected ten and got a hundred thousand, the statistics are stale, and the chosen plan was bad for lack of information. Run ANALYZE on the table.

Third: where the time is. Every node in the plan shows accumulated time. Find the node taking the largest slice and work on that one. Do not optimise the rest.

And add BUFFERS to your EXPLAIN: it shows how many pages were read from cache and how many from disk. A query reading heavily from disk is a candidate either to fit better in memory or to need a leaner index.

The full routine#

When a query is slow:

1. Run EXPLAIN (ANALYZE, BUFFERS).
2. If it is a Seq Scan with a selective filter → create the index, respecting column order.
3. If it is an Index Scan with many table accesses → consider INCLUDE to turn it into an Index Only Scan.
4. If the estimate is badly off → run ANALYZE and consider raising the statistics target on that column.
5. If none of that fixes it → the problem may not be the query. It could be locking, saturated I/O, or a bad plan from a parameter. At that point you go up a level and move to observability.