How do you optimize a slow SQL query? | Interview Question

Q: How do you optimize a slow SQL query?

**Target Audience:** Developers who own a slow query and want a method instead of guesses, and candidates facing the open-ended "how would you optimise this?" interview question. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (plans and timings quoted here were produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use). The method is the same on MySQL (`EXPLAIN ANALYZE` since 8.0.18, per the [MySQL blog announcing it](https://dev.mysql.com/blog-archive/mysql-explain-analyze/)) and SQL Server (actual execution plans); only the plan vocabulary differs. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every plan, result and timing shown was produced by running it; the **Verified** section quotes the example output in full. Timings are from one machine and vary between runs; the row and page counts are the stable part. T

The problem

Question presented to candidate: "A report query that used to take 200 milliseconds now takes 30 seconds. Nobody knows why. How do you approach making it fast again — what do you look at first, and what kinds of fixes do you reach for?"

What a strong answer should cover:

Measure before changing anything: find the slow statements (pg_stat_statements, the slow query log), reproduce with real parameters, and read EXPLAIN (ANALYZE, BUFFERS) to find the node where the time and the pages go
Read fewer rows: a missing or unusable index, a non-sargable predicate (function or cast on the column), an ORDER BY ... LIMIT without a matching index, stale statistics
Do less work per row: correlated subqueries or N+1 loops from the application, needless DISTINCT or sorts, joins that multiply rows before aggregating
Avoid spills and wasted transfer: sorts and hashes exceeding work_mem go to disk; SELECT * and unbounded result sets move data nobody uses; OFFSET pagination reads and discards rows
When the query is already as good as it gets: change the data shape (summary table, materialized view, partitioning, denormalisation) or the workload (cache, move analytics off the OLTP primary)
Verify every change with the same measurement, one change at a time, and weigh the cost (an index slows writes)

Clarifying questions expected:

How slow, how often does it run, and what is the target?
What changed: data volume, a deploy, a new filter, a database upgrade?
Is it a read on a busy OLTP database or a report that could run somewhere else?

Code / implementation expected: Yes — read a plan, rewrite one query (sargable range, aggregate-then-join, index for ORDER BY ... LIMIT) and show the before/after plans.

Schema used by the examples:

query-optimization

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who own a slow query and want a method instead of guesses, and candidates facing the open-ended "how would you optimise this?" interview question. Difficulty: Hard Engines: PostgreSQL 16.15 (plans and timings quoted here were produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use). The method is the same on MySQL (EXPLAIN ANALYZE since 8.0.18, per the MySQL blog announcing it) and SQL Server (actual execution plans); only the plan vocabulary differs.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every plan, result and timing shown was produced by running it; the Verified section quotes the example output in full.

The solution is hidden so you can try first

17 min read · 14 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: a function on the indexed date column forces a SCAN; a half-open range gives the same answer with SEARCH

SQLSQLite: a correlated subquery run twice per customer, rewritten as aggregate-once-then-join

PostgreSQL: a sort that spills to disk at work_mem 1MB and stays in memory at 64MB

SET max_parallel_workers_per_gather = 0;
CREATE TABLE big_orders (
  id int PRIMARY KEY,
  customer_id int NOT NULL,
  order_date date NOT NULL,
  amount numeric NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO big_orders
SELECT g, (g * 7919) % 5000 + 1, DATE '2024-01-01' + (g * 31) % 366, (g * 37) % 1000 + 1
FROM generate_series(1, 200000) AS g;
ANALYZE big_orders;
CREATE FUNCTION plan_analyze(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
    RETURN NEXT r."QUERY PLAN";
  END LOOP;
END $$;
CREATE TABLE steps (n serial, step text, plan_line text);

SET work_mem = '1MB';
INSERT INTO steps (step, plan_line)
SELECT 'work_mem 1MB', plan_analyze('SELECT id, amount FROM big_orders ORDER BY amount DESC, id');

SET work_mem = '64MB';
INSERT INTO steps (step, plan_line)
SELECT 'work_mem 64MB', plan_analyze('SELECT id, amount FROM big_orders ORDER BY amount DESC, id');

SELECT step, plan_line FROM steps ORDER BY n;

PostgreSQL: latest 10 orders — reading 200,000 rows to sort vs 10 rows from a matching index

SET max_parallel_workers_per_gather = 0;
CREATE TABLE big_orders (
  id int PRIMARY KEY,
  customer_id int NOT NULL,
  order_date date NOT NULL,
  amount numeric NOT NULL
) WITH (autovacuum_enabled = off);
INSERT INTO big_orders
SELECT g, (g * 7919) % 5000 + 1, DATE '2024-01-01' + (g * 31) % 366, (g * 37) % 1000 + 1
FROM generate_series(1, 200000) AS g;
ANALYZE big_orders;
CREATE FUNCTION plan_analyze(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) ' || q LOOP
    RETURN NEXT r."QUERY PLAN";
  END LOOP;
END $$;
CREATE TABLE steps (n serial, step text, plan_line text);

INSERT INTO steps (step, plan_line)
SELECT 'no index', plan_analyze('SELECT id, order_date, amount FROM big_orders ORDER BY order_date DESC, id DESC LIMIT 10');

CREATE INDEX big_orders_date_id_idx ON big_orders (order_date, id);
INSERT INTO steps (step, plan_line)
SELECT 'index (order_date, id)', plan_analyze('SELECT id, order_date, amount FROM big_orders ORDER BY order_date DESC, id DESC LIMIT 10');

SELECT step, plan_line FROM steps ORDER BY n;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.