Query Optimization

What is it?

When a query is slow, guessing why rarely works well — you need to see what the database is actually doing. Every database has a way to ask it to explain its plan before (or while) running a query, usually via an EXPLAIN command. This shows you things like whether it's scanning every row in a table, whether it's using an index, and roughly how much work each step is estimated to cost.

Query optimization is the practice of reading that plan, spotting the expensive parts (often a full table scan where an index lookup would help, or a poorly ordered join), and fixing them — usually by adding an index, rewriting the query, or occasionally restructuring the schema.

Explain like I'm 10

Running EXPLAIN on a slow query is like asking a delivery driver to describe their planned route before they leave. If they say 'I'll drive past every house in the city checking addresses one by one,' you immediately know why the delivery is slow, and you can fix it — hand them a map (an index) instead.

Examples

Reading a query plan

EXPLAIN SELECT * FROM orders WHERE customer_email = 'amara@example.com';

-- Output might show:
-- Seq Scan on orders  (cost=0.00..18334.00 rows=1 width=72)
--   Filter: (customer_email = 'amara@example.com'::text)

'Seq Scan' means a sequential (full table) scan — the database is checking every row. On a large table, that's the expensive part to fix.

After adding an index

CREATE INDEX idx_orders_customer_email ON orders (customer_email);

EXPLAIN SELECT * FROM orders WHERE customer_email = 'amara@example.com';

-- Output might now show:
-- Index Scan using idx_orders_customer_email on orders
--   (cost=0.42..8.44 rows=1 width=72)

After the index exists, the plan switches to an 'Index Scan' with a dramatically lower estimated cost — the database jumps to matching rows instead of checking every one.

How it works

Before running a query, the database's query planner considers multiple possible ways to execute it (scan the whole table, use this index, use that index, join in this order versus that order), estimates the cost of each, and picks the plan it believes will be cheapest, based on statistics it keeps about the data (like how many rows a table has, or how many distinct values a column contains).

