Optimizing slow queries starts with analyzing the execution plan (EXPLAIN) to find the bottleneck, then applying targeted fixes — most commonly adding indexes, rewriting the query, and avoiding common anti-patterns. The biggest win is usually turning full table scans into index lookups.
EXPLAIN ANALYZE
orders customer_id ;
EXPLAIN (or EXPLAIN ANALYZE) shows how the database executes the query — crucially, whether it's doing a full table scan (slow) or using an index (fast). Always profile before optimizing.
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
The most common and impactful fix: add indexes on columns used in WHERE, JOIN, and ORDER BY. This turns full table scans into fast lookups.
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = '[email protected]' -- can't use an index on email
WHERE email = '[email protected]' -- ✅ can use the index
-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith' -- ✅ 'smith%' (prefix) can use an index
-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns -- less data, may use a covering index
-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
Query optimization is critical knowledge for database performance — slow queries are a leading cause of poor application performance, and understanding how to find and fix them is essential senior-level skill for anyone working with databases at scale.
The foundational discipline is analyzing with EXPLAIN (examining the execution plan to find the actual bottleneck — crucially whether the query does a slow full table scan or a fast index lookup) before optimizing, rather than guessing — this measurement-driven approach is what makes optimization effective.
The single biggest and most common win is adding indexes on filtered, joined, and sorted columns (turning full table scans into fast lookups — often improving query time by orders of magnitude), making index knowledge central to optimization.
Understanding common anti-patterns is equally important: functions on columns and leading-wildcard LIKE prevent index use, SELECT * fetches unnecessary data, and especially N+1 queries (querying in a loop instead of a single JOIN — a frequent, severe performance killer in application code) — recognizing and fixing these addresses the most common causes of slow queries.
Other techniques (composite indexes, reducing data scanned, rewriting subqueries as JOINs, keeping statistics updated, materialized views for expensive queries) round out the toolkit.
Since query performance directly affects application responsiveness and scalability, and since the systematic approach (analyze with EXPLAIN, then fix — usually by adding indexes and eliminating anti-patterns like N+1) is what keeps databases fast under load, mastering query optimization is important, high-value senior knowledge that distinguishes developers who can build performant, scalable database-backed applications — a frequently-relevant, critical skill and common interview topic for database performance.
A library of IT interview questions with detailed answers — from Junior to Senior.
Donate