Database Query Optimization

# Database Query Optimization

## EXPLAIN ANALYZE
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42 AND status = 'active';
```
Look for: `Seq Scan` (bad on large tables), `Nested Loop` with high row estimates, `Sort` without index.

## Indexing strategies
```sql
-- Composite index (column order matters: equality first, range last)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Partial index (only index what you query)
CREATE INDEX idx_active_orders ON orders (user_id) WHERE status = 'active';

-- Covering index (avoid table lookup)
CREATE INDEX idx_cover ON orders (user_id, status) INCLUDE (total, created_at);

-- GIN for array/jsonb
CREATE INDEX idx_tags ON posts USING GIN (tags);
```

## N+1 problem
```
BAD:  SELECT * FROM users           → 100 users
      SELECT * FROM orders WHERE user_id = ?  (×100 queries!)

GOOD: SELECT * FROM users
      SELECT * FROM orders WHERE user_id IN (1,2,3,...100)

BEST: SELECT u.*, o.* FROM users u
      LEFT JOIN orders o ON o.user_id = u.id
```
ORM solutions: Prisma `include`, SQLAlchemy `joinedload`, Drizzle `.leftJoin()`.

## Pagination
```sql
-- Offset (slow for deep pages)
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000; -- scans 10020 rows

-- Cursor-based (fast, consistent)
SELECT * FROM posts WHERE id > :last_id ORDER BY id LIMIT 20;
```

## Common anti-patterns
- `SELECT *` when only 2 columns needed.
- Functions on indexed columns: `WHERE YEAR(created_at) = 2026` — use range instead.
- Missing `LIMIT` on potentially large result sets.
- Implicit type casting in WHERE (kills index).
- Not using connection pooling (PgBouncer, Prisma pool).

## Monitoring
```sql
-- PostgreSQL slow query log
ALTER SYSTEM SET log_min_duration_statement = 100; -- ms
SELECT pg_reload_conf();

-- Top slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
```