A query-tuning workflow

A query-tuning workflow is a loop that turns “the database is slow” into one measured fix at a time: find the statements that cost the most in total, read the plan of the worst one, write down one hypothesis, change one thing, measure the same workload again, and keep the change only if the results are identical and the workload got faster. The discipline matters more than any single trick. Ranking by total cost, the time of one call times the number of calls, finds the right target, which is often a quick statement that runs a hundred thousand times a day rather than the slow report everyone complains about. One change at a time shows what helped, and comparing results catches the fix that is fast because it is wrong.

The loop, and where each engine keeps the evidence

One turn of the loop fits in a few statements. The query is a customer’s ten latest orders, on 20,000 orders:

-- 20,000 orders of 2,000 customers. The slow query: a customer's ten latest orders.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  placed_at    TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
INSERT INTO orders
SELECT i, 1 + (i * 37) % 2000,
       strftime('%Y-%m-%d %H:%M:%S', '2026-01-01', '+' || ((i * 7919) % 7776000) || ' seconds'),
       100 + (i * 7919) % 90000
FROM n;

-- Keep the result before the change, to compare it afterwards.
CREATE TEMP TABLE before AS
SELECT id, placed_at, amount_paise FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC, id DESC LIMIT 10;

-- 1. Read the plan: every order is read, then the matches are sorted.
EXPLAIN QUERY PLAN
SELECT id, placed_at, amount_paise FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC, id DESC LIMIT 10;

-- 2. One change, from the plan: an index that finds the customer's orders already in order.
CREATE INDEX orders_customer_newest ON orders (customer_id, placed_at, id);

-- 3. Read the plan again: a search, and no sort.
EXPLAIN QUERY PLAN
SELECT id, placed_at, amount_paise FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC, id DESC LIMIT 10;

-- 4. Check that the result did not change: no row in one result and not the other, in the same order.
CREATE TEMP TABLE after AS
SELECT id, placed_at, amount_paise FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC, id DESC LIMIT 10;
SELECT (SELECT count(*) FROM (SELECT * FROM before EXCEPT SELECT * FROM after)) AS only_before,
       (SELECT count(*) FROM (SELECT * FROM after EXCEPT SELECT * FROM before)) AS only_after,
       (SELECT group_concat(id, ' ') FROM before) = (SELECT group_concat(id, ' ') FROM after) AS same_order;

┌────┬────────┬─────────┬──────────────────────────────┐
│ id │ parent │ notused │            detail            │
├────┼────────┼─────────┼──────────────────────────────┤
│ 4  │ 0      │ 216     │ SCAN orders                  │
│ 19 │ 0      │ 0       │ USE TEMP B-TREE FOR ORDER BY │
└────┴────────┴─────────┴──────────────────────────────┘
┌────┬────────┬─────────┬──────────────────────────────────────────────────────────────────┐
│ id │ parent │ notused │                              detail                              │
├────┼────────┼─────────┼──────────────────────────────────────────────────────────────────┤
│ 5  │ 0      │ 63      │ SEARCH orders USING INDEX orders_customer_newest (customer_id=?) │
└────┴────────┴─────────┴──────────────────────────────────────────────────────────────────┘
┌─────────────┬────────────┬────────────┐
│ only_before │ only_after │ same_order │
├─────────────┼────────────┼────────────┤
│ 0           │ 0          │ 1          │
└─────────────┴────────────┴────────────┘

Before the change, SQLite reads every order and sorts the matches in a temporary B-tree. The plan suggests the hypothesis: an index on (customer_id, placed_at, id) would find the customer’s orders already in order. After it, the plan is a single SEARCH with no sort, and the comparison finds no row in one result that is missing from the other, in the same order. Write each turn down as you go: the plan before and after, the cost per call, and the check of the results are what make a change reviewable. The measuring step needs a tool that sees the whole workload, and each engine has its own:

• PostgreSQL’s pg_stat_statements extension keeps calls, rows, time and pages for each statement, with constants replaced by placeholders.
• PostgreSQL’s auto_explain module logs the plan of every statement slower than a threshold you choose.
• MySQL’s slow query log records statements that run longer than long_query_time, 10 seconds unless you lower it.
• MySQL’s Performance Schema groups statements into digests, each with its calls, rows examined and scans without an index.
• The SQLite shell’s .timer, .eqp and .expert commands time each statement, print its plan and suggest indexes.

Two problems need more than an index: N+1 queries, one query for a list and then one more for each of its rows, and plans that stay wrong, where a plan hint forces the engine’s hand as a last resort.

A tuning session on 100,000 orders, measured Pro: opens with a Pro, Premium or Ultimate pass