SQL is declarative: you say what rows you want, and the optimizer decides how to fetch them — which index, which join order, which join algorithm. Interviewers probe this because "the query is slow" is the most common production incident a backend engineer owns, and the difference between a junior and a senior answer is method: measure first, read the plan, find the one node that dominates, fix the cause, and measure again. For a Backend or AI engineer it also decides whether a retrieval or analytics endpoint scales from thousands of rows to millions, and whether the fix is an index, a rewrite, or a different design entirely.
1. Confirm it is the DATABASE app time vs query time; N+1 (1 query per row)?
pool wait? lock wait? network / row transfer?
2. Capture the EXACT query slow-query log, pg_stat_statements (PostgreSQL),
and real parameter values performance_schema / slow log (MySQL). Parameters matter:
status = 'refunded' and status = 'shipped' can need
different plans.
3. Read the plan on realistic EXPLAIN (ANALYZE, BUFFERS) -- PostgreSQL
data volume EXPLAIN ANALYZE -- MySQL 8.0.18+
4. Find the dominant node highest actual time, or the largest gap between
estimated rows and actual rows
5. Name the cause missing / unusable index, stale statistics, bad join
order, sort or hash spilling to disk, too many rows
6. Change ONE thing, re-measure index, rewrite, ANALYZE, fewer columns, different
pagination; compare the plan and the timingThe order matters because the expensive mistakes are made in step 1 and step 6: adding an index to a query that was slow because of an N+1 loop in the application, or changing three things at once so nobody knows which one helped. A database can only be blamed after you have shown the time is spent inside it, and a fix only counts once the plan or the measured time changed in the direction you predicted. Also test against data of production size and shape: a plan that looks fine on 1,000 dev rows tells you nothing about 50 million rows, because the optimizer's choice depends on table size and value distribution.
-- PostgreSQL. Illustrative plan shape and numbers, NOT measured output.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded';
Hash Join (rows=1200 est) (actual rows=480000 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (rows=1200 est) (actual rows=480000 loops=1)
Filter: (status = 'refunded')
Rows Removed by Filter: 9520000
Buffers: shared hit=... read=...
-> Hash
-> Seq Scan on customers c (rows=... est) (actual rows=... loops=1)
Read it bottom-up / inside-out. Two things to look for:
* estimated rows vs actual rows 1200 vs 480000 = the planner was badly wrong
* "Rows Removed by Filter" huge = you read far more than you keptEXPLAIN only shows the plan the optimizer chose, with its estimates; it does not run the query. EXPLAIN ANALYZE executes the query and adds actual row counts, per-node time and loop counts, so you can compare what the optimizer expected with what happened. That gap is the most valuable signal: the optimizer picks a plan from statistics (row counts, value histograms, distinct counts), so when estimated rows are far below actual rows it may have chosen a nested loop or a poor join order for data it wrongly believed to be small. Common causes are stale statistics (fix: ANALYZE), correlated columns the planner treats as independent (PostgreSQL extended statistics via CREATE STATISTICS), or skewed values. Because EXPLAIN ANALYZE really executes, running it on an UPDATE or DELETE really changes data unless you wrap it in a transaction and roll back (locks are still taken, and triggers still fire). Cost numbers in a plan are unitless estimates for comparing plans within one engine, not milliseconds.
-- SQLite (real output). SQLite's EXPLAIN QUERY PLAN is much terser than PostgreSQL's,
-- but the vocabulary is the same idea: SCAN = read the whole table,
-- SEARCH ... USING INDEX = seek into an index.
SCAN orders
SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
SEARCH orders USING COVERING INDEX idx_orders_customer (customer_id=?)Table = 10,000 heap pages. Index on status.
Query returns 0.01% of rows Query returns 60% of rows
index: seek, ~a few index pages index: 6,000,000 random heap visits
+ a handful of heap fetches (each fetch may hit a different page)
seq scan: read all 10,000 pages seq scan: read 10,000 pages ONCE, in order
-> index wins by a large margin -> seq scan generally wins
cost
| seq scan (flat: always reads the table)
| ......................................
| .-'
| .-' index scan (grows with rows returned)
| .-'
| .-'
| .-'
+-------------------------------------- fraction of rows returned
crossover point (depends on engine, caching, storage)
PostgreSQL adds a middle option: Bitmap Index Scan -> Bitmap Heap Scan
(collect matching row locations from the index, sort them by page,
then visit each heap page once, in physical order)An index scan does not read the table faster; it reads less of it, at the price of random access. Each index hit is a pointer into the heap, and following many pointers means jumping around the disk (or, if cached, the buffer pool). A sequential scan reads every page but reads them in order, which storage and the OS prefetcher handle efficiently. So the crossover depends on the fraction of rows returned, on how well the index order matches the physical row order, and on the storage: PostgreSQL's random_page_cost defaults to 4.0 relative to 1.0 for sequential pages and is commonly lowered on SSDs, which shifts the crossover. That is why a low-selectivity predicate like status = 'shipped' (most rows) generally should not use an index, and why "the optimizer ignored my index" is often the optimizer being correct. Sargability is the other reason an index is ignored:
-- SQLite (real output, 20,000-row table, index on created_at)
WHERE substr(created_at,1,7) = '2024-03' -- function on the column
-> SCAN orders
WHERE created_at >= '2024-03-01'
AND created_at < '2024-04-01' -- same rows, range on the raw column
-> SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)An index is sorted by the raw column value, so a predicate must be expressible as a range or equality on that raw value to seek into it. Wrapping the column in a function, doing arithmetic on it, or triggering an implicit cast (comparing an integer column to a text parameter, or bigint to numeric in some engines) hides the sorted order from the optimizer. Fix by rewriting the predicate so the column stands alone, or by building an expression index (PostgreSQL and SQLite support CREATE INDEX ... ON t (lower(email)); MySQL 8.0.13+ supports functional key parts).
NESTED LOOP HASH JOIN MERGE JOIN
for each row r in OUTER: 1. BUILD: hash the smaller 1. both inputs sorted on
look up matching rows input on the join key the join key (via an
in INNER (ideally via an 2. PROBE: stream the other index or an explicit sort)
index seek) input, look each key up 2. walk both in step,
like merging two sorted lists
good: small outer + INDEX good: large, unsorted, good: inputs already sorted,
on inner join key no useful index, or a sort is needed anyway
bad: large outer, no index equality join limits: needs sorted input;
= outer x inner scans limits: equality only; hash sorting cost if not indexed
table may spill to disk
when it exceeds work_mem
(PostgreSQL)PostgreSQL implements all three. MySQL added hash join for equi-joins in 8.0.18 (earlier versions relied on nested-loop variants such as block nested loop), and SQLite only uses nested loops, sometimes building a temporary automatic index. The optimizer chooses among them using estimated row counts, which is why a wrong estimate is dangerous: if it believes the outer side has 10 rows it will pick a nested loop, and if the truth is 10 million, the inner lookup runs 10 million times. With a nested loop the join key on the inner table must be indexed, and note that PostgreSQL does not automatically index the referencing side of a foreign key (InnoDB creates an index for it if none exists). SQLite shows the plan flipping with the index (real output, 1,000 customers, 20,000 orders):
-- SQLite (real output)
-- same query both times: SELECT c.name, o.total FROM customers c JOIN orders o
-- ON o.customer_id = c.id WHERE c.country = 'IN'
-- no index on orders.customer_id: scan orders, probe customers by primary key
SCAN o
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
-- after CREATE INDEX idx_orders_customer ON orders(customer_id): scan the
-- filtered customers, seek into the big orders table for each one
SCAN c
SEARCH o USING INDEX idx_orders_customer (customer_id=?)The usual causes of a slow join, in the order worth checking: a missing index on the join key of the probed table; mismatched key types forcing casts that disable the index; a missing or wrong ON condition (a cartesian product: in the same SQLite data, 1,000 customers cross 20,000 orders returned 20,000,000 rows); stale statistics leading to the wrong join order or algorithm; a hash or sort spilling to disk because it exceeds memory (work_mem in PostgreSQL, per operation); and filters applied late so a join processes far more rows than necessary. Very large join graphs also strain the planner itself (PostgreSQL switches from exhaustive search to a genetic optimizer above a table-count threshold, and join_collapse_limit bounds how far it reorders explicit JOINs).
Ask first: does anyone need millions of rows in the application?
aggregate in the DB SELECT status, count(*) ... GROUP BY status
select fewer columns SELECT id, total, not SELECT *
paginate with a KEYSET WHERE id > :last_seen ORDER BY id LIMIT 50
pre-aggregate / cache summary table, materialized view
stream, do not buffer server-side cursor / batch loop, not one giant list
partition the data prune old / irrelevant partitions
OFFSET vs KEYSET pagination, SQLite (real measurement of VM work, 20,000 rows):
ORDER BY id LIMIT 50 OFFSET 15000 -> 30,360 VM steps
WHERE id > 15000 ORDER BY id LIMIT 50 -> 310 VM stepsOFFSET n cannot skip rows it has not read: the engine produces the first n rows and throws them away, so deep pages get progressively slower, and rows inserted between page requests shift the window (duplicates or gaps). Keyset (seek) pagination remembers the last key and seeks straight to it through the index, so page 1 and page 1,000 cost about the same, at the cost that you can only go forward and backward from a known key, not jump to page N, and the sort key must be unique (or paired with a unique tie-breaker such as (created_at, id)). The step counts above come from SQLite's progress handler and are a proxy for work, not wall-clock time.
Selecting only needed columns is the other big lever. If every requested column lives in the index, the engine can answer from the index alone and never touch the table (a covering index, "index-only scan" in PostgreSQL, which also depends on the visibility map being up to date). In SQLite, selecting just customer_id gave USING COVERING INDEX, while selecting customer_id, total gave a plain USING INDEX that must visit the table row for total. SELECT * generally forecloses that, drags wide columns (large text, JSON, blobs) across the network, and silently changes what your code receives when someone adds a column.
EXPLAIN ANALYZE, find the dominant node, change one thing, re-measure.EXPLAIN shows the chosen plan and estimates without running it; EXPLAIN ANALYZE runs the query and adds actual rows and time. Wrap EXPLAIN ANALYZE on writes in a transaction you roll back.OFFSET, stream instead of loading everything.EXPLAIN prints that tree; EXPLAIN ANALYZE also records the actual rows, time and loops per node as it runs.-- SQLite (run: sqlite3 :memory: < demo.sql). Output below is real.
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
total REAL, created_at TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i<1000)
INSERT INTO customers SELECT i, 'cust'||i,
CASE i%4 WHEN 0 THEN 'IN' WHEN 1 THEN 'US' WHEN 2 THEN 'DE' ELSE 'JP' END FROM n;
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i<20000)
INSERT INTO orders SELECT i, (i*7)%1000+1,
CASE WHEN i%50=0 THEN 'refunded' WHEN i%3=0 THEN 'pending' ELSE 'shipped' END,
(i%500)+0.5, date('2024-01-01', '+'||(i%365)||' days') FROM n;
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
-- SCAN orders
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 42;
-- SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
EXPLAIN QUERY PLAN SELECT customer_id FROM orders WHERE customer_id = 42;
-- SEARCH orders USING COVERING INDEX idx_orders_customer (customer_id=?)
EXPLAIN QUERY PLAN SELECT customer_id, total FROM orders WHERE customer_id = 42;
-- SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
CREATE INDEX idx_orders_created ON orders(created_at);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE substr(created_at,1,7) = '2024-03';
-- SCAN orders
EXPLAIN QUERY PLAN SELECT * FROM orders
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
EXPLAIN QUERY PLAN SELECT * FROM orders ORDER BY total DESC LIMIT 20;
-- SCAN orders
-- USE TEMP B-TREE FOR ORDER BY
CREATE INDEX idx_orders_total ON orders(total);
EXPLAIN QUERY PLAN SELECT * FROM orders ORDER BY total DESC LIMIT 20;
-- SCAN orders USING INDEX idx_orders_total (no sort step any more)
EXPLAIN QUERY PLAN SELECT c.name, o.total FROM customers c
JOIN orders o ON o.customer_id = c.id WHERE c.country = 'IN';
-- SCAN c
-- SEARCH o USING INDEX idx_orders_customer (customer_id=?)
SELECT count(*) FROM customers c, orders o; -- forgot the join condition
-- 20000000# Python: count SQLite VM steps as a proxy for work (real output)
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INT, status TEXT, total REAL);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM n WHERE i<20000)
INSERT INTO orders SELECT i, (i*7)%1000+1, 'x', (i%500)+0.5 FROM n;
""")
def steps(sql):
c = [0]
def h(): c[0] += 1; return 0
db.set_progress_handler(h, 1)
rows = db.execute(sql).fetchall()
db.set_progress_handler(None, 0)
return len(rows), c[0] # (rows returned, VM steps)
q = "SELECT * FROM orders WHERE customer_id = 42"
print("no index :", steps(q)) # no index : (20, 60128)
db.execute("CREATE INDEX idx_c ON orders(customer_id)")
print("index :", steps(q)) # index : (20, 189)
print("OFFSET :", steps("SELECT id,total FROM orders ORDER BY id LIMIT 50 OFFSET 15000"))
# OFFSET : (50, 30360)
print("keyset :", steps("SELECT id,total FROM orders WHERE id > 15000 ORDER BY id LIMIT 50"))
# keyset : (50, 310)
-- PostgreSQL (not run here; output depends on your data, so none is shown)
-- EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
-- BEGIN; EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'refunded'; ROLLBACK;
-- ANALYZE orders; -- refresh statistics, then re-run EXPLAIN and compareQ: How do you debug a slow SQL query?
A: First prove the time is spent in the database, not in an N+1 loop, a connection-pool wait, a lock wait, or transferring too many rows. Then capture the exact query with real parameter values, run EXPLAIN (ANALYZE, BUFFERS) on production-sized data, find the node with the largest time or the largest estimated-versus-actual row gap, fix that one cause (index, rewrite, statistics, fewer rows or columns), and re-measure. The trade-off of any fix is write cost and complexity: a new index speeds this query but slows every insert and update on the table.
Q: What is EXPLAIN?
A: It prints the execution plan the optimizer chose: the tree of scans, joins, sorts and aggregates, with estimated row counts and costs. EXPLAIN ANALYZE additionally executes the query and reports actual rows, time and loops per node, so you can see where the optimizer's assumptions were wrong. Because it really executes, run it inside a rolled-back transaction for UPDATE/DELETE, and remember that costs are unitless estimates, not milliseconds.
Q: Sequential scan vs index scan?
A: A sequential scan reads every page of the table in order; an index scan seeks through a B-tree and then fetches only the matching rows, usually with random access. Index scans win when the query returns a small fraction of the table; when it returns a large fraction, following millions of random pointers costs more than one ordered pass, so a sequential scan generally wins. The optimizer decides from statistics, and PostgreSQL also has a bitmap scan that sits between the two.
Q: What are common causes of slow JOINs?
A: An unindexed join key on the probed table (nested loop then rescans it per outer row), mismatched key types that force casts and disable the index, a missing join condition that produces a cartesian product, stale statistics that produce a wrong join order or algorithm, hash or sort operations spilling to disk, and filters applied too late. Diagnose with the plan: look for a nested loop with a huge actual outer row count, or a hash/sort node that reports disk usage.
Q: How would you optimize a query returning millions of rows?
A: Question the requirement first: aggregate in the database, filter earlier, or pre-compute a summary if the caller does not need raw rows. If raw rows are needed, select only the required columns (which may allow an index-only scan), paginate with keyset pagination instead of deep OFFSET, and stream in batches with a cursor rather than loading everything into application memory. The trade-off is that keyset pagination gives up "jump to page N", and pre-aggregation adds staleness and maintenance.
Q: Why can SELECT * be problematic?
A: It prevents covering/index-only access because the index rarely contains every column, it moves wide columns (large text, JSON, blobs) across the network even when unused, and it makes code fragile because adding or reordering a column changes what callers receive (and duplicate column names appear after joins). For ad hoc exploration or tiny tables it is harmless; in production paths list the columns you need.
Follow-up: The estimated rows are 1 but the actual rows are 500,000. What do you do?
A: That gap means the optimizer's statistics are wrong for this predicate, so it likely chose a plan (such as a nested loop) suited to a tiny result. Run ANALYZE to refresh statistics; if it persists, suspect correlated columns (PostgreSQL extended statistics with CREATE STATISTICS), heavy value skew (raise the statistics target), or a parameterized query whose generic plan is wrong for skewed values. Only after the estimates are trustworthy is it worth adding indexes or rewriting.
Follow-up: The query is fast in staging but slow in production. Why?
A: Generally data volume and distribution differ, so the optimizer picks a different plan; production may have stale statistics, bloat, or skewed parameter values that staging lacks. Cache state also differs (a cold buffer pool reads from disk), and production adds concurrency: lock waits, connection-pool exhaustion, and competing workloads. Reproduce with a production-sized copy or captured statistics and compare plans, not just timings.
Follow-up: When would a hash join beat a nested loop, and when would it be wrong?
A: A hash join builds an in-memory table on the smaller input and streams the larger one past it, so it works well for large, unsorted inputs joined on equality with no useful index; a nested loop is better when the outer side is small and the inner join key is indexed. A hash join is the wrong tool for non-equality conditions, and if the build side exceeds available memory it spills to disk in batches and slows down sharply, so memory settings matter.
Query plans are where a system's data-access design meets reality. An endpoint that lists a customer's recent orders is really a query with an equality filter, a sort and a limit, which is exactly the shape a composite index on (customer_id, created_at) serves without a separate sort step; designing the API around keyset pagination rather than page numbers keeps that endpoint fast as the table grows. Reporting and analytics queries that scan large fractions of a table belong on a read replica or an analytical store, or behind pre-aggregated tables, so they do not contend with the transactional workload, and the sequential-versus-index reasoning above is precisely why forcing an index onto those queries does not help. For AI and RAG back ends the same discipline applies to metadata filters that accompany a vector search: an unindexed filter column or a non-sargable predicate can dominate latency, so check the plan of the whole query, not just the similarity operator. In every case, wire slow-query logging (or pg_stat_statements) into monitoring so regressions are found from data rather than from user complaints.
Optimizing a query is always a trade: an index buys read speed at the cost of write throughput, storage and maintenance, and an index that helps one query can be ignored, or hurt, for another. Rewriting for sargability or denormalizing a column improves the hot path but adds complexity or staleness. Trusting the optimizer is usually right, because it sees statistics you do not, but it is only as good as those statistics, so the failure mode is stale or misleading stats rather than a "dumb" planner. The wrong choices are optimizing by folklore without reading the plan, adding indexes to a table that is write-heavy and rarely queried, forcing plan hints that go stale when the data changes, and tuning a query on dev-sized data. When a query is inherently reading a large share of the table, the honest answer is often not another index but changing what is asked of the database: aggregate less data, pre-compute, or move the workload elsewhere.