Query optimisation

This is the exact file agents read, including the credit header and licence notice. Save it as query-optimization/SKILL.md.

---
name: query-optimization
description: >
  Diagnose and fix slow SQL queries by reading the execution plan and fixing the dominant bottleneck, across PostgreSQL, MySQL and MariaDB, SQLite and SQL Server: EXPLAIN and EXPLAIN ANALYZE, estimated-versus-actual rows, scan and join choices, sargable rewrites, N+1 and over-fetching. Use it when someone says "this query is slow", "speed up this SQL", "why is this query slow", "the query times out", "database CPU is high", "read this EXPLAIN", "what does this query plan mean", "it is doing a full table scan", or pastes a plan and asks what to do. Do NOT use it to design the index column order once an index is clearly the fix (see database-indexing), for keyset pagination design (see efficient-pagination), for join result logic (see sql-joins), or for window frame tuning (see window-functions).
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/query-optimization/.
---

# Query optimisation

## When to use this, and when not to

Use this skill to find out why a SQL query is slow and fix the one thing that is actually slow, by reading its plan, spotting the costly operator, and rewriting the query, refreshing statistics or routing to an index. The triggers are a slow query, a query that times out, high database CPU, a plan you need to read, or a full table scan you did not expect.

Do not use it to design the column order of an index once you have confirmed an index is the fix: run `database-indexing` for that. Do not use it for keyset pagination design (`efficient-pagination`), join result logic (`sql-joins`) or window frames (`window-functions`). The defining feature of this skill is diagnosis: find the dominant bottleneck first, then apply the smallest change that moves it.

The common mistake this skill guards against is optimising before diagnosing: adding an index, a cache or a rewrite to a query whose real problem is somewhere else. Read the plan first.

## Inputs the agent must have before starting

1. The SQL engine and version. Plan output and the fixes differ (see the appendix).
2. The exact query, with a representative parameter value, not a toy one. A query that is fast on a developer's 1,000 rows can fail at production cardinality.
3. The table sizes in rows, how often the query runs, and the concurrent load.
4. The current indexes on the tables involved.
5. Whether you can run `EXPLAIN ANALYZE` safely: this is production or a copy, and the statement is a `SELECT` or a data-modifying statement.
6. The latency target, so you know when to stop.

If you cannot run the plan yourself, ask for the `EXPLAIN` (and ideally `EXPLAIN ANALYZE`) output. Diagnosing from the query text alone is a guess. If the statement writes data, do not run `EXPLAIN ANALYZE` until you have read the safety section.

## The workflow, step by step

### 1. Reproduce and baseline

Capture the exact SQL and a representative parameter, run it once, and record the time and the number of rows returned. This is the number every later change is measured against. Do not optimise a query you have not timed.

### 2. Classify the bottleneck before changing anything

Decide which layer is slow, because the fix differs:

- High database execution time: a plan, index, data or statistics problem. Continue below.
- Low database time but high end-to-end latency: the application, serialisation or network, not the SQL.
- Many queries per request: an N+1 access pattern (step 7), not one slow query.
- A fast query that transfers a huge amount of data: a projection or pagination problem (step 7).
- Requests waiting on a connection: a pool or concurrency problem, not the query.

Fix the dominant layer. Tuning the SQL will not help a request that is really waiting on a connection pool.

### 3. Get the plan

Run `EXPLAIN` to see the planner's chosen plan and its estimates. Then run `EXPLAIN ANALYZE` to get the actual row counts and run times, but only when it is safe: it executes the query. On PostgreSQL add `BUFFERS`. For an `INSERT`, `UPDATE`, `DELETE` or `MERGE`, wrap it so it does not change data:

```sql
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;
ROLLBACK;
```

### 4. Read the plan (the signals that matter)

The single most useful signal is the gap between estimated rows and actual rows. If the estimate is off by roughly ten times or more, the planner is working from bad information and will often pick the wrong plan; fix that first (step 5). Then read the operators:

