Query Optimization
Make queries fast with EXPLAIN, indexes, selective joins, and avoiding common pitfalls.
What you'll learn
- Using EXPLAIN and EXPLAIN ANALYZE to understand query plans
- Identifying sequential scans and missing indexes
- Optimizing JOINs by filtering early and joining in the right order
- Avoiding N+1 queries and the SELECT * trap
- Partitioning and denormalization for large tables
Concept
EXPLAIN and EXPLAIN ANALYZE
EXPLAIN shows the query plan the database will use — which tables are scanned, which indexes are used, how tables are joined, and estimated costs. EXPLAIN ANALYZE actually runs the query and shows real timing data. This is the single most important tool for optimization. Look for Sequential Scan on large tables (bad — it reads every row) vs Index Scan (good — it uses an index). Look for high-cost nodes at the top of the plan.
Missing Indexes
If EXPLAIN shows a sequential scan on a large table, you likely need an index on the filtered column. Add the index and re-run EXPLAIN to confirm it is used. But remember: the optimizer may choose not to use an index if the table is small or if the query returns a large fraction of rows (an index is not helpful when you read most of the table anyway).
Optimizing JOINs
The database optimizer usually picks a good join order, but you can help by: (1) filtering early — put restrictive WHERE conditions before joining, (2) joining on indexed columns (foreign keys should be indexed), (3) avoiding joining large tables without a filter. If a join is slow, check that the join columns have indexes and that the WHERE clause reduces the row count significantly before the join.
The N+1 Problem
The N+1 query problem occurs when you fetch N records and then make one additional query per record (N+1 total queries). For example, fetching 100 orders, then querying the user for each order individually — 101 queries. The fix is a JOIN (fetch orders and users in one query) or batch loading (load all 100 users in one query by their IDs). ORM eager-loading options exist specifically to solve this.
Partitioning and Denormalization
For very large tables (millions/billions of rows), consider partitioning — splitting the table into physical chunks by date or ID range. The database prunes partitions that cannot contain matching rows, turning a full scan of a billion-row table into a scan of one million-row partition. Denormalization — duplicating data to avoid expensive joins — is another option for read-heavy workloads, but it adds complexity to writes.
Example
-- See the query plan without running
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- See the plan with actual timing (runs the query)
EXPLAIN ANALYZE
SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;
-- If EXPLAIN shows Seq Scan on orders, add an index:
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
-- Avoid N+1: use a JOIN instead of per-row queries
SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);
-- Partition a large orders table by year (PostgreSQL)
CREATE TABLE orders (
id BIGSERIAL,
user_id BIGINT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
EXPLAIN ANALYZE to inspect a plan, adding a composite index to fix a sequential scan, a JOIN to avoid N+1, and range partitioning for a large table.
Try it
- SQL Formatter
Format EXPLAIN and complex queries for analysis.
- Code Diff
Compare query plans before and after optimization.
Common mistakes
The mistake
Adding an index without checking if the optimizer uses it
The fix
Run EXPLAIN before and after adding the index. If the optimizer still does a sequential scan, the index is not helping — the query may return too many rows or the condition may not match the index.
The mistake
The N+1 query problem in ORM code
The fix
Use eager loading (JOIN) or batch loading instead of lazy-loading related records in a loop. Monitor query count per request in development.
Practice Query Optimization
1 exercise
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.