SQL Query Optimization

Query optimization is the process of understanding how the database engine actually executes a query and adjusting the query, schema, or indexes so it runs faster. Two identical-looking queries can have wildly different performance depending on whether the engine can use an index, how many rows it has to examine, and how it joins tables together.

The starting point for almost all optimization work is asking the database to show you its execution plan — the actual step-by-step strategy it intends to use (or used) to answer a query.

Why it matters

A query that is fast on a hundred rows in development can bring a site down on ten million in production. Optimisation is the skill of finding out why a query is slow and fixing the cause. The N+1 problem alone, which is easy to create with an ORM, is behind a large share of slow pages.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN asks the database to show the execution plan it would use for a query — which indexes (if any) it plans to use, in what order it joins tables, and its estimated cost — without actually running the query. EXPLAIN ANALYZE goes further: it actually executes the query and reports the real number of rows processed and real time spent at each step, alongside the planned estimates. Comparing the estimated rows to the actual rows in EXPLAIN ANALYZE output is one of the fastest ways to spot a bad assumption the optimizer made, such as outdated table statistics.

Why an Index Isn't Used

Having an index does not guarantee the database will use it. Common reasons an index gets skipped: the table is small enough that a full scan is actually cheaper than the overhead of using the index; the query applies a function or type conversion to the indexed column (e.g. WHERE LOWER(email) = ... against a plain index on email); a leading wildcard is used in a LIKE pattern (LIKE '%gmail.com' cannot use a standard B-tree index efficiently, while LIKE 'asha%' can); the query's WHERE clause doesn't match the leftmost columns of a composite index; or the table's statistics are stale, causing the optimizer to misjudge how selective the index actually is.

The N+1 Query Problem

The N+1 problem is a very common performance bug, especially in applications using an ORM (object-relational mapper). It happens when code runs one query to fetch a list of N parent records, and then, for each of those N records, runs a separate query to fetch related data — resulting in 1 + N total queries instead of 2. For example, fetching 100 blog posts and then looping through them to fetch each post's author with a separate query results in 101 round trips to the database instead of a single JOIN or a single batched query.

Examples

EXPLAIN ANALYZE
SELECT id, name FROM customers WHERE email = 'asha@example.com';

Index Scan using idx_customers_email on customers
  (cost=0.42..8.44 rows=1 width=36)
  (actual time=0.031..0.033 rows=1 loops=1)
  Index Cond: (email = 'asha@example.com'::text)
Planning Time: 0.112 ms
Execution Time: 0.052 ms

-- Confirms the index is used (Index Scan, not Seq Scan) and estimated rows (1)
-- closely match actual rows (1) — a healthy, well-optimized plan.

-- N+1 pattern: 1 query for posts, then N queries for authors (bad)
-- SELECT id, title, author_id FROM posts;
-- (then, for each post) SELECT name FROM authors WHERE id = ?;

-- Fixed: a single query using JOIN
SELECT p.id, p.title, a.name AS author_name
FROM posts p
JOIN authors a ON a.id = p.author_id;

id: 1 | title: 'Intro to Indexes'    | author_name: 'Asha Verma'
id: 2 | title: 'Understanding Joins'  | author_name: 'Rahul Mehta'
(2 rows returned in a single round trip instead of 1 + N separate queries)

Exercise

An application lists books by running one query for the books and then a separate query for the author of each book, which is the N+1 problem. Replace all of that with a single query that returns each book's title with its author's name, ordered by title.

Expected output: title | author, Clean Code | Robert Martin, Refactoring | Martin Fowler (one per line)

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER REFERENCES authors(id));
INSERT INTO authors (id, name) VALUES (1, 'Robert Martin'), (2, 'Martin Fowler');
INSERT INTO books (id, title, author_id) VALUES (1, 'Clean Code', 1), (2, 'Refactoring', 2);

-- The slow way: SELECT * FROM books, then for each book
-- SELECT name FROM authors WHERE id = <author_id>

-- TODO: one query returning title and author name, ordered by title

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER REFERENCES authors(id));
INSERT INTO authors (id, name) VALUES (1, 'Robert Martin'), (2, 'Martin Fowler');
INSERT INTO books (id, title, author_id) VALUES (1, 'Clean Code', 1), (2, 'Refactoring', 2);

SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY b.title;

Quick quiz

Interview questions