- PostgreSQL: each node shows `cost=startup..total` in arbitrary units (not milliseconds), and under `ANALYZE`, `actual time` in milliseconds and `loops`. The per-node `actual time` and `rows` are per loop, so multiply by `loops` for the total. Watch for a `Seq Scan` on a large table with a selective filter and a high `Rows Removed by Filter`, or a `Sort` that dominates the time.
- MySQL: the `type` column ranks the access method. `ALL` is a full table scan and is the usual thing to fix on a large table; `ref`, `range`, `eq_ref` and `const` are progressively better. Read `key` (the index used, `NULL` if none), `rows` (estimated rows examined), and `Extra`: `Using filesort` means a sort with no index, `Using temporary` means a temp table (often from `GROUP BY` or `DISTINCT`), `Using index` means a covering index.
- SQLite: `EXPLAIN QUERY PLAN` shows `SCAN` for a full scan and `SEARCH ... USING INDEX` when an index narrows it; `USE TEMP B-TREE FOR ORDER BY` means the sort was not served by an index.

### 5. Fix cardinality errors before rewriting

If estimated and actual rows diverge badly, refresh the statistics with `ANALYZE` (PostgreSQL and SQLite) or `ANALYZE TABLE` (MySQL) and re-read the plan. For columns that are correlated (city and country, say), single-column statistics mislead the planner; on PostgreSQL create extended statistics. Do not treat "query slow, run `ANALYZE`" as a reflex: confirm the estimate is actually wrong before you act on it.

### 6. Make the predicates sargable

A predicate that wraps the column in a function, a cast or arithmetic, or that starts a `LIKE` with a wildcard, stops the engine using an index and forces a scan. Rewrite so the column stands bare on one side:

- `WHERE EXTRACT(YEAR FROM created_at) = 2026` becomes `WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'`.
- `WHERE date_trunc('day', created_at) = '2026-07-16'` becomes a half-open range on `created_at` the same way.
- `WHERE price * 1.2 > :limit` becomes `WHERE price > :limit / 1.2`.
- `WHERE CAST(id AS text) LIKE :p` and `WHERE phone::bigint = :n`: cast the constant, not the column.
- `WHERE lower(email) = :e`: store the value already normalised, add an expression index on `lower(email)`, or use a case-insensitive type or collation.
- `WHERE name LIKE '%phones'` (leading wildcard): a plain B-tree cannot help; use a trigram index, and note it changes which rows match.

These matter once the table is large. On a small table the engine scans anyway, and that is correct.

### 7. Cut the work the query does

- Select only the columns you need. `SELECT *` carries large text and `jsonb` columns the application never uses and inflates transfer; name the columns.
- Bound the result with a `LIMIT`. An unbounded production query is a latent outage.
- Remove an N+1 pattern (one query, then one more per row) by batching or joining, but do not replace it with a join that multiplies rows and transfers far more than the screen needs.
- Filter as early as possible, before joins and aggregation, so fewer rows flow through the plan.

### 8. Fix the dominant operator

Change the one node that dominates the time, then re-measure:

- A `Sort` that dominates, under an `ORDER BY`: an index that matches the ordering lets the engine skip the sort. Hand the column order to `database-indexing`.
- A nested loop with a huge `loops` count and an unindexed inner side: index the inner join key, or let the planner choose a hash or merge join by giving it good statistics. Do not force a join type before you understand why it chose this one.
- A `Seq Scan` on a large table with a selective predicate: an index is likely the fix. But a `Seq Scan` that returns most of the table is correct, so do not force an index onto it.

### 9. Decide whether an index is really the fix

An index is one fix among several, and often not the first. A rewrite that removes work, a projection that cuts transfer, or fresh statistics can beat a new index and help more queries. Once you have confirmed the bottleneck really is index access, hand the design to `database-indexing` for the column order, and weigh the write cost there.

### 10. Re-measure and verify

Re-run `EXPLAIN ANALYZE` and confirm the plan actually changed and the time dropped against the baseline. Test a second, different parameter value, because a plan that is fast for one distribution can be slow for another. Confirm the results are identical to before unless you changed behaviour on purpose, and check the write impact of any index you added.

## Worked example, end to end

A dashboard endpoint runs this on a 20 million row `events` table and takes about 900 ms:

