A dashboard query that took 200ms now takes 40 seconds. The table has grown to 200 million rows. Walk me through diagnosis and fix, including what you would not do.

What the interviewer is testing

Whether you diagnose with evidence before changing anything, and whether you know the costs of the fixes you propose.

Diagnosis, in order

1. Get the query plan. EXPLAIN ANALYZE (or the equivalent). This is not optional and it is where most of the answer lives. Look for: a sequential scan on a large table, an index scan returning far more rows than the query needs, a nested loop over a large outer set, or a sort or hash spilling to disk.

2. Compare estimated against actual row counts. A large divergence means the planner's statistics are stale, and the fix may be ANALYZE rather than a new index. This is the cheapest possible fix and the most commonly missed.

3. Establish what changed. 200 ms to 40 s is rarely gradual. Candidates: the table crossed the size where the planner switched from index scan to sequential scan, an index was dropped or never created on a new column, data distribution skewed so a previously selective predicate no longer is, or a code change added a column to the WHERE or ORDER BY.

4. Check whether it is the query at all. Lock contention, connection pool saturation and I/O starvation from a concurrent job all present as a slow query while the query is fine.

The fixes, in order of preference

Statistics refresh if estimates are wrong. Minutes, no schema change.

A covering index matching the query shape. Column order matters: equality predicates first, then range predicates, then sort columns — an index is usable only for a left-to-right prefix. Including the selected columns lets the query be answered from the index without touching the table.

A partial index if the query always filters to a subset (WHERE status = 'active'). Far smaller, far cheaper to maintain.

Rewrite the query if the plan shows something structurally bad — a function on an indexed column defeats the index, OR across columns often prevents index use, SELECT * forces table access that a covering index would have avoided.

Partition the table by date if the dashboard only ever looks at recent data, so most partitions are never touched.

Pre-aggregate into a materialized view if the query is an aggregate over millions of rows. Dashboards are the canonical case: computing once per minute and reading a thousand times is orders of magnitude cheaper than computing per view.

What I would not do

Add an index without reading the plan. Indexes are not free: every one is maintained on every write to its columns, so a table with a dozen indexes has a write path a dozen times more expensive. Speculative indexes accumulate and nobody dares remove them.

Index a low-selectivity column. An index on a boolean or a three-value status is usually ignored by the planner.

Reach straight for a cache. Caching a slow query hides it, and the first cold cache under load brings the problem back at the worst moment.

Reach for a different database. "Postgres is too slow at 200 million rows" is almost never true; a missing index is the overwhelmingly more likely explanation, and migrating is a very expensive way to add one.

What a strong answer adds

Noting that index creation on a 200-million-row table locks or loads the system unless done concurrently (CREATE INDEX CONCURRENTLY), and that this is exactly the kind of change that should be tested against a production-sized dataset first — because the fix itself can be the outage.