From a slow query to the right index

A list page that used to open instantly takes four seconds. Nothing obvious changed, the table just kept growing, and somewhere between fifty thousand rows and two million the planner changed its mind about how to answer the query. The temptation at that point is to add an index on the column in the WHERE clause and move on. That guess is right often enough to be dangerous, because when it is wrong you are left with a slow page and one more index to maintain.

Find the query before you fix anything

The query users complain about is not always the one costing the time. Start from the data the database already keeps. Most engines expose aggregated statement statistics, and sorting by total time rather than mean time is what surfaces the real problem:

SELECT calls,
       round(total_exec_time)      AS total_ms,
       round(mean_exec_time, 1)    AS mean_ms,
       left(query, 70)             AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

 calls | total_ms | mean_ms | query
  8421 |   312840 |    37.2 | SELECT id, total FROM orders WHERE cust
    12 |    48120 |  4010.0 | SELECT count(*) FROM events WHERE creat

The first row is the one to fix. A query at 37 milliseconds looks fine in isolation and is being run eight thousand times, which is where the page went. The second one is slower per call and barely matters.

The other half of the picture is in the application. Log the duration and the row count of each query with the request id, and a slow endpoint tells you immediately whether it ran one slow query or two hundred fast ones. That second case is not an index problem and no index will fix it.

Read the plan, do not guess

With a query in hand, ask the database what it intends to do and what it actually did:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4711 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=48210.44..48210.49 rows=20 width=28)
        (actual time=612.2..612.3 rows=20 loops=1)
  ->  Sort  (cost=48210.44..48287.19 rows=30700 width=28)
            (actual time=612.1..612.1 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..47394.00 rows=30700 width=28)
                  (actual time=0.3..598.4 rows=28934 loops=1)
              Filter: ((customer_id = 4711) AND (status = 'paid'))
              Rows Removed by Filter: 1971066
Planning Time: 0.2 ms
Execution Time: 612.6 ms

Three things in that output carry the whole diagnosis. The access path is a sequential scan, so the database is reading the entire table. Rows Removed by Filter is almost two million, which is the work being thrown away. And the sort happens after the scan, which means every matching row is materialised before twenty of them are kept.

Compare the estimated rows with the actual rows while you are there. When the estimate is out by an order of magnitude, the planner is working from bad statistics and the fix may be ANALYZE rather than an index.

Choose the column order

The query filters on two columns with equality and sorts on a third. That maps directly onto the index:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

The rule behind the order is that the planner can only use a leading prefix of an index. Equality columns come first, because each one narrows the scan to a contiguous block. The column used for sorting or for a range comes last, because once you are inside that block the rows are already in the right order and the sort disappears. Reverse the order and put created_at first, and the index becomes a date range scan that still has to filter every row it finds.

Selectivity decides the order among the equality columns, not against the rule above. customer_id has many distinct values and cuts the table to a handful of rows, status has maybe five. Putting the selective one first keeps the traversal short.

That is also why an index on status alone is usually worthless. If a quarter of the table is paid, reading the index and then fetching a quarter of the rows one at a time is more expensive than scanning the table. There is one shape where a low cardinality column earns its place, and that is a partial index, where the flag becomes a condition instead of a key:

CREATE INDEX CONCURRENTLY orders_pending_created_idx
  ON orders (created_at)
  WHERE status = 'pending';

The index now contains only the rows a worker is looking for. It is small, it stays in memory, and it does not grow with the rows that are already finished. This is the index that matters for a background queue draining a large import, where the interesting rows are always a tiny fraction of the table.

If the query returns only a few columns, you can go one step further and let the index answer it without touching the table at all:

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total);

That is a covering index. The payload columns ride along in the leaf pages, the plan turns into an index only scan and the heap fetches disappear. The cost is size: every included column makes the index bigger, and a bigger index is slower to keep in cache.

How to check it worked

Run the same EXPLAIN and read the same three lines:

Limit  (cost=0.43..8.91 rows=20 width=28) (actual time=0.05..0.19 rows=20 loops=1)
  ->  Index Scan using orders_customer_status_created_idx on orders
        (actual time=0.04..0.17 rows=20 loops=1)
        Index Cond: ((customer_id = 4711) AND (status = 'paid'))
Planning Time: 0.3 ms
Execution Time: 0.2 ms

The sort node is gone, the filter is gone, and the rows read equal the rows returned. Six hundred milliseconds became under one. Then let it run for a week and look at what the indexes are actually doing:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC
LIMIT 10;

Indexes at the top of that list with zero scans and a real size are costing you write throughput and disk for nothing. Dropping them is the other half of the job, and it is the half that never gets done. Disk fills quietly, the same way it does with logs nobody rotated.

What to watch out for

• Wrapping the column kills the index. WHERE lower(email) = $1 or WHERE created_at::date = $1 cannot use an index on the plain column. Either index the expression itself or rewrite the query as a range.
• A leading wildcard in a LIKE pattern cannot use a normal index either. Text search that needs %term% wants a different index type, not a column order tweak.
• Test the plan with a realistic parameter, and with an unusual one. A customer with three orders and a customer with three hundred thousand can get different plans from the same query, and the second is the one that pages someone at night.
• Build indexes without locking writes where the engine supports it, and expect the build to take longer and to fail if something conflicts. A failed concurrent build leaves an invalid index behind that you have to drop by hand.
• More indexes means slower writes and more to keep consistent. On a table that takes bulk inserts it can be cheaper to drop an index, load, and rebuild it, as long as the load itself is safe to run twice if the rebuild fails halfway.

The route is always the same: find the query with numbers rather than intuition, read the plan the database gives you, and let the shape of the query choose the index rather than the other way round. Most of the value comes from the first two steps, because a plan tells you in ten seconds what an afternoon of guessing will not. And when the plan says the database is already doing the least work it can, the problem is somewhere else, which is just as useful to know.