---
name: sql-query-review
description: Reviews SQL queries and ORM code for correctness (NULLs, duplicating joins, time zones) and performance (EXPLAIN, indexes, SELECT *, N+1, keyset pagination) with parameterized queries. Use when the user writes, debugs or optimizes SQL or a slow query.
---

# SQL Query Review

Review a query for wrong results first, slow results second, and injection risk always. Identify the database engine and version (Postgres, MySQL, SQLite, SQL Server) before advising; syntax and planner behavior differ. Read the schema (tables, keys, indexes) before judging a query.

## Workflow

1. Get the query, the schema (`\d table` in psql, `SHOW CREATE TABLE`, or migration files) and the approximate table sizes.
2. Check correctness using the list below. Wrong results outrank slow results.
3. Check safety (parameters, permissions).
4. Check performance with the real plan, not guesses.
5. Propose the rewritten query and any index; verify results are identical on sample data.

## Correctness checklist

### NULL semantics
- `NULL = NULL` and `x <> 5` are not true when x is NULL. Use `IS NULL`, `IS DISTINCT FROM` (Postgres) or `<=>` (MySQL).
- `NOT IN (subquery)` returns nothing if the subquery yields any NULL; use `NOT EXISTS`.
- `COUNT(col)` skips NULLs, `COUNT(*)` does not. `SUM` of no rows is NULL; wrap with `COALESCE` when a number is expected.
- A filter on the right table in a `WHERE` clause after a `LEFT JOIN` turns it into an inner join; move the condition into `ON` — except `WHERE right.id IS NULL`, which is a deliberate anti-join and must stay in `WHERE`.

### Joins that duplicate rows
- Joining a one-to-many table multiplies rows, then `SUM`/`COUNT` overcounts. Aggregate in a subquery or CTE first, then join, or use `COUNT(DISTINCT ...)` knowingly.
- Confirm the join keys are unique on the side you assume is "one"; check with `GROUP BY key HAVING COUNT(*) > 1`.
- Missing join condition produces a cartesian product.

### Grouping and ordering
- Every non-aggregated selected column must be in `GROUP BY` (or functionally dependent). Do not rely on MySQL's lax mode.
- `LIMIT` without a total `ORDER BY` returns an arbitrary set; add a unique tiebreaker (`ORDER BY created_at, id`).

### Time and types
- Store and compare timestamps in UTC; use `timestamptz` in Postgres. State the session time zone before using `DATE(ts)` or `date_trunc`.
- Range filters should be half-open: `ts >= '2026-01-01' AND ts < '2026-02-01'`, not `BETWEEN` with `23:59:59`.
- Avoid implicit casts and mixing types in comparisons; they change results and disable indexes.
- Money in integers or `numeric`, not floats. Case sensitivity and collation affect equality and uniqueness.

## Safety

- All user-provided values go through bind parameters (`$1`, `%s` with a driver, `?`), never string concatenation or f-strings. For dynamic identifiers (column or table names) use an allow-list.
- `LIKE` patterns from users: escape `%` and `_`.
- Application roles should be least-privilege; flag use of a superuser in application code.
- Do not run `UPDATE`/`DELETE`/DDL on a real database without confirmation. For write queries run a `SELECT` with the same `WHERE` first and wrap the change in a transaction the user can roll back.

## Performance

### Read the plan
```sql
EXPLAIN SELECT ...;                      -- estimates only, safe
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- Postgres: actually runs the query
```
`EXPLAIN ANALYZE` executes the statement. Never use it on `INSERT`/`UPDATE`/`DELETE` against real data unless inside a transaction you roll back, and ask first.

Look for: sequential scans on large tables with selective filters, row estimates far from actual rows (stale statistics: `ANALYZE`), sorts spilling to disk, nested loops over big sets, repeated subplans.

### Indexes
- Index columns used in `WHERE`, `JOIN` and `ORDER BY`; order composite columns by equality conditions first, then range, then sort.
- Functions on columns (`WHERE lower(email) = ...`, `WHERE date(ts) = ...`) defeat plain indexes; rewrite as a range or add an expression index.
- A leading wildcard `LIKE '%x'` cannot use a btree; consider trigram or full-text indexes.
- Each index slows writes and costs space; do not add one without evidence from the plan. Creating indexes on large live tables needs the safe procedure (see the safe-db-migration skill if available).

### Patterns to fix
- `SELECT *`: list needed columns; it blocks index-only scans and breaks when columns are added.
- N+1 from ORMs: one query per row in a loop. Use eager loading (`select_related`, `prefetch_related`, `includes`, `joinedload`) or a single `WHERE id IN (...)` query. Detect by enabling query logging for one request.
- `OFFSET` pagination slows linearly. Use keyset pagination:

```sql
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;
```
- `COUNT(*)` on huge tables for UI totals: consider approximate counts or caching.
- `DISTINCT` used to hide duplicate-producing joins: fix the join.

## Output format

1. Verdict: correct / has correctness bugs / slow, with one sentence of why.
2. Findings: `severity | location | problem | fix`.
3. The rewritten query, and any index DDL as a proposal (not executed).
4. Plan evidence: before/after key numbers (rows, timing, scan type) if you could measure; otherwise state it was not measured.
5. Assumptions made about schema, data size and engine version.

## Pitfalls

- Do not tune on an empty dev database; plans differ with data volume.
- Do not trust a cached warm run for timing; compare repeated runs.
- Do not rewrite a query into something that changes results without proving equality on sample data.
- Engine-specific advice must name the engine.

Source: [AnyModel skills catalog](https://anymodel.org/en/skills) · License: [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/)
