---
name: optimise-slow-sql-queries
description: Speeds up slow SQL queries in Postgres or MySQL - find the slow ones, read the EXPLAIN ANALYZE plan, add the right index, rewrite N+1 loops and wide selects, and page with cursors instead of big offsets. Use when a database query is slow, a page waits on the database, or the user mentions indexes, EXPLAIN or N+1 queries.
license: MIT
metadata:
  category: backend
  tags: "sql,database,performance,postgres"
  author: johnny-bravo
  version: "1.0"
  updated: "2026-11-02"
  status: published
  aliases: "slow query, explain analyze, add index, n+1"
  source_url: "https://github.com/wshobson/agents/tree/7e2fb559cf4b824b27f1fec49fe88f7511066d26/plugins/developer-essentials/skills/sql-optimization-patterns"
  source_license: MIT
  copyright: "Copyright (c) 2024 Seth Hobson"
  original_author: "Seth Hobson (wshobson/agents, sql-optimization-patterns)"
  modified: "Adapted by TheSkillVault: condensed into a measure-fix-measure loop for app builders with original examples; long pattern sections dropped; added production safety notes, gotchas and done-when."
  imported_on: "2026-11-02"
---

# Optimise slow SQL queries

Measure first. Most slow queries are fixed by one missing index or one query run inside a loop.

## When to use
- A page or API call is slow and the database is the bottleneck.
- You see the same query run hundreds of times per request.
- Someone suggested "add an index" and you are not sure where.

## When not to use
- You are designing tables from scratch. Use design-supabase-postgres-schema first.

## Steps
1. Find the slow queries: `pg_stat_statements` in Postgres, the slow query log in MySQL, or your ORM's query log.
2. Copy one slow query and run `EXPLAIN ANALYZE` on it against a copy of real data.
3. Look for "Seq Scan" on a big table, a large gap between estimated and actual rows, or a costly sort.
4. Add an index on the columns in `WHERE`, `JOIN` and `ORDER BY`. Put the most selective column first in a composite index.
5. In production Postgres, use `CREATE INDEX CONCURRENTLY` so writes are not blocked.
6. Fix N+1 loops: load related rows in one query with a join or `WHERE id = ANY(...)`, or use your ORM's include option.
7. Select only the columns you need instead of `SELECT *`.
8. Replace large `OFFSET` paging with cursor paging: `WHERE id > :last ORDER BY id LIMIT 20`.
9. Run `EXPLAIN ANALYZE` again and compare the timing. Keep the change only if it helped.

```sql
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
CREATE INDEX CONCURRENTLY orders_customer_created_idx ON orders (customer_id, created_at DESC);
```

## Gotchas
- `EXPLAIN ANALYZE` runs the query. Wrap writes in a transaction you roll back.
- Every index slows writes and uses space. Remove ones nothing uses.
- Functions on a column (`WHERE lower(email) = ...`) skip a plain index. Index the expression instead.
- Test on a copy of production data, not on production, and back up before big schema changes.

## Portability
Works with Postgres, MySQL, SQLite and hosted options such as Supabase or Neon. If you use an ORM like Prisma or Drizzle, log the SQL it generates and tune that; the steps are the same.

## Example prompt
"My orders page takes 4 seconds. Find the slow query, explain the plan and add the right index."

## Done when
- The slow query has a before-and-after `EXPLAIN ANALYZE` time.
- New indexes are in a migration file, not added by hand.
- No N+1 loop remains on the fixed page.

---
Adapted from "sql-optimization-patterns" in wshobson/agents (MIT, Copyright (c) 2024 Seth Hobson). See LICENSE.txt.