SQL Query Optimizer

Turn "this query is slow" into a ranked, evidence-backed remediation plan.

When to use

Use for slow SELECTs, timeouts, lock contention, runaway DML, plan regressions, index design, and reviewing SQL before it ships.

Do not use for: writing a query from scratch (that is authoring, not tuning), ORM configuration debugging, or infrastructure sizing.

Workflow

1. Gather context before theorising

Never optimise blind. Ask for whatever is missing, but proceed with assumptions clearly labelled if the user cannot supply it:

Plan commands by engine:

2. Run the static linter first

Before reasoning about the plan, catch the mechanical defects:

python3 scripts/sql_lint.py query.sql --dialect postgres --format md

It flags the anti-patterns in the catalogue below, reports query complexity, and proposes a candidate composite index using the equality → range → sort ordering. Its output is a set of leads: a static rule cannot know your data distribution, so confirm everything against a real plan.

3. Read the plan top-down, cost bottom-up

Find the operator that dominates actual time, not estimated cost. Then check:

• Cardinality misestimation — actual rows ≫ or ≪ estimated rows (>10× off). Root cause is usually stale statistics, correlated predicates, or a function wrapping a column.
• Scan type — Seq/Full scan on a large table with a selective predicate.
• Join strategy — Nested loop over a large outer input; hash join spilling to disk; merge join forcing an expensive sort.
• Materialisation — sorts, hash aggregates, or CTEs spilling to temp files.
• Row multiplication — a join fan-out that inflates rows before filtering.

4. Match against the anti-pattern catalogue

5. Design indexes deliberately

Order composite index columns as equality → range → sort/include:

-- WHERE tenant_id = ? AND status = ? AND created_at >= ? ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Rules:

• One well-ordered composite index beats three single-column indexes.
• Add covering columns (INCLUDE in PG/SQL Server) to enable index-only scans.
• Use partial indexes for skewed predicates: WHERE status = 'pending'.
• Every index costs write throughput and storage — state the tradeoff explicitly.
• Verify the index is actually used after creation; do not assume.
• Use CONCURRENTLY (PG) or ONLINE=ON (SQL Server) to avoid blocking prod.
• Before adding, check for a redundant prefix of an existing index.

6. Escalate only if rewrites and indexes are not enough

In order of increasing cost: refresh statistics → increase work_mem/sort memory → materialised view or summary table → denormalisation → partitioning → caching layer → sharding. Never propose sharding as a first response.

7. Report

Deliver a table of recommendations, each with: change, rationale tied to a plan line, estimated speedup, risk, and rollback. Provide runnable DDL/SQL, and always tell the user to re-run EXPLAIN ANALYZE to verify.

Tools

python3 scripts/sql_lint.py query.sql --dialect postgres --format md
echo "SELECT * FROM t WHERE YEAR(d)=2025" | python3 scripts/sql_lint.py --min-severity high

Exit code 1 means at least one critical/high finding — usable as a CI gate on checked-in SQL.

Worked example

Query

SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE YEAR(o.created_at) = 2025
  AND c.country = 'FR'
ORDER BY o.created_at DESC
LIMIT 50;

Plan finding — Seq Scan on orders (12.4 M rows, actual 8.1 s), then hash join, then top-N sort. Estimated 1 200 rows, actual 486 000 rows.

Diagnosis

1. YEAR(o.created_at) is non-sargable → no index can be used.
2. SELECT * pulls 41 columns, preventing an index-only scan.
3. The sort runs after the join over ~486 k rows.

Fix

CREATE INDEX CONCURRENTLY idx_orders_created_customer
  ON orders (created_at DESC) INCLUDE (customer_id, total_amount, status);
CREATE INDEX CONCURRENTLY idx_customers_country_id
  ON customers (country, id);

SELECT o.id, o.created_at, o.total_amount, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2025-01-01'
  AND o.created_at <  DATE '2026-01-01'
  AND c.country = 'FR'
ORDER BY o.created_at DESC
LIMIT 50;

Result — index scan walks created_at DESC and stops early; sort eliminated. 8.1 s → ~15 ms. Cost: two indexes, ~180 MB, small write penalty on orders.

Quality checklist

• sql_lint.py run and every finding either fixed or explicitly dismissed
• Every recommendation cites a specific line/operator of the plan
• Rewrites are semantically identical (NULL handling, duplicates, ordering)
• No index recommended that duplicates an existing prefix
• Write-path cost of new indexes stated
• Engine-specific syntax is correct for the stated version
• Verification step (EXPLAIN ANALYZE re-run) included
• Destructive or blocking operations flagged with a safe alternative

Failure modes