Find root cause of slow query

Hook: A slow SQL query is usually a traffic jam, not a mystery — interviewers love this question because it shows whether you diagnose with evidence instead of guessing.

Question: How do I find the root cause of a slow query?

Answer: I start by reproducing the exact query and measuring its real execution plan. Then I look for the biggest bottleneck: too many rows read, a bad join order, a sort or hash that spills to disk, stale statistics, or blocking from locks. Once I know where the time is going, I change one thing at a time and confirm the fix with another measured run.

Interview-Ready Answer: I would first reproduce the query with the same parameters and run an execution plan with actual timings, not just the SQL text. Then I compare estimated rows to actual rows, because a big gap usually points to stale statistics or skewed data, and I check for scans, expensive joins, sort spills, and lock waits. After that I fix the most likely cause — for example by rewriting a non-sargable predicate, adding the right index, or refreshing statistics — and I verify the improvement with another plan and timing check.

Detailed Explanation: A slow query is not diagnosed by guessing the fix; it is diagnosed by finding the bottleneck. In practice, the root cause usually lives in one of five places: too many rows read, too many rows joined, too much work to sort or hash, waiting on locks, or bad estimates from statistics.

Step-by-step flow

1. Reproduce the same workload. Run the exact SQL with the same parameters and similar data volume. A query that is slow only during a busy window often points to blocking or I/O pressure, while a query that is always slow is more likely a plan issue.
2. Inspect the execution plan with actual timing. Use EXPLAIN ANALYZE (or the vendor equivalent) so you see real runtime, not just estimates. The key interview clue is the gap between estimated rows and actual rows; rows are the count of records the optimizer thinks it will touch. Big gaps usually mean stale statistics, data skew, or a filter that the optimizer cannot model well.
3. Look for access-path mistakes. If you see Seq Scan, the database is reading the table row by row. That can be fine for tiny tables or low-selectivity filters, but it is a red flag when only a small fraction should match. Also watch for functions on indexed columns, leading wildcards like %abc, or implicit type casts; these make a predicate non-sargable, meaning the index cannot be used efficiently.
4. Check joins, sorts, and hashes. A wrong join order can explode row counts. A sort or hash that does not fit in memory may spill to disk, which is much slower than RAM. In PostgreSQL, the default work_mem is 4MB, so a moderately wide result set can spill even when the query looks small on paper.
5. Check waits outside the SQL text. The query may be innocent but blocked by another transaction, or slowed because the data is not in cache and the storage layer is busy. This is why root-cause analysis must separate CPU time, I/O time, and lock waits.
6. Change one thing and remeasure. Rewrite the predicate, add the right index, refresh statistics with ANALYZE, or reduce the amount of data returned. Do one fix at a time so you can prove which change solved the issue.

Plan clues vs likely meaning

An easy memory trick: if the plan says the query should touch 10 rows but it actually touches 100,000, the planner is blind somewhere. That blindness is often the real root cause.

Performance notes

Think in trade-offs. A table scan is O(n) because the engine may inspect every row. An index lookup is closer to O(log n + k), where k is the number of matched rows. That is why indexes help most when a query is selective, meaning it returns a small slice of the table. But an index is not free: every insert, update, and delete also pays the index maintenance cost.

In other words, the root cause is not just 'slow SQL'; it is 'wrong work for this shape of data and load'. Your job is to find that mismatch and prove it with the plan.

Real-World Story: In an e-commerce checkout service, a customer order lookup query suddenly started taking 3 to 5 seconds instead of 80 milliseconds. The team had already added an index on created_at, so they first blamed the database server, but the real issue was that the query used DATE(created_at) in the filter and then sorted a large result set. The symptoms were clear: p95 latency jumped, logs showed Seq Scan and Sort Method: external merge Disk, and users saw checkout timeouts or abandoned carts. The fix was to rewrite the filter as a range, confirm the plan with EXPLAIN ANALYZE, and keep statistics fresh after data loads. If they had only added more hardware, the problem would have returned during the next traffic spike.

Follow-up & Tricky Questions:

• How do you tell whether the query is slow or the system is busy? I compare the plan with wait information: if CPU is low but the session is waiting, it is likely locks or I/O; if the same SQL is always slow on the same data, it is more likely a plan problem.
• Why compare estimated rows and actual rows? The estimate is the optimizer's guess, and the actual row count is what really happened. A big mismatch usually means bad statistics, skew, or a predicate the optimizer cannot reason about well.
• When would you rewrite the SQL instead of adding an index? If the predicate is non-sargable, like a function on a column or a leading wildcard, rewriting is usually the first fix because the current shape blocks index use.
• What if the query is fast alone but slow in the application? Then the root cause may be outside the SQL itself: connection pool waits, network latency, app-side loops, or an N+1 query pattern where the app fires many small queries instead of one good one.
• How do you confirm the fix? Re-run the same query, compare the plan, check the row counts, and make sure the new change did not slow down writes or other queries.
• Does adding an index always help? No. If the query returns most of the table, a scan can be cheaper, and every extra index makes inserts, updates, and deletes more expensive.
• Is EXPLAIN enough? Usually no. EXPLAIN shows the plan, but EXPLAIN ANALYZE shows the real runtime, which is what you need for root-cause analysis.
• Can a query with an index still do a full scan? Yes. Low selectivity, stale stats, function-wrapped columns, or implicit casts can make the optimizer skip the index even when it exists.

Common Mistakes:

• Adding an index first. Correction: inspect the plan first so you know whether the problem is access path, join order, memory spill, or locking.
• Reading only estimated rows. Correction: always compare estimated rows with actual rows, because the gap is often the clue to the root cause.
• Forgetting non-query causes. Correction: a slow query can be blocked by locks or slowed by I/O, even if the SQL text is fine.
• Changing too many things at once. Correction: apply one fix, measure, then move to the next; otherwise you will not know what helped.

Memory Hook: Think road, lights, load: the road is the plan, the lights are locks, and the load is sort/hash pressure. Check them in that order under interview pressure.

Cheat Sheet:

• Reproduce the exact slow case.
• Use EXPLAIN ANALYZE, not guesswork.
• Compare estimated rows to actual rows.
• Look for scans, bad joins, and spills.
• Check locks, I/O, and cache pressure.
• Fix one thing, then retest.

Practice Tasks:

• Run a query with DATE(column), then rewrite it as a date range and compare the plan.
• Add a filter that matches 90 percent of a table, then change it to match 10 percent and see when the index becomes useful.
• Create a join that multiplies rows, then use EXPLAIN ANALYZE to spot the row explosion.