How Do You Tune a Slow Query?

Five steps, and the order matters because each one tells you where to look next.

First, measure. Get the actual execution time and the actual row counts, not the estimate. EXPLAIN tells you what the engine intends to do; EXPLAIN ANALYZE, in PostgreSQL and MySQL, runs the query and reports what actually happened. The gap between estimated and actual rows is the single most informative signal you will get, because it means the planner is working from wrong information and any plan it picks is suspect.

Second, read the plan and find the expensive node. Look for a full table scan where you expected an index seek, a sort of far more rows than the query returns, or a nested loop executing far more times than the row count justifies. In SQLite the equivalent is EXPLAIN QUERY PLAN, which reports SCAN and SEARCH operations and which index was used.

Third, identify which part of the query is causing the problem. Sometimes it is the predicate that is not using an index, sometimes it is the join order, sometimes it is the sheer number of rows the query has to aggregate. Fix the biggest thing first — an index on a column that narrows the scan by a factor of a million is worth more than every other optimisation combined.

Fourth, make the smallest change that addresses it. Usually that is adding an index, but sometimes it is rewriting the query: replacing a correlated subquery with a join, pulling an aggregate into a derived table so it is computed once, or splitting a query in two. Add the index, re-measure, and confirm the plan changed.

Fifth, check the whole query again afterwards, because an index on one part of a query can expose a new bottleneck in another part. And check the write side — if the table is write-heavy, an index that makes the read fast but the writes slow may not be a net win.

If the plan already looks good and the query is still slow, the problem is not the plan. It is that the query is asking for too much: it returns far more rows than the application needs, it aggregates data it never uses, or it does the same work repeatedly. At that point the fix is to change what the query computes, not how it is executed.

Here is the same thing as runnable code:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (customer_id, amount, status) VALUES
  (1, 120.00, 'shipped'), (1, 340.50, 'pending'), (2,  75.25, 'shipped'),
  (2, 610.00, 'pending'), (3,  40.00, 'shipped'), (1,  90.00, 'pending');

-- The plan before any index exists.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid';

CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

-- The plan after.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid';

SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid' ORDER BY id;

The same query, the same data, two plans — and the difference between them is one index.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

INSERT INTO orders (customer_id, amount, status) VALUES
  (1, 120.00, 'shipped'), (1, 340.50, 'pending'), (2,  75.25, 'shipped'),
  (2, 610.00, 'pending'), (3,  40.00, 'shipped'), (1,  90.00, 'pending');

-- Before: the engine reads every row and tests the predicate.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid';

CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

-- After: the engine seeks straight to the one matching row.
EXPLAIN QUERY PLAN
SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid';

SELECT id, amount FROM orders WHERE customer_id = 1 AND status = 'paid' ORDER BY id;

The first plan says SCAN orders. The second says SEARCH orders USING COVERING INDEX idx_orders_cust_status (customer_id=? AND status=?). The final query returns one row: order id 1 with amount 120.00 — the only order that is both customer 1's and paid.

What changed is the access path. Before, the engine had no way to narrow the rows, so it read all six rows and evaluated the two predicates on each one. After, the composite index is sorted by customer_id and then by status, so the engine can seek to the entry for customer 1 and paid directly. The query became a logarithmic lookup instead of a linear scan, and because the index happens to contain both customer_id and amount, the table was never touched at all.

Two things to be careful about when you read this. The speed-up scales with the size of the table — six rows to six million rows is the difference between a plan that is nice to have and one that is the difference between a timeout and a fast response. And the index only helps this query shape. A query that filters on customer_id but sorts by amount DESC would still need a sort, and a query that selects a column outside the index would lose the covering property.

The other half of the tuning story is that indexes are not the only tool. If the query above were selecting hundreds of thousands of rows, the right answer might be to not select them — to aggregate in the database instead of transferring rows, or to add a summary table. A query can be perfectly well indexed and still be the wrong shape for the work it is doing.

In rough order of how often they turn out to be the culprit.

A missing index on the filtered or joined column. The single most common cause. The engine scans the whole table where it could have sought. The fix is usually one CREATE INDEX, and the plan will tell you immediately whether it worked.

A function wrapped around an indexed column. WHERE strftime('%Y', order_date) = '2026' cannot use an index on order_date, because the engine has to compute the function for every row before it can compare. A bounded range — order_date >= '2026-01-01' AND order_date < '2027-01-01' — uses the index properly. The general rule is that the indexed column must appear bare on one side of a comparison.

Too much data. The query is correct but is asking for far more than it needs — a SELECT * where two columns would do, no LIMIT on a query that returns millions of rows, or a report that aggregates the entire table to produce one number. The fix is to return less, and to aggregate in the database rather than transferring rows to be aggregated in application code.

Correlated subqueries in a hot path. A subquery that references the outer row runs once per outer row, so a million outer rows means a million inner executions. The fix is to pre-aggregate in a derived table and join.

Join fan-out. Joining a one-to-many relationship and then aggregating counts the many-side rows repeatedly, which both slows the query and produces wrong numbers. Aggregating before the join fixes both.

Stale statistics. The planner's estimate of how many rows a table has is out of date, so it picks the wrong join order or the wrong strategy. Updating the statistics — ANALYZE on PostgreSQL, a statistics refresh on MySQL — can fix a query that no amount of rewriting will help.

Missing indexes on the foreign key side. PostgreSQL does not create an index on a referencing column, so a join or a cascade can be doing a sequential scan where a lookup should be.