How do you answer query optimisation questions in SQL?

In this article

1. Before you start
2. Why interviewers ask this
3. Query optimisation from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page belongs to the SQL interview questions guide. The box lists what you need first.

Why interviewers ask this

A query can return exactly the right rows and still be too slow, and a candidate who can only say "add an index" has not shown how to find the cause. Candidates report these questions in data engineering, backend and analytics loops, usually with a slow query on the table and a request to explain what you would do.

The interviewer wants a method: read the plan, find the step that does the most work, check whether the optimiser's estimate was sound, change one thing, then read the plan again. A weak answer lists tricks. A strong answer starts from the plan, explains why the optimiser chose it and says what the fix costs elsewhere, for example slower writes because every index must be kept up to date. A Staff-level answer adds the case where the fix is not an index at all but fresh statistics or a rewritten filter.

Query optimisation from first principles

Query optimiser

A query optimiser is the part of a database that turns a SQL statement into a query plan and picks the plan it estimates to be cheapest, so you describe the rows you want and the optimiser decides how to fetch them.

Query plan

A query plan is a tree of plan nodes, with scan nodes at the bottom and join, sort and aggregate nodes above them, that states the steps the database will take to produce the result of one query.

In plain words

Think of a satnav. You give it a destination, and it picks the route with the lowest estimated travel time, using a map of typical traffic. A slow journey has three possible causes. The map was out of date, a road you could have used was blocked, or no faster route existed. The analogy breaks in one place: a satnav reports minutes, while a database reports cost in arbitrary units that only make sense when compared with each other.

In one precise sentence, the optimiser picks the plan with the lowest estimated cost, so a slow query means the chosen plan does too much work, the estimate behind it was wrong, or no cheaper plan was available. The five steps below test those three causes in order. The examples use PostgreSQL, because its documentation prints complete plans; the ideas carry over to other engines.

Step 1: Read the plan from the bottom up

A query plan is read from the leaves up: the scan nodes at the bottom fetch rows, and the nodes above them join, sort or aggregate those rows. The optimiser builds a plan tree and picks the lowest estimated cost. The PostgreSQL 18 documentation (2026) says that PostgreSQL "devises a query plan for each query it receives", and the explain command prints it.

Each node shows an estimated cost, an estimated rows and a width. Cost is measured in arbitrary units set by the planner's cost parameters, and a node's cost includes its children's costs. The rows value is the number of rows the node emits, not the number it scans. A sequential scan is a plan step that reads every row of a table in order, as in the docs' example plan for the table tenk1:

Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

The cost ignores the time to convert output to text and send it to the client. You now know where to look; the next step checks whether to trust the numbers.

Step 2: Compare estimated rows with actual rows

Comparing estimated rows with actual rows tells you whether the optimiser chose a bad plan from good information or from bad information. The explain command prints the plan with estimates only. Adding the analyze option, written explain analyze, makes it run the query, so the plan also shows actual rows. As the PostgreSQL documentation puts it, the option "actually executes the query, and then displays the true row counts and true run time accumulated within each plan node". The separate analyze command in Step 5 samples the table to refresh statistics.

A real line from that page shows the pair side by side:

Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)

The estimate is rows=10 and the actual count is rows=10.00, so in this example the planner's picture of the data was right. Because the query really runs, a write statement under explain analyze really changes rows, so test those inside a transaction you can undo.

When estimated and actual rows are close, look at the filter and the index order (Steps 3 and 4). A wide gap points to estimation: stale statistics, correlated columns, or a filter the planner cannot estimate, such as a function on a column (Steps 3 and 5). The estimate is the table's row count multiplied by the selectivity of the condition (row estimation).

Step 3: Make the filter usable by an index

A function around an indexed column makes a plain index unusable, because the index is sorted by the column and not by the function's result. In Use The Index, Luke on case-insensitive search, an index on last_name cannot serve a search on the upper-cased name; the function is a black box to the index. The PostgreSQL plan shown there is a Seq Scan with a cost of 1722.00 for an estimated 50 rows. The page notes that for such a filter the optimizer "does a gross misestimation and expects 4% of the table rows". Here upper is the function wrapped around the column.

The fix is an index built on the same expression as the filter. A case-insensitive collation also avoids the wrapper.

-- the plain index on last_name cannot serve this filter
SELECT * FROM employees WHERE UPPER(last_name) = 'WINAND';
-- an expression index matches the filter exactly
CREATE INDEX emp_up_name ON employees (UPPER(last_name));

