sql-query-performance

Make a slow query fast

The database already knows why the query is slow; ask it. Read the plan, find the scan, loop or sort that dominates, change the query or add the index the plan needs, and measure again on data shaped like production. Most slow queries are an N+1, a missing or wrong-order index, a function or type cast on an indexed column, a wide SELECT *, an OFFSET walk, or stale statistics. Sources: PostgreSQL docs ("Using EXPLAIN", indexes, pg_stat_statements, explicit locking); MySQL 8 reference (EXPLAIN ANALYZE, optimisation, InnoDB locking); Markus Winand, Use The Index, Luke.

Steps

1. Capture the real SQL and its frequency. From the ORM query log, the slow-query log, pg_stat_statements (total and mean time) or performance_schema, with real parameter values. An ORM call is not a query. Count queries per request: fifty fast queries make one slow page. Call search_lore for hot tables and index conventions.
2. Build production-shaped data. Seed row counts, value distributions and skew comparable to production (generate_series or the repo's fixture factories, the test-fixtures-and-factories skill), then run ANALYZE. A plan on ten rows lies.
3. Read the plan. Postgres: EXPLAIN (ANALYZE, BUFFERS); MySQL 8.0.18+: EXPLAIN ANALYZE (or EXPLAIN FORMAT=TREE); SQLite: EXPLAIN QUERY PLAN. ANALYZE executes the statement: wrap INSERT/UPDATE/DELETE in BEGIN; … ROLLBACK;. Look for: sequential scans filtering most rows away, nested loops with large outer row counts, sorts or hashes spilling to disk, estimated vs actual rows off by 10× or more (stale or missing statistics; try ANALYZE or extended statistics), and heap fetches on an intended index-only scan (vacuum/visibility map).
4. Fix N+1 first. Load related rows in one query (join, IN (…)/= ANY($1) batch, the ORM's eager loading or a dataloader) and pin it with a query-count assertion.
5. Add the index the plan needs, and only that. Composite order: equality columns, then the range column, then the sort columns in the query's direction. Covering (INCLUDE) for hot read paths, partial for selective predicates. One index per access pattern; every index costs every write. Ship it through the migration path (the add-db-migration skill): CREATE INDEX CONCURRENTLY outside a transaction in Postgres (drop and retry if it leaves an INVALID index), online DDL in MySQL.
6. Make predicates sargable. No function or cast on the indexed column (WHERE created_at >= :day_start AND created_at < :next_day, not date(created_at) = :d), or index the expression; match parameter types to column types (MySQL string-vs-int comparisons defeat indexes); no leading-wildcard LIKE (use trigram or full-text); split OR across different columns into UNION ALL when the plan shows a scan; select only needed columns; replace OFFSET walks with keyset pagination (the pagination-and-filtering skill).
7. Protect the write path and other sessions. Long reports and bulk updates hold locks and snapshots: set statement_timeout/lock_timeout (or max_execution_time), batch large UPDATE/DELETE in keyed chunks with a commit per chunk, and move heavy reads to a replica, a materialised view refreshed on a schedule (the scheduled-jobs skill; REFRESH MATERIALIZED VIEW CONCURRENTLY needs a unique index), or a summary table maintained by the writes.
8. Measure again with the same data and tool: before/after plan and timing over repeated runs (discard the first cold run or report cold and warm separately). If the new index does not appear in the plan, drop it.