```sql
SELECT *
FROM events
WHERE account_id = :acct
  AND date(created_at) = :day
ORDER BY created_at DESC;
```

`EXPLAIN (ANALYZE, BUFFERS)` shows a sequential scan that reads the whole table, discards almost all of it, then sorts:

```
Sort  (actual time=905.2..905.4 rows=180)
  Sort Key: created_at DESC
  ->  Seq Scan on events  (actual time=0.1..880.6 rows=180 loops=1)
        Filter: (account_id = :acct AND date(created_at) = :day)
        Rows Removed by Filter: 19999820
```

Three problems. The `date(created_at)` wraps the column, so no index on `created_at` can be used (step 6). `SELECT *` pulls a large `payload` `jsonb` column the dashboard does not show (step 7). And there is no index for this access path (step 8).

Fix the query first: make the date filter a half-open range and select only the needed columns.

```sql
SELECT id, account_id, created_at, event_type
FROM events
WHERE account_id = :acct
  AND created_at >= :day
  AND created_at <  :day + INTERVAL '1 day'
ORDER BY created_at DESC;
```

Then, having confirmed the bottleneck is index access, add the index (column order from `database-indexing`): equality column first, then the range and sort column.

```sql
CREATE INDEX idx_events_acct_created
  ON events (account_id, created_at DESC);
```

The plan is now an index scan that reads only this account's rows for the day, already in order, with no sort, and runs in a few milliseconds:

```
Index Scan using idx_events_acct_created on events  (actual time=0.05..0.6 rows=180 loops=1)
  Index Cond: (account_id = :acct AND created_at >= :day AND created_at < :day + '1 day')
```

Re-measured against the 900 ms baseline, verified with a second account, results identical.

## Templates you can use verbatim

```sql
-- PostgreSQL: estimate, then actual with I/O
EXPLAIN SELECT ...;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Safe analysis of a data-modifying statement
BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;
-- Refresh statistics when estimates are wrong
ANALYZE my_table;                 -- PostgreSQL / SQLite
ANALYZE TABLE my_table;           -- MySQL

-- MySQL: plan, actual timings (8.0.18+), and JSON detail
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;

-- SQLite: which indexes the plan uses
EXPLAIN QUERY PLAN SELECT ...;

-- Sargable rewrites (keep the column bare on one side)
-- year filter:   EXTRACT(YEAR FROM d) = 2026  ->  d >= '2026-01-01' AND d < '2027-01-01'
-- day filter:    date(d) = :day               ->  d >= :day AND d < :day + 1 day
-- arithmetic:    price * 1.2 > :lim           ->  price > :lim / 1.2
-- cast:          CAST(col AS text) = :s        ->  col = CAST(:s AS col_type)
-- case-insensitive: lower(email) = :e          ->  expression index on lower(email), or citext
```

## Failure modes and anti-patterns

1. **Optimising before reading the plan.** Spot it when someone adds an index, a cache or a rewrite with no `EXPLAIN` to justify it. Fix: get the plan first and let the dominant operator and the estimated-versus-actual gap tell you what to change.
2. **Treating a sequential scan as a bug.** Spot it when an index is forced onto a query that returns most of the table. Fix: a scan that reads most rows is cheaper than an index and is the right plan; only index a selective predicate on a large table.
3. **Reflex fixes: "slow, so run ANALYZE" or "slow, so add an index".** Spot it when the same move is applied to every slow query. Fix: confirm the estimate is actually wrong before `ANALYZE`, and confirm the bottleneck is index access before adding an index; a rewrite often helps more and more broadly.
4. **Running EXPLAIN ANALYZE on a mutating statement without protection.** Spot it when `EXPLAIN ANALYZE` is run on an `UPDATE` or `DELETE` on production: it executes and changes data. Fix: wrap it in `BEGIN; ... ROLLBACK;`, or analyse a read-only equivalent, and get confirmation before running heavy analysis on production.
5. **Optimising for one lucky parameter.** Spot it when a query is tuned with one value (a tiny account, an empty tenant) and regresses for the common case. Fix: test representative and skewed parameter values, because the best plan can differ by distribution.

## Quality bar