After a change like this, read the plan again and confirm that the scan node now uses the index.

Step 4: Put composite index columns in the order the query uses them

A composite index is a single index over several columns, sorted by its first column, then the second, so it serves a search that fixes the leading column. The concatenated index page says: "The database does not use the index because it cannot use single columns from a concatenated index arbitrarily."

Three ideas follow. First, one index with several columns is better than several single-column indexes in most cases (index merge page). Second, put equality columns first, then at most one range column, because an index supports only one range condition as an access predicate. Third, putting the most selective column first is a myth: the myth page calls it "just wrong".

PostgreSQL 18 adds skip scan, which can make an index on (a, b) usable for a filter on b alone when a has few distinct values (multicolumn index documentation). It is usually slower than a matching leading column.

Text description of the diagram

• A function around an indexed column makes a plain index unusable.
• A composite index is sorted by its first column, then the second.

Step 5: Fix the estimate when the statistics are wrong

Statistics are sampled and approximate, so estimates change after each analyze run. The PostgreSQL planner documentation says that collection is done by analyze, either manually or as background auto-analyze, and the planner reads these figures to estimate how many rows each condition keeps.

Sampling has limits. The undefined reference notes that the estimate of distinct values can sometimes be quite inaccurate. Correlated columns are a known weak spot: the planner multiplies their separate selectivities as if they were independent, which gives underestimates. In PostgreSQL the answer is extended statistics. After a bulk load, run analyze first, then read the plan again.

1 · Read the plan2 · Compare rows3 · Close: filter4 · Close: order5 · Far apartReplay

Step-by-step description

1. The optimiser builds a plan tree of estimated costs.
2. explain analyze adds actual rows to the estimates.
3. Remove functions around indexed columns.
4. Equality columns first, then one range.
5. Fix statistics or the unestimable filter.

• The optimiser builds a plan tree and picks the lowest estimated cost.
• Plain explain shows estimates, and explain analyze adds actual rows.
• A function around an indexed column makes a plain index unusable.
• A composite index is sorted by its first column, then the second.
• Statistics are sampled and approximate, so estimates change after each analyze run.
• A wide gap points to estimation: stale or missing statistics, correlated columns, or a filter the planner cannot estimate, such as a function on a column.

Worked example

The numbers below come from the tenk1 table in the PostgreSQL 18 documentation, read on 2026-10-26: 345 pages and 10,000 rows. The sequential scan cost is pages times seq_page_cost plus rows times cpu_tuple_cost, which with the default values of 1.0 and 0.01 gives 345 × 1.0 + 10000 × 0.01 = 445.¹ Adding the filter unique1 < 7000 raises the plan cost to 470.00, an extra 25 units, or 0.0025 per row scanned.¹ A filter on a sequential scan does not reduce the cost; it only lowers the estimated rows, here to 7000.¹

Now compare the plans for unique1 < 100. The bitmap heap scan costs 224.98 for 100 rows, against the sequential scan's 470.00, so the index plan costs about 48% as much.¹ The index plan wins when the filter keeps few rows. For unique1 < 7000 the planner stays with the sequential scan.

The row estimate for the documented condition unique1 < 1000 is 10000 × 0.100697 = 1006.97, rounded to 1007.¹ Suppose explain analyze then reported 9,000 actual rows (illustrative): the estimate is about 8.9 times too low, which points to Step 5.¹ A limit clause also changes the arithmetic. An index scan totals 70.27 for 10 rows, and with a limit of 2 rows the limit node costs 14.28, about 20% of the total.¹

Last, a toy index of 100,000 sorted entries (illustrative) shows why Steps 3 and 4 matter. A search by the leading column needs at most 17 comparisons by binary search, while a search that cannot use the sort order reads all 100,000 entries.¹

Text description of the diagram

• The bitmap heap scan costs 224.98 against 470.00 for the sequential scan.
• The index plan wins when the filter keeps few rows.

Trade-offs and edge cases

Every fix helps one cause of a slow plan and costs something elsewhere, so name the cost when you answer.

Edge cases deserve a sentence each. Cost only reflects what the planner cares about, so a plan with a low cost can still be slow to send to the client. Sorting is a plan step too: a sort has a large start-up cost, because it must read all its input first; in the PostgreSQL 18 documentation (2026) a Sort node over tenk1 starts at 1109.39 (see SQL order by). A wrong row estimate below a join node feeds the decisions made above it, so check estimates under joins first (see SQL joins).

