The Case of the Missing Index: How to Debug Slow Queries

The Endpoint That Was Only Slow for Some Customers

The "pending orders" page loads in 40 milliseconds for nearly everyone. For a handful of large wholesale customers it takes four seconds, and sometimes it times out. The query looks harmless:

SELECT * FROM orders
WHERE customer_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

There's an index on customer_id. Someone ran EXPLAIN in staging and it used the index. Case closed, except the page is still slow.

Slow query investigations go wrong in predictable ways: the wrong query gets blamed, it gets tested with unrepresentative parameters, the plan gets skimmed instead of read, and an index gets added that's almost right. The fix is to treat it like an investigation, with steps you follow every time.

The Investigation, Start to Finish

Step 1: Find the Query That Matters

Don't start from the query you suspect. Start from what the database says it's spending time on.

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

Step 2: Reproduce It With the Right Parameters

The staging EXPLAIN in the opening story was correct. It just tested a customer with 300 orders. The slow customers had 200,000.

Most real data is skewed: a few tenants, users, or products account for a large share of rows. The same query can get a different plan, and very different performance, depending on which value you pass. When you reproduce, use the actual parameter values from the slow log, run it against data with production's size and distribution, and run it more than once, so you can see the difference between a cold cache (data read from disk) and a warm one.

Step 3: Read What the Plan Is Telling You

EXPLAIN shows the plan the database intends to use. EXPLAIN (ANALYZE, BUFFERS) runs the query and shows what actually happened: real row counts, real timings, and how many pages it touched. Always use the second form when you're debugging.

You don't need to understand every node. Look for these five signals, in roughly this order:

The single most useful number is the ratio of rows read to rows returned. If a query returns 20 rows and reads 200,000 to find them, there's a better access path. If it reads 25 to return 20, indexes won't help much more, and the slowness is somewhere else.

Step 4: Design the Index From the Query

Once you know where rows are wasted, build the index from the query's shape:

1. Equality columns first. customer_id = $1 AND status = 'pending' narrows to exactly the rows that match both.
2. Then the range or sort column. ORDER BY created_at DESC after those equality columns means the matching rows come out of the index already in order, and LIMIT 20 can stop after 20.
3. Consider a partial index if one condition is fixed. If the query always asks for status = 'pending' and only 5% of orders are pending, CREATE INDEX ... (customer_id, created_at) WHERE status = 'pending' indexes only those rows: a fraction of the size, cheaper to maintain.
4. Consider covering the query with INCLUDE (...) columns if reading the table rows is the remaining cost, so the database can answer from the index alone with an index-only scan.

Try it on the query from the opening story. Start with no index, then work up, and switch between a typical customer and a large one.

SELECT * FROM orders
WHERE customer_id = 4417 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;   -- orders: 10M rows, 5% pending

Things to notice:

• (customer_id) with a typical customer. Fast, which is why staging looked fine. Switch to the 200k-order customer: the index finds 200,000 rows, then a filter throws away 95% of them, then a sort orders the rest to return 20.
• (customer_id, status). No filter waste, but the large customer still has 10,000 pending orders to sort.
• (customer_id, status, created_at). The Sort node disappears. The index returns rows in order, so the query reads 20 rows for 20 results, for any customer.
• The partial index. Same plan, a fraction of the size, because it only contains pending orders.
• (status) alone. A low-selectivity index: "pending" matches half a million rows, so it's still hundreds of milliseconds and only a few times better than a full scan.

On a busy production table, build the index with CREATE INDEX CONCURRENTLY (PostgreSQL) so writes aren't blocked during the build. It takes longer, and if it fails it leaves an invalid index you need to drop and retry. MySQL's InnoDB builds most secondary indexes online by default.

Step 5: Verify, Then Clean Up

Run the same EXPLAIN (ANALYZE, BUFFERS) with the same slow parameters and confirm the plan changed the way you expected. Then confirm production agrees: mean_exec_time in pg_stat_statements should drop (you can reset its counters with pg_stat_statements_reset() to see fresh numbers), and pg_stat_user_indexes.idx_scan should start climbing for the new index.

Indexes aren't free. Every insert, and every update that touches an indexed column, has to update each index, and each index takes disk and memory. If your new index makes an older one redundant (a (customer_id) index is covered by (customer_id, status, created_at) for most queries), check idx_scan on the old one after a while and drop it if nothing uses it.

When It's Not a Missing Index

Sometimes the plan already looks reasonable and the query is still slow. Before adding another index, rule these out:

• The planner has bad statistics. If estimated and actual rows differ by 10× or more, run ANALYZE on the table and check again. If the index exists but isn't chosen, Why Your Index Isn't Being Used covers the common causes: type mismatches, functions on columns, low selectivity, and wrong column order.
• It's waiting, not working. If EXPLAIN ANALYZE is fast but the query is slow in production, it may be blocked on a lock. Check wait_event in pg_stat_activity while it runs.
• It's the pool, not the database. Requests waiting for a free connection look like slow queries from the application's side. (Connection Pooling Gone Wrong covers this.)
• It's many fast queries. An endpoint making 300 queries of 2 ms each is slow without any single slow query. That's the N+1 query problem.
• It reads too much by design. Deep OFFSET pagination reads and discards every skipped row no matter how it's indexed. (Pagination at Scale covers keyset pagination.)

Key Takeaways

• Find the expensive query from data, not suspicion. Rank by total time and by mean time, and capture slow executions with their parameters.
• Reproduce with the slow parameters. Skewed data means the same query can be fast for most values and slow for a few.
• Use EXPLAIN (ANALYZE, BUFFERS), and look first at the ratio of rows read to rows returned.
• "Rows Removed by Filter" and a Sort above a Limit are the two signs of an index that's close but incomplete.
• Build the index from the query: equality columns first, then the sort or range column. Partial indexes are a cheap win when one condition is fixed.
• Verify in production and clean up. Watch mean_exec_time and idx_scan, and drop indexes the new one makes redundant.
• Not every slow query is an index problem. Rule out bad statistics, lock waits, pool waits, N+1 patterns, and deep offsets.

References

• PostgreSQL docs: pg_stat_statements
• PostgreSQL docs: Using EXPLAIN
• PostgreSQL docs: auto_explain
• PostgreSQL docs: Partial Indexes and Building Indexes Concurrently
• MySQL docs: The Slow Query Log and EXPLAIN ANALYZE