How do you diagnose and fix a slow SQL query?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Reproduce with EXPLAIN (ANALYZE, BUFFERS) to see the plan the engine actually ran — look for sequential scans on big tables, wrong row estimates, and spill-to-disk sorts. Fix by adding or correcting indexes, rewriting the query (avoid SELECT *, functions on indexed columns, leading wildcards), and rechecking the plan. Measure before and after.
Detailed explanation
Start by capturing the real query and its parameters, then EXPLAIN (ANALYZE, BUFFERS) it: ANALYZE executes and shows actual vs estimated rows per node, BUFFERS shows cache hits vs disk reads. Read the plan for the usual culprits: Seq Scan on a large table where an index should apply (missing index, or a function wrapping the column like WHERE lower(email) = … defeating it — use a functional index instead); Nested Loop joining millions of rows where a Hash Join fits better (often stale statistics — run ANALYZE on the table); Sort spilling to disk (work_mem too small or an unindexed ORDER BY over huge input); and wildly wrong row estimates (rows=10 vs actual 2M), which poison every downstream plan choice and point at stale stats or correlated columns. Fixes in order: update statistics (ANALYZE), add the missing index (or fix selectivity — a leading-wildcard LIKE '%x%' can't use a B-tree; use trigram/pg_trgm or full-text search), rewrite (select only needed columns, push filters into subqueries, replace N+1 loops with one JOIN, paginate with keyset instead of OFFSET), and only then consider denormalization, materialized views, or caching. Verify with the plan and wall-clock time on production-like data — 'feels faster locally' is not evidence. Keep pg_stat_statements enabled so the next slow query finds you instead of a user report.
Example
-- Diagnose: actual plan, timings, buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents FROM orders
WHERE customer_id = 7 ORDER BY placed_at DESC LIMIT 20;
-- Bad: function on indexed column defeats the index
-- WHERE lower(email) = 'a@x.com'
-- Fix: functional index
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- Bad: huge OFFSET re-scans everything
-- ... ORDER BY id LIMIT 20 OFFSET 200000
-- Fix: keyset pagination (stable + O(log n))
-- ... WHERE id > 200000 ORDER BY id LIMIT 20;Interview tip
Walk the loop out loud: reproduce → EXPLAIN ANALYZE → name the node (seq scan, bad estimate, disk sort) → fix → re-measure. Interviewers hire the loop, not any single trick.
Common mistakes
- Adding indexes blindly without reading the plan first.
- Using SELECT * and OFFSET pagination on large tables.
- Wrapping indexed columns in functions or leading-wildcard LIKEs.