Query Performance

Reading execution plans, finding bottlenecks, and making slow queries fast

Why

Writing correct SQL is the baseline. Writing SQL that stays fast at scale is what separates strong engineers. A query that works in dev on 10k rows can become a minute-long scan in production on 100M rows. EXPLAIN tells you exactly what the database is doing — which indexes it's using, how many rows it's scanning, where the cost is. Without it, optimisation is guesswork.

Intuition

The query optimizer reads your SQL, generates several possible execution plans, estimates the cost of each, and picks the cheapest one based on table statistics. EXPLAIN shows you that chosen plan. EXPLAIN ANALYZE actually runs the query and shows real timing — the gap between estimated and actual rows is where optimizer mistakes live.

The two things to look for: Seq Scan on a large table (bad — full scan, missing index) and Nested Loop on large outer sets (can be bad — O(n) index lookups). Index Scan and Hash Join are usually what you want.

Explanation

EXPLAIN and EXPLAIN ANALYZE

-- EXPLAIN — show the execution plan without running the query EXPLAIN SELECT * FROM orders WHERE customer_id = 42; -- EXPLAIN ANALYZE — run the query and show actual vs estimated times EXPLAIN ANALYZE SELECT o.id, c.name, o.amount FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending' ORDER BY o.created_at; -- Key things to look for in the output: -- Seq Scan → full table scan — likely missing index -- Index Scan → using an index — good -- Index Only Scan → serving from index alone (covering index) — best -- Hash Join → efficient for large tables -- Nested Loop → good for small sets, can be slow for large ones -- rows= estimate vs actual → large gap means stale statistics -- run ANALYZE table_name to refresh

Common performance patterns

-- 1. Avoid functions on indexed columns in WHERE — prevents index use -- BAD: WHERE YEAR(created_at) = 2024 -- GOOD: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' -- 2. Avoid SELECT * in production queries -- Fetches all columns including large text/json blobs unnecessarily -- 3. Use EXISTS instead of COUNT for existence checks -- BAD (scans and counts all matching rows): WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) > 0 -- GOOD (stops at the first match): WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id) -- 4. Pagination — OFFSET gets slower as page number grows -- BAD for large offsets: SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 100000 -- GOOD — keyset pagination (use last seen id): SELECT * FROM events WHERE id > :last_id ORDER BY id LIMIT 20

N+1 and join vs subquery performance

-- N+1 pattern: running a query per row of an outer result -- This is almost always wrong in SQL — use a JOIN instead -- BAD (conceptually — N+1 in an ORM context): -- For each customer, SELECT their latest order separately -- GOOD: one query with a window function SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, amount, created_at FROM orders ORDER BY customer_id, created_at DESC; -- Postgres DISTINCT ON -- Or with ROW_NUMBER: SELECT customer_id, order_id, amount FROM ( SELECT customer_id, id AS order_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders ) t WHERE rn = 1;

← Previous Practice interview questions →