Query tuning

Why it matters

Indexes fix many slow queries, but not all. Some queries are written so the database cannot use an index, or so that it repeats work for every row. Query tuning is the craft of finding the expensive part of a query and rewriting it to return the same answer with less work. As a DBA you will often do this together with the developer who owns the code.

A repeatable process

1. Find the queries that matter. Sort by total time, not just the slowest single run: a 20 ms query called a million times a day costs more than a 5 s report run once. In PostgreSQL, pg_stat_statements gives you this list (covered in the Monitoring lesson); MySQL has the slow query log and the Performance Schema.
2. Read the plan. EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, EXPLAIN ANALYZE in MySQL, EXPLAIN QUERY PLAN in SQLite. Look for full scans on large tables, sorts that spill to disk, nested loops over many rows, and big gaps between estimated and actual row counts.
3. Form one hypothesis and change one thing. An index, a rewrite, fresh statistics.
4. Measure again with realistic data and confirm the results are identical.

Rewrites that often help

Make filters sargable. A predicate is sargable (Search ARGument ABLE) when the database can use an index for it. Wrapping the column in a function usually breaks that:

-- Not sargable: the function runs on every row's ordered_at
WHERE strftime('%Y', ordered_at) = '2025'

-- Sargable: a plain range on the column
WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'

The same applies to WHERE price * 1.2 > 100 (move the math to the constant side) and WHERE lower(email) = ? (use an expression index or store a normalised column).

Replace per-row subqueries with joins and grouping. A correlated subquery in the SELECT list can run once per outer row:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o
         WHERE o.customer_id = c.id AND o.status = 'shipped') AS shipped
FROM customers c;

Planners are good at some of these, but a join with GROUP BY states the intent once and is easy to index:

SELECT c.name, COUNT(*) AS shipped
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'
GROUP BY c.id, c.name;

Careful: the inner join drops customers with zero matches. Use LEFT JOIN plus COUNT(o.id) if you need them, and filter in the ON clause, not in WHERE.

Return less. SELECT * drags every column over the network and prevents index-only scans. Select the columns you need, and aggregate in the database rather than in application loops.

Kill N+1 queries. An application that runs one query for a list and then one more query per item ("N+1") makes hundreds of round trips. Fetch the related rows in one query with a join or WHERE id IN (...).

Paginate with keys, not big offsets. LIMIT 50 OFFSET 100000 still reads and discards 100,000 rows. Keyset pagination (WHERE id > :last_seen_id ORDER BY id LIMIT 50) stays fast on every page.

Keep statistics fresh. The planner chooses plans from table statistics. After a bulk load, run ANALYZE (PostgreSQL, SQLite) or ANALYZE TABLE (MySQL); stale statistics are a classic cause of a sudden bad plan.

Worked example

A dashboard query counts each customer's 2025 shipped orders:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o
         WHERE o.customer_id = c.id AND o.status = 'shipped'
           AND strftime('%Y', o.ordered_at) = '2025') AS orders_2025
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.id AND o.status = 'shipped'
          AND strftime('%Y', o.ordered_at) = '2025') > 0
ORDER BY orders_2025 DESC, c.name;

Two problems: the subquery appears twice (and runs per customer), and the year filter is not sargable. The rewrite:

SELECT c.name, COUNT(*) AS orders_2025
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'shipped'
  AND o.ordered_at >= '2025-01-01' AND o.ordered_at < '2026-01-01'
GROUP BY c.id, c.name
ORDER BY orders_2025 DESC, c.name;

The inner join naturally drops customers with no 2025 orders, replacing the > 0 filter. Same rows, one pass, and an index on (status, ordered_at) or (customer_id, status, ordered_at) can now be used.

Common mistakes

• Tuning the query that feels slow instead of the one that costs the most in total.
• Changing several things at once, so you cannot tell what helped.
• Testing on a laptop with 100 rows; plans change with data size.
• Forgetting to compare results: a "faster" query that returns different rows is a bug.
• Adding query hints or forcing plans before fixing statistics and indexes.

Quick recap

• Measure, read the plan, change one thing, measure again.
• Keep filters sargable, prefer joins and grouping to repeated per-row subqueries, select only what you need.
• Watch for N+1 patterns, deep offsets and stale statistics.

Try it

In the Do tab, rewrite the dashboard query above so it returns exactly the same rows with sargable filters and a single join.