## SQL Query Performance Reference

### Index design rules
1. Index columns in `WHERE`, `JOIN`, `ORDER BY`, `GROUP BY`
2. Composite index: **equality columns first**, then range/sort
3. Leftmost-prefix: `(a, b, c)` serves `a`, `a+b`, `a+b+c` — never `b` alone
4. Covering index: include `SELECT` columns to avoid table lookups
5. Don't over-index: writes slow, storage grows

### Query rewrite patterns
```sql
-- Bad: function on column defeats index
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

-- Good: functional index (Postgres) or store computed column
CREATE INDEX idx_email_lower ON users (LOWER(email));

-- Bad: SELECT * fetches everything
SELECT * FROM orders;

-- Good: only needed columns
SELECT id, total, status FROM orders;

-- Bad: IN with large subquery
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE ...);

-- Often better: EXISTS or JOIN
SELECT o.* FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND ...);
```

### Reading the plan
| Plan node | Meaning |
|-----------|---------|
| `SCAN` | Full table scan (bad on big tables) |
| `SEARCH ... USING INDEX` | Index lookup (good) |
| `COVERING INDEX` | No table lookup needed (best) |
| `USE TEMP B-TREE` | Sorting in memory (watch for large sorts) |

### Maintenance
- `ANALYZE` after bulk loads (updates planner stats)
- `VACUUM` reclaims space and defragments
- Re-check plans after schema changes (indexes don't self-maintain)

### Don't prematurely optimize
- Index tables with 100 rows = pointless
- Check for N+1 in app code first (often the real bottleneck)
- Measure with production-shaped data, not empty tables