Plans also drift: statistics change after each analyze run, so the same query can get a different plan as data grows. Other engines print plans with their own commands and layouts; for the kinds of database, see types of relational database, and for engine-specific preparation, see SQL Server interview questions, Oracle SQL interview questions and SQL interview questions for testers.

How to answer it in an interview

The spoken structure: say you read the plan first, say what the estimate-versus-actual comparison decides, give one fix for each branch, then name the cost.

Common mistakes

• "explain tells me how long the query takes." (wrong) Plain explain shows estimated cost in arbitrary units. (right) "explain analyze runs the query and shows actual rows and time next to the estimates."
• "Wrapping the column in upper() is harmless." (wrong) It makes a plain index on the column unusable. (right) "I build an expression index on the same expression, or compare the bare column."
• "Put the most selective column first." (wrong) The Use The Index, Luke author calls this a myth. (right) "I put equality columns first, then at most one range column; selectivity matters mainly for independent range conditions."
• "One index per column is the same as one composite index." (wrong) One multi-column index is usually better. (right) "I build one composite index that matches the query's predicates."
• "The rows figure is how many rows the node scanned." (wrong) It is the number the node emits. (right) "rows is the estimated output of that node."

Follow-up questions

What is an execution plan in SQL, and how do explain and explain analyze differ?

An execution plan (query plan) is the tree of steps a database will take to answer one statement. The explain command prints only the planner's estimates, while explain analyze executes the query and adds true row counts and run time to each node, so it can change data when the statement writes. In PostgreSQL 18 it also prints buffer counts by default, as the documentation shows.

Why does the database ignore my index?

Four causes are common: the filter keeps so many rows that a sequential scan is cheaper, the filter is not sargable, the filter skips the index's leading column, or stale statistics made the optimiser expect the wrong row count. A sargable filter is one the database can answer by searching an index instead of testing every row, and a function on the column breaks it, as does a pattern match with a leading wildcard such as '%abc', because only the characters before the first wildcard can narrow the index search (see the pattern-match tuning page). The plan tells you which cause applies.

Should I create one index per column or one composite index, and which column goes first?

Create one composite index in most cases, because a single index with several columns usually beats several single-column indexes. Put equality columns first, then at most one range column, since an index supports only one range condition as an access predicate. Putting the most selective column first is not a general rule.

What if the row estimate is wrong by a large factor?

Run analyze first, because the statistics may be out of date. If the columns are correlated, create extended statistics. If the filter wraps a column in a function, index the expression. Then read the plan again and compare the new estimate with the actual rows.

What is a covering index, and why does selecting every column hurt?

A covering index holds every column a query needs, so the database can answer with an index-only scan, which reads the index without visiting the table (index-only scans). Selecting every column asks for columns the index lacks, so it rules that scan out and moves more data.

Which join algorithms does a database choose between?

The planner chooses between a nested loop join, a hash join and a merge join, and the PostgreSQL documentation shows each in plans. A nested loop suits few outer rows with an indexed lookup on the inner side, a hash join builds a hash table from one input, and a merge join reads two sorted inputs. A wrong row estimate can push the planner to the wrong one (see SQL joins).

Sources

1. Using EXPLAIN (PostgreSQL 18 documentation, chapter 14.1) — PostgreSQL Global Development Group, 13 Aug 2026
2. Statistics Used by the Planner (PostgreSQL 18 documentation, chapter 14.2) — PostgreSQL Global Development Group, 26 Oct 2026
3. Row Estimation Examples (PostgreSQL 18 documentation, chapter 69.1) — PostgreSQL Global Development Group, 26 Oct 2026
4. ANALYZE (PostgreSQL documentation) — PostgreSQL Global Development Group, 26 Oct 2026
5. Multicolumn Indexes (PostgreSQL documentation) — PostgreSQL Global Development Group, 26 Oct 2026
6. Concatenated indexes (Use The Index, Luke) — Markus Winand, 26 Oct 2026
7. Case-insensitive search using UPPER or LOWER (Use The Index, Luke) — Markus Winand, 26 Oct 2026
8. Myth: most selective column first (Use The Index, Luke) — Markus Winand, 26 Oct 2026
9. Index merge performance (Use The Index, Luke) — Markus Winand, 26 Oct 2026
10. LIKE performance tuning (Use The Index, Luke) — Markus Winand, 26 Oct 2026
11. Index-Only Scans and Covering Indexes (PostgreSQL documentation) — PostgreSQL Global Development Group, 26 Oct 2026