query-optimization

# Query Optimization Skill

Patterns for improving database query performance.

## Understanding Query Performance

### EXPLAIN ANALYZE

```sql
-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.role = 'ADMIN'
GROUP BY u.id;

-- Key metrics to watch:
-- - Seq Scan vs Index Scan
-- - Actual rows vs Estimated rows
-- - Sort operations
-- - Nested loops vs Hash joins
```

### Query Plan Reading

```
Seq Scan         -- Full table scan (often bad)
Index Scan       -- Using index (good)
Index Only Scan  -- All data from index (best)
Bitmap Scan      -- Multiple index matches
Hash Join        -- Good for large joins
Nested Loop      -- Good for small inner table
Sort             -- May use disk if large
```

## Indexing Strategies

### When to Index

```sql
-- Index: Foreign keys (always)
CREATE INDEX idx_posts_author_id ON posts(author_id);

-- Index: Frequently filtered columns
CREATE INDEX idx_users_role ON users(role);

-- Index: Columns in ORDER BY
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- Index: Columns in JOIN conditions
CREATE INDEX idx_comments_post_id ON comments(post_id);
```

### Composite Indexes

```sql
-- Order matters! Most selective first
CREATE INDEX idx_posts_status_date
ON posts(published, published_at DESC);

-- Covers queries like:
SELECT * FROM posts WHERE published = true ORDER BY published_at DESC;
SELECT * FROM posts WHERE published = true AND published_at > '2024-01-01';

-- Does NOT help:
SELECT * FROM posts WHERE published_at > '2024-01-01'; -- Needs leading column
```

### Partial Indexes

```sql
-- Only index active records
CREATE INDEX idx_active_users_email
ON users(email)
WHERE deleted_at IS NULL;

-- Only index specific values
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';
```

### Covering Indexes

```sql
-- Include all needed columns to avoid table lookup
CREATE INDEX idx_users_covering
ON users(email) I