sql-query-optimization
verifieda6319faa-3964-4809-8856-b3ae71ff0c36
Diagnose slow SQL with EXPLAIN, identify the real cause (missing index, N+1, full scan), and fix it. Use for any slow database query.
Metadata
Skill file
# SQL Query Optimization
Use when a database query is slow — run `EXPLAIN ANALYZE`, read the plan, identify the real cause, and fix it with before/after verification.
## Step 1: Run EXPLAIN ANALYZE
```sql
-- PostgreSQL — the "ANALYZE" actually executes the query and times it
EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'east'
AND o.created_at > now() - interval '90 days';
-- SQLite
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 123;
```
## Step 2: Read the Plan
```
Seq Scan on orders o (cost=0.00..12500.00 rows=180000 width=32)
(actual time=0.015..124.300 rows=180000 loops=1)
Filter: (created_at > (now() - '90 days'::interval))
Rows Removed by Filter: 820000
```
| Signal | Meaning |
|---|---|
| **Seq Scan** | Full table scan — reading every row |
| **Index Scan / Bitmap Index Scan** | Using an index — usually good |
| **rows=180000** (estimate) | Planner's *guess* at row count |
| **actual ... rows=180000** | What *actually* happened |
| **Rows Removed by Filter** | Rows read but discarded — wasted work |
| **cost** | Arbitrary units; compare relative, not absolute |
**Critical**: Compare the *estimated* rows to the *actual* rows. A big mismatch means stale statistics.
```sql
-- Fix stale statistics
ANALYZE orders;
```
## Step 3: Fix by Cause
| Cause | Fix |
|---|---|
| Seq Scan on a large filtered table | Add an index on the filter column |
| Index exists but not used | Function-wrapped column (index on `lower(email)`, not `email`) or statistics problem |
| N+1 from the ORM | Eager load (see `n-plus-one-query-detection`) |
| `WHERE ... OR ...` defeats indexes | Rewrite as `UNION` of indexed queries |
| `LIKE '%foo%'` (leading wildcard) | Full-text search index (GIN) or trigram index |
| `ORDER BY` on unindexed column | Add index or limit the sort set |
### Adding an Index (with write-cost awareness)
```sql
-- Simple index on the filter column
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Composite index — matches WHERE + ORDER BY
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);
-- Partial index — smaller, faster when the condition is selective
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
-- Verify the index is actually used after creating it
EXPLAIN ANALYZE SELECT ... ; -- should now show Index Scan
```
### Rewriting the Query Shape
```sql
-- ❌ BAD — OR defeats index usage
SELECT * FROM orders WHERE customer_id = 123 OR status = 'pending';
-- ✅ BETTER — two indexed queries combined
SELECT * FROM orders WHERE customer_id = 123
UNION
SELECT * FROM orders WHERE status = 'pending';
```
```sql
-- ❌ BAD — function on the indexed column
SELECT * FROM users WHERE lower(email) = 'alice@example.com';
-- ✅ GOOD — functional index, or normalize the value
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'alice@example.com';
```
## Step 4: Verify with Before/After
```sql
-- Enable query timing
\timing on
-- BEFORE — record time and plan
EXPLAIN ANALYZE SELECT ... ;
-- Execution Time: 124.3 ms
-- ... apply the fix ...
-- AFTER — confirm improvement
EXPLAIN ANALYZE SELECT ... ;
-- Execution Time: 0.8 ms ← 150x faster, proven
```
## Guardrails
- **Never** add an index for every slow query — indexes have write cost (every INSERT/UPDATE must maintain them) and disk cost. Add the *minimum* index that fixes the dominant query.
- **Never** trust row *estimates* over *actuals* — stale stats lie. `ANALYZE` first.
- **Never** fix SQL when the ORM is the real problem — a 1000-query N+1 can't be fixed with an index; fix the eager loading.
- **Never** run `EXPLAIN ANALYZE` on a query with side effects (INSERT/UPDATE/DELETE) against production — it *executes* the query.
## Pitfalls
- **Adding an index for every slow query**: Each index slows writes and consumes disk. Profile the query, add one targeted index, re-measure.
- **Trusting estimates over actuals**: The planner estimates `rows=10` but `actual rows=100000` because stats are stale. Run `ANALYZE` before changing anything.
- **Fixing SQL when the ORM is the problem**: The query is "slow" because it runs 1000 times (N+1). Optimizing the single query is pointless; fix the loop.
- **Index not used because of a function wrapper**: `WHERE lower(email) = ...` bypasses the index on `email`. Use a functional index or normalize the data.
- **Measuring "slow" by wall-clock during peak load**: A query that's normally fast looks slow under load. Reproduce in isolation with `EXPLAIN ANALYZE` to get a true baseline.
## Verify / Checklist
- [ ] `EXPLAIN ANALYZE` captured before and after
- [ ] Plan shows the bottleneck (Seq Scan, N+1, stale stats, missing index)
- [ ] Row estimates compared to actuals — `ANALYZE` run if mismatched
- [ ] Root cause matched to the correct fix (index vs query rewrite vs ORM eager load)
- [ ] Index created only if it's the right tool (write cost considered)
- [ ] Query timing measured before/after (`\timing on`) — improvement quantified
- [ ] No side-effecting query run with EXPLAIN ANALYZE against productionAttached files
No attached files.