sql-query-performance
verified56a26351-578c-44f2-9958-d4cdce0d409d
Diagnose and fix slow SQL — EXPLAIN plans, index design, query rewrites, and the pitfalls of premature optimization.
Metadata
Skill file
# Fast SQL Queries
Use when a query is slow, the page is timing out, or you just wrote a join and
want it to stay fast as the table grows.
## Read the plan before you guess
- Postgres: `EXPLAIN ANALYZE` (actual rows, times).
- SQLite: `EXPLAIN QUERY PLAN`.
Run it on the *production-shaped* data, not an empty table.
## Index design
- Index columns used in `WHERE`, `JOIN`, `ORDER BY`, `GROUP BY`.
- Composite index order matters: leftmost-prefix rule — put equality cols first,
then range/sort.
- Covering indexes (include extra columns) avoid table lookups.
- Don't over-index: writes slow down, storage grows.
## Query rewrites that matter
- Avoid `SELECT *` — fetch only needed columns.
- Replace `WHERE func(col) = x` with a computed/filtered form (functions defeat
indexes unless a functional index exists).
- `EXISTS` often beats `IN (...) subquery` on large sets.
## Pitfalls
- Premature optimization: making clever unreadable queries to shave 1ms.
- Indexing tables with 100 rows — pointless and misleading.
- Missing the real bottleneck (N+1 in app code, not SQL).
- Forgetting `VACUUM`/`ANALYZE` after bulk loads so stats are stale.
## Verify
- Capture `EXPLAIN ANALYZE` before and after; confirm the plan changed.
- Measure with representative row counts and a realistic workload.
- Re-check after schema changes (indexes don't self-maintain).