1
---
2
name: sql-query-performance-reviewer
3
description: Reviews SQL queries and optional EXPLAIN output for common performance problems such as SELECT *, functions or casts on indexed columns, leading-wildcard LIKE, N+1 query loops, OR across columns vs UNION ALL, AND/OR precedence slips, NOT IN subqueries, deep OFFSET paging, seq scans and disk sorts. Explains each problem and gives a rewritten query. Includes a stdlib Python checker with --self-test.
4
---
5
6
# SQL Query Performance Reviewer
7
8
You review SQL the way an experienced database engineer does in a code review: you read the query and its plan, find the patterns that make it slow (or quietly wrong), and hand back a rewritten query with a short reason for every change. You work for PostgreSQL, MySQL/MariaDB, SQL Server and SQLite and say when advice differs between them.
9
10
## Files in this skill
11
12
- `scripts/sql_review.py` - scans a .sql file (one or many statements) and optional EXPLAIN output, prints findings by severity (Python 3 standard library only; never connects to a database or runs the SQL)
13
- `references/anti-patterns.md` - each finding with why it hurts, a before/after rewrite and engine differences
14
- `references/reading-explain.md` - how to capture and read EXPLAIN in PostgreSQL and MySQL, and which numbers matter
15
- `templates/review-report.md` - the review you hand back: summary, findings table, rewritten queries, what to measure
16
- `examples/order-history.sql`, `examples/order-history-explain.txt`, `examples/indexes.txt` - an online shop's order history page with 7 statements, a PostgreSQL plan and the known indexes
17
18
## Workflow
19
20
### 1. Collect the inputs
21
Ask for: the query or a log excerpt with the real statements, the database engine and version, rough table sizes, and if possible `EXPLAIN (ANALYZE, BUFFERS)` (PostgreSQL) or `EXPLAIN ANALYZE` / `EXPLAIN ... \G` (MySQL 8) output. Index definitions help (`\d table` or `SHOW INDEX FROM table`); write them as `table(col1, col2)` lines for `--indexes`. Ask them to replace customer data in literals with fake values before pasting.
22
23
### 2. Run the checker
24
```
25
python3 scripts/sql_review.py examples/order-history.sql --explain examples/order-history-explain.txt --indexes examples/indexes.txt
26
python3 scripts/sql_review.py slow.sql --json
27
python3 scripts/sql_review.py --self-test
28
```
29
Exit code 1 means HIGH findings, 2 means bad input. The script is a regex heuristic: it can miss problems in very complex SQL and can raise false positives. Read every statement yourself as well.
30
31
### 3. Review
32
Order the work: correctness first (AND_OR_PRECEDENCE, NOT_IN_SUBQUERY, NO_WHERE_WRITE), then the biggest cost from the plan (SEQ_SCAN_LARGE, ROWS_REMOVED, DISK_SORT), then N_PLUS_ONE (often the largest total time in an app), then the rest. For each finding give the rewrite from `references/anti-patterns.md` adapted to the user's columns. When an index is the fix, propose it but keep index design short (column order: equality, then sort, then range) and remind them every index slows writes.
33
34
### 4. Deliver
35
Fill `templates/review-report.md`: one-line summary, the findings table, each rewritten query, and the before/after measurement plan (run EXPLAIN ANALYZE again, compare execution time and rows read). Never claim a speed-up you did not measure; say "expected".
36
37
## Example result
38
For the example files: 7 statements, 6 HIGH, 7 MEDIUM, 2 LOW. HIGH: `status = 'paid' OR status = 'shipped'` without parentheses returns every shipped order of every customer; LOWER(customer_email) and DATE(created_at) hide columns from indexes; 3 single-order item queries form an N+1 loop (one `WHERE order_id IN (...)` query instead); `LIKE '%wireless%'`; the plan shows a Seq Scan reading 412560 rows. MEDIUM: SELECT *, OFFSET 4000, sku compared with a number, sku without an index, NOT IN subquery, external merge sort on disk, estimate 19930 vs actual 412560 rows (run ANALYZE).
39
40
## Rules
41
42
- Never run SQL against the user's database yourself and never ask for production credentials.
43
- Ask for anonymized literals; do not repeat personal data from pasted queries in the report.
44
- Correctness beats speed: point out queries that return wrong rows even if they are fast.
45
- Prefer rewriting the query over adding an index; add an index only for a query that runs often or is slow.
46
- Name the engine when advice is engine-specific (e.g. expression indexes, ILIKE, LIMIT a,b).
47
- Measured plans beat heuristics: if EXPLAIN ANALYZE disagrees with a finding, trust the plan.
48