Spotting and Fixing Slow Queries

This is the capstone interview prompt: the interviewer hands you a slow query and an EXPLAIN ANALYZE plan and asks you to diagnose it. They are testing a method, not memorized tricks.

A strong answer follows a checklist out loud: measure, read the plan, find the dominant cost, form a hypothesis, propose a fix, and verify. This lesson builds that checklist step by step.

Stay systematic and narrate your reasoning, that is what earns the senior rating.

Never guess from the SQL alone. Get the real plan with EXPLAIN (ANALYZE, BUFFERS).

ANALYZE gives actual times and row counts; BUFFERS shows whether you are hitting cache or reading from disk. Together they tell you if the query is CPU-bound, I/O-bound, or just doing too much work.

Run it a couple of times; the first run may pay a cold-cache penalty that distorts timing.

Do not read top-to-bottom hunting randomly. Find the node where the most time is actually spent.

Compute each node's self time: its total actual time minus the time of its children, multiplied by loops. The node with the biggest share is your target; everything else is noise.

In interviews say: 80 percent of the runtime is in this one Seq Scan, so that is where I focus. Optimizing anything else would be wasted effort.

At the dominant node, compare estimated rows to actual rows. A large gap means the planner is flying blind and likely chose a bad plan (wrong join algorithm, wrong access method).

The example shows a 1000x underestimate. Before redesigning anything, refresh statistics, this single command often fixes the plan for free.

ANALYZE recomputes column statistics; VACUUM ANALYZE also cleans dead tuples and updates the visibility map.

The most frequent fixable bug: a function or cast wraps the column in WHERE, so the index cannot be used and the engine seq-scans.

The example forces a full scan because DATE() is applied to every row. Rewrite it as a bare-column range predicate (sargable form), and the index on created_at kicks in.

Same idea for WHERE lower(email)=...: either store normalized data, query the bare column, or build an expression index.

If the dominant node is a Seq Scan with a highly selective filter, or a Nested Loop with huge loops on an unindexed inner key, the fix is usually an index.

Add an index on the filtered or joined column. The example creates one on customer_id so the join can switch from seq scans to index scans, and the planner may pick a far cheaper plan.

Verify by re-running EXPLAIN ANALYZE, do not assume the index helped.

SELECT * drags every column off disk and over the wire, and it prevents index-only scans because the index rarely covers all columns.

Select only the columns you need. This shrinks row width, lowers I/O, and can enable a covering index-only scan.

An interviewer who plants SELECT * wants you to notice it. Trimming the column list is often a quick, real win on wide tables.

If a Sort or Hash node reports disk usage (Sort Method: external merge Disk: 25000kB or Batches: > 1), the operation overflowed work_mem and spilled.

Options: raise work_mem for the session, reduce how many rows reach the sort/hash (filter earlier), or add an index that supplies sorted order so no sort is needed at all.

This is a precise, senior-level diagnosis interviewers reward.

Watch for Rows Removed by Filter: 9500000. The query read ten million rows and threw away almost all of them, classic wasted work.

Fixes: add an index so the filter is applied during the access (not after), make the predicate more selective, or push filtering earlier in the query so fewer rows flow up the tree.

The principle: do the least work, filter as early and as cheaply as possible.

Recite this in the interview and you cannot lose your way:

• Measure with EXPLAIN (ANALYZE, BUFFERS).
• Locate the node consuming the most time.
• Compare estimated vs actual rows, fix stale stats first.
• Check sargability, remove functions from filtered columns.
• Index selective filters and join keys.
• Trim columns, avoid SELECT *.
• Watch disk spills and over-fetching.
• Verify by re-running the plan.

Walk a full example aloud. Plan shows a Seq Scan on a 50M-row orders table, filter customer_id = 42, Rows Removed by Filter near 50M, estimate roughly matching actual.

Diagnosis: selective filter, no index, dominant cost is the scan. Fix: CREATE INDEX ON orders(customer_id). Re-run: plan flips to an Index Scan, time drops from seconds to under a millisecond.

That measure-diagnose-fix-verify loop is the answer template for any slow-query prompt.

A query filters with WHERE YEAR(order_date) = 2026 and the plan shows a full Seq Scan despite an existing B-tree index on order_date. What is the best first fix?

You now have a repeatable method for slow-query prompts:

• Always measure with EXPLAIN (ANALYZE, BUFFERS) and focus on the dominant node.
• Fix stale statistics first when estimates and actuals diverge.
• Make predicates sargable, add indexes for selective filters and join keys, and trim SELECT *.
• Address disk spills and over-fetching, then verify the new plan.

Narrate the checklist, propose a concrete change, and re-run the plan to prove it, that is the senior answer.