query-optimizer

# Query Optimizer

Optimize SQL queries for better performance through indexing, rewriting, and analysis.

## Quick Start

Use EXPLAIN to analyze queries, add indexes on WHERE/JOIN columns, avoid SELECT *, limit results.

## Instructions

### Query Analysis with EXPLAIN

**Basic EXPLAIN:**
```sql
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
```

**EXPLAIN ANALYZE (actual execution):**
```sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
```

**Key metrics to check:**
- Seq Scan (bad) vs Index Scan (good)
- Rows: Estimated vs actual
- Cost: Lower is better
- Execution time

### Common Performance Issues

**1. Missing Indexes**

Problem:
```sql
-- Seq Scan on users (cost=0.00..1234.56)
SELECT * FROM users WHERE email = 'user@example.com';
```

Solution:
```sql
CREATE INDEX idx_users_email ON users(email);
-- Now: Index Scan using idx_users_email
```

**2. SELECT ***

Problem:
```sql
SELECT * FROM posts;  -- Fetches all columns
```

Solution:
```sql
SELECT id, title, created_at FROM posts;  -- Only needed columns
```

**3. N+1 Queries**

Problem:
```sql
-- Fetches posts
SELECT * FROM posts;
-- Then for each post:
SELECT * FROM users WHERE id = ?;
```

Solution:
```sql
-- Single query with JOIN
SELECT posts.*, users.name 
FROM posts 
JOIN users ON posts.user_id = users.id;
```

**4. No LIMIT**

Problem:
```sql
SELECT * FROM posts ORDER BY created_at DESC;  -- Returns all rows
```

Solution:
```sql
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
```

### Indexing Strategies

**Single column index:**
```sql
CREATE INDEX idx_users_email ON users(email);
```

**Composite index (order matters):**
```sql
-- For: WHERE user_id = ? AND created_at > ?
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
```

**Covering index (includes all needed columns):**
```sql
-- For: SELECT id, title FROM posts WHERE user_id = ?
CREATE INDEX idx_posts_user_id_title ON posts(user_id) INCLUDE (title);
```

**Partial index (filtered