Detailed explanation
Optimising a slow SQL query starts with measurement, not blindly adding indexes. Capture the exact query, representative parameter values, table sizes, existing indexes, and baseline execution time. Check whether the delay comes from database execution, lock waits, transferring too many rows, or application processing. Test against realistic data because a query that is fast on a small development table may behave very differently in production.
Read the execution plan to understand how the database accesses and combines rows. Look for expensive scans, repeated inner-loop operations, large sorts, temporary-disk spills, and major differences between estimated and actual row counts. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) provides execution timings, actual row counts, and buffer activity, but it executes the statement, so use it carefully. Poor estimates can indicate stale statistics or skewed data. A full table scan is not automatically wrong: it may be cheaper when the query needs a large proportion of the table.
Next, improve access paths and reduce unnecessary work. Consider indexes for selective WHERE predicates, join keys, and relevant ordering patterns; do not index every referenced column. Composite index column order should match the query's filtering and ordering requirements, and extra indexes increase storage and write costs. Select only required columns to reduce transfer and potentially enable covering-index access. In the example, LOWER(email) usually cannot use a plain index on email for a direct lookup. PostgreSQL supports CREATE INDEX idx_email_lower ON orders (LOWER(email)), which can support the matching expression. Equivalent syntax and support vary across databases.
Finally, examine query structure without changing its meaning. Correlated subqueries may cause repeated work, but optimisers can sometimes transform them already; compare plans before replacing them with joins or EXISTS. Joins can accidentally introduce duplicate rows. For large result sets, use pagination with deterministic ordering, preferably keyset pagination for deep navigation when suitable indexes exist. After each change, verify identical results and compare latency, rows processed, and resource usage across representative inputs. Interviewers value this evidence-driven process more than a checklist of supposedly universal fixes.
Key points to remember
- Measure execution time and distinguish query work from lock waits and result-transfer delays.
- Inspect actual execution plans, row estimates, scans, join loops, sorts, and spills.
- Design selective, query-aligned indexes while accounting for storage and write overhead.
- Reduce unnecessary columns and rows; use expression indexes when predicates require functions.
- Validate correctness and benchmark each change with representative data and parameters.
Common mistakes candidates make
- Assuming every table scan is bad or indexing every WHERE, JOIN, and ORDER BY column separately.
- Replacing correlated subqueries with joins without checking duplicate rows, NULL behaviour, or the execution plan.
- Treating LIMIT with a large OFFSET as a complete performance fix, or testing only on tiny datasets.
Likely follow-up questions
Why might the optimiser ignore an existing index?
A scan may be cheaper if the query returns a large fraction of the table or requires many random row lookups. Stale statistics, incompatible expressions, implicit conversions, or an unsuitable composite-index order can also prevent effective index use.
How does keyset pagination differ from OFFSET pagination?
OFFSET pagination generally processes and skips preceding rows, making deep pages expensive. Keyset pagination resumes after the last seen ordering key, such as WHERE id > :last_id ORDER BY id LIMIT 50, using an appropriate index and a unique, deterministic ordering.
Is replacing a correlated subquery with a join always faster?
No; the optimiser may already transform the subquery, and indexed correlated lookups can be efficient. Compare actual plans and preserve semantics: for existence checks, EXISTS avoids multiplying outer rows when several inner rows match.
Interview tip: Present a diagnose–change–validate workflow, then use the LOWER(email) example to explain why index design must match the predicate and database engine.