EXPLAIN reveals that chosen plan (and often, with a variant like EXPLAIN ANALYZE, the plan's actual measured cost after really running it). Common fixes once you spot a problem: add a missing index, rewrite a query to avoid an operation that blocks index use (like wrapping an indexed column in a function), or restructure an inefficient join.

Why does it exist?

Query optimization exists because a database's planner, while sophisticated, works from statistics and heuristics — it can make a poor choice, or a query can be written in a way that prevents it from using an index it otherwise could. Being able to see and reason about the actual execution plan is what turns "this query is slow" from a guessing game into a solvable, evidence-based problem.

When to use it

Reach for query optimization whenever a specific query is measurably slow, or before shipping a new query against a table you expect to grow large — checking the plan early can catch a missing index before it becomes a production problem.

When not to use it

Don't spend time optimizing a query that already runs fast and only touches a small amount of data — premature optimization adds complexity for no real benefit. Focus effort on queries that are actually measured to be slow or that run extremely often.

Common mistakes

• Adding an index without checking whether the query planner actually uses it — sometimes the fix requires rewriting the query, not just adding an index.

• Optimizing based on a guess instead of actually reading the EXPLAIN output, and fixing the wrong thing.

• Testing query performance only on a small local database, where a full table scan is fast enough to hide a problem that will appear at production scale.

Practice exercises

1. Easy:

   Explain what a 'Seq Scan' in a query plan tells you about how the database is executing that query.

2. Medium:

   Given a slow query filtering on an unindexed column, describe the steps you'd take to diagnose and fix it.

3. Hard:

   Explain why wrapping an indexed column in a function inside a WHERE clause (e.g. LOWER(email) = ...) can prevent the database from using that column's index.

Interview questions

What does the `EXPLAIN` command show you?

The execution plan the database intends to use for a query — whether it scans the whole table or uses an index, in what order it joins tables, and the estimated cost of each step.

What's the difference between `EXPLAIN` and `EXPLAIN ANALYZE`?

EXPLAIN shows the planned strategy and estimated costs without running the query; EXPLAIN ANALYZE actually executes it and reports the real, measured row counts and timings alongside the plan.

Name one common fix for a slow query doing a full table scan.

Adding an index on the column used in the WHERE clause or join condition, so the database can look up matching rows directly instead of checking every row.

What's the difference between a `Seq Scan` and an `Index Scan` in a query plan?

A Seq Scan reads every row in the table in order and checks each against the filter; an Index Scan uses an index to jump straight to the rows that match, without touching the rest.

Why might the planner choose a `Seq Scan` even when an index exists on the filtered column?

If the table is small, or the filter matches a large fraction of its rows, scanning it directly can be estimated as cheaper than the overhead of random-access index lookups — an index only pays off when it narrows the result down a lot.

What do the two numbers in a cost estimate like `cost=0.42..8.44` mean?

The first is the estimated startup cost — the work needed before the first row can be returned — and the second is the estimated total cost to return every row of that step.

What does the `rows=` estimate in an EXPLAIN plan represent, and where does it come from?

The planner's guess at how many rows that step will return, derived from statistics it keeps about the table — like how many rows it has and how many distinct values a column contains — not an actual count.

Why can EXPLAIN ANALYZE show a very different 'actual rows' than the plan's estimated 'rows', and why does that matter?

It means the planner's statistics are stale or the data's distribution is unusual, so its cost estimates (and therefore its choice of plan) may be based on a badly wrong guess — a large gap between estimated and actual rows is a strong signal to re-run ANALYZE on the table.

What is an `Index Only Scan`, and how does it differ from a regular `Index Scan`?

A regular index scan finds matching entries in the index, then still has to fetch the actual row from the table (the 'heap') for any column not stored in the index; an index-only scan can answer the query using just the index's own data, skipping that extra fetch.

What's a 'covering index', and why does it enable an index-only scan?

An index that includes every column the query needs, not just the one it filters on — because nothing outside the index has to be read, the database can skip visiting the table's actual rows entirely.

Why does an index scan in Postgres often still need to fetch the row from the table, whereas MySQL's InnoDB frequently doesn't for primary-key lookups?

Postgres stores table data as an unordered heap with indexes pointing into it, so most index scans require a separate fetch from the heap; InnoDB physically clusters row data around the primary key, so a primary-key index lookup already has the row data attached.

What is a `Bitmap Heap Scan`/`Bitmap Index Scan` pair, and when does the planner use it instead of a plain index scan?

The bitmap index scan builds an in-memory map of which pages contain matching rows, then the bitmap heap scan visits just those pages in physical order; the planner favors this when a query matches enough rows that jumping around row-by-row via a plain index scan would mean too much random I/O.

Why does wrapping an indexed column in a function (e.g. `LOWER(email) = ...`) typically prevent the planner from using an index on that column?

A standard index stores the column's raw values, not the result of applying a function to them, so the planner can't match LOWER(email) against an index built on plain email — it has to fall back to checking every row.

Instead of wrapping the column in `LOWER()`, how would you index a case-insensitive lookup so the planner can still use it?

Create a functional/expression index on the exact expression used in the query, e.g. CREATE INDEX ON users (LOWER(email)), so the index stores the lowercased values the query is actually filtering on.

Why does a leading wildcard in `LIKE '%something'` prevent a standard B-tree index from helping?

A B-tree index is ordered by the column's value from the start, so it can quickly jump to entries matching a known prefix; a pattern that can match anywhere in the string gives it no fixed starting point to jump to, so it still has to check every entry.

In a composite index on `(a, b)`, why can a query filtering only on `b` fail to use that index efficiently?

A composite index is ordered first by a and only by b within each value of a, so without a condition on a there's no way to narrow down where matching b values live — the database would have to scan the whole index anyway.

Why does column order matter when creating a composite index meant to speed up a query filtering on multiple columns?

The index can only be searched efficiently starting from its leading column(s); putting the most selective, most-often-filtered condition first lets the database narrow the search fastest, while the wrong order can leave the index barely more useful than a full scan.

What role does the `ANALYZE` command play in query planning (as distinct from `EXPLAIN ANALYZE`)?

ANALYZE recomputes and stores the table statistics — row counts, distinct value estimates, data distribution — that the planner relies on to estimate costs; without it, those statistics grow stale and the planner's choices get worse.

What happens to query plans if a table's statistics are stale, for example right after a large bulk load, and how do you fix it?

The planner keeps costing plans based on the old row counts and distributions, which can lead it to a plan that's wrong for the table's new size or shape — running ANALYZE (many databases also do this automatically on a schedule) refreshes the statistics so it can plan accurately again.

At a high level, what's the difference between a nested loop join, a hash join, and a merge join?

A nested loop join scans one input and, for each row, looks up matches in the other (cheap when one side is small or well-indexed); a hash join builds an in-memory hash table from one side and probes it with the other (good for larger unsorted inputs); a merge join walks both inputs in sorted order together (good when both are already sorted on the join key).

Why does join order matter for a multi-table query, and who decides it?

Joining tables in a different order can hugely change how many intermediate rows have to be produced and processed along the way; the query planner decides the order itself based on estimated costs, unless the number of tables is large enough that it falls back to heuristics instead of exhaustively costing every ordering.

Given a tiny 4-row `orders` table with no `WHERE` clause at all, what would `EXPLAIN SELECT * FROM orders;` show, even with an index on `id`?

A Seq Scan on orders — with no filter, every row must be returned anyway, so scanning the table directly is at least as cheap as consulting an index, and avoids the overhead of doing so; the existence of an index doesn't change this.

Debugging: a query has run fine in production for months and suddenly gets slow, with no change to the query text or schema. What's the first thing to check?

Whether the table's size or data distribution has changed enough that the previous plan (e.g. index scan) is no longer the cheapest one, or its statistics have gone stale — re-run EXPLAIN ANALYZE to see whether the plan itself has changed, and check when statistics were last updated.

Debugging: EXPLAIN shows the query is already using an index, but it's still slow. What's a likely reason?

The index scan is still matching a large number of rows (low selectivity), so it's not actually narrowing the work much, or the query needs columns outside the index and is paying for a heap fetch on every matched row.

Why might adding an index make a table's writes slower, and how is that a tradeoff against faster reads?

Every insert, update, or delete has to keep each index on the table in sync as well as the table itself, so more indexes mean more work per write — indexes trade some write throughput for faster reads on the columns they cover.

What's the practical effect of having too many indexes on a heavily written-to table?

Write throughput degrades because every write has to update every one of those indexes, and many of them may barely be used by actual queries — a table's index set generally needs periodic review against what's really being queried.

What's the difference between the 'cost' units shown by EXPLAIN and actual wall-clock time?

Cost is an abstract, internal unit the planner uses to compare plans against each other (roughly calibrated to disk-page fetches and per-row processing), not a prediction of milliseconds — only EXPLAIN ANALYZE's actual timings tell you real elapsed time.

What is a correlated subquery, and why can it be much slower than an equivalent join?

A subquery that references a column from the outer query, so it has to be re-evaluated once per outer row; a join, by contrast, lets the planner process the two tables together in a single pass instead of repeating a lookup for every row.

What does `EXPLAIN (ANALYZE, BUFFERS)` add over plain `EXPLAIN ANALYZE`, and why is that useful?

It reports how many data pages were read from cache versus read from disk for each step, which helps distinguish a step that's slow because of genuine disk I/O from one that's slow for some other reason entirely.

Why can two queries that return the same result set produce different EXPLAIN plans and different performance?

The planner works from the query's structure, not its intent — a rewritten version (a join instead of a subquery, a different clause order, an added redundant condition) can open up or shut off plan choices the original phrasing didn't, even though the final rows returned are identical.

Why can a query that performs well in local testing turn out to be slow in production despite identical query text?

A small local dataset can make a full table scan fast enough to hide a missing index entirely, and the planner may even choose different plans at different table sizes — a plan that's fine for thousands of rows can be the wrong one at millions.