- The query was timed before and after, against the same representative parameter.
- The bottleneck layer was identified (database, application, transfer or connection) before any change.
- The plan was read, and the estimated-versus-actual row gap was checked and addressed if large.
- Any predicate that blocked an index was made sargable, and `SELECT *` was narrowed where it carried unused columns.
- The dominant operator was the thing changed, and the change was confirmed in a re-run plan with a lower time.
- A second parameter distribution was tested, results are unchanged, and the write impact of any new index was weighed.
- `EXPLAIN ANALYZE` on a data-modifying statement was wrapped in a transaction and rolled back, and heavy or production analysis was confirmed with a human.

## Data handling and safety

`EXPLAIN ANALYZE` executes the query, so running it on an `INSERT`, `UPDATE`, `DELETE` or `MERGE` changes data: wrap it in `BEGIN; ... ROLLBACK;` or analyse a read-only equivalent, and get explicit human confirmation before running heavy or mutating analysis against a shared or production database. Adding or dropping an index, if you reach that step, is a schema change: hand it to `database-indexing`, which treats the DDL as an irreversible action needing confirmation. Optimisation uses the query text, plan output and row-count estimates, not the data values, so refer to columns by name and never paste real personal, health or financial values into prompts or shared plan logs; replace literals with placeholders when sharing a query. Do not run optimisation experiments against production without a go-ahead.

## Appendix: engine-specific notes (verify before relying on these)

- **PostgreSQL**: costs are in arbitrary units, not time; `actual time` is in milliseconds and per loop, so multiply by `loops`. Use `EXPLAIN (ANALYZE, BUFFERS)` for I/O. `pg_stat_statements` finds the expensive queries across the workload, and `auto_explain` logs slow plans. Under `LIMIT`, estimated rows for a child node are shown as if it ran to completion, so actual can be far lower without an error. `BitmapAnd` and `BitmapOr` report their actual rows as zero. Extended statistics help with correlated columns.
- **MySQL and MariaDB**: `EXPLAIN ANALYZE` (8.0.18 and later) runs the statement and reports actual timings; `EXPLAIN FORMAT=JSON` gives cost and detail; the optimizer trace shows why a plan was chosen. `ANALYZE TABLE` refreshes the statistics the optimiser relies on. The `type` column and the `Extra` field are the quickest read.
- **SQLite**: `EXPLAIN QUERY PLAN` gives `SCAN` versus `SEARCH`, `USING INDEX` and `USING COVERING INDEX`, and `USE TEMP B-TREE` for an unindexed sort. Its output is for interactive debugging and the format can change between releases. `ANALYZE` populates the stat tables the planner uses.
- **SQL Server**: `SET STATISTICS IO, TIME ON` reports reads and time; compare the estimated and actual execution plans, and watch for a key lookup, a scan where a seek was expected, or a warning about a cardinality estimate.

## Sources

- PostgreSQL 18 documentation, "Using EXPLAIN": cost units, `actual time` and `loops` being per loop, the estimated-versus-actual row signal, scan and join node meanings, the `LIMIT` early-stop caveat, `BUFFERS`, and that `EXPLAIN ANALYZE` executes the query including side effects. Verified 30 October 2026.
- MySQL 8.4 Reference Manual, "Using EXPLAIN" and "How MySQL Uses Indexes": the `type` access-method ranking, `Extra` values, `EXPLAIN ANALYZE` from 8.0.18, `FORMAT=JSON`, and `ANALYZE TABLE` for statistics. Verified 30 October 2026.
- SQLite documentation, "EXPLAIN QUERY PLAN": `SCAN` versus `SEARCH`, index use and `USE TEMP B-TREE` for an unindexed sort. Verified 30 October 2026.
- Milan Jovanovic, "Why Postgres Ignores Your Index (Sargability)" and a PostgreSQL non-sargable query guide: concrete non-sargable patterns (function and cast on a column, year extraction, leading-wildcard `LIKE`) and their sargable rewrites, and that the fix is to transform the parameter, not the column. Verified 30 October 2026.
- Use The Index, Luke (Markus Winand): matching an index to filtering and ordering, and why wrapping a column defeats the index. Verified 30 October 2026.