Use when writing SQL queries, optimizing database performance, or analyzing data
Guidelines for SQL optimization, database performance, and data analysis.
| Problem | Solution |
|---|---|
| Subqueries | Replace with JOINs |
| Complex logic | Use CTEs for readability |
| Self-joins | Use window functions |
| Row-by-row | Batch operations |
| SELECT * | Specify columns |
| COUNT for existence | Use EXISTS |
Before (N+1):
-- 1 query for users, then N queries for posts
SELECT * FROM users WHERE active = true;
-- Then for each: SELECT * FROM posts WHERE user_id = ?;
After (Single Query):
SELECT u.id, u.name,
JSON_AGG(JSON_BUILD_OBJECT('id', p.id, 'title', p.title))
FILTER (WHERE p.id IS NOT NULL) AS posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.active = true
GROUP BY u.id, u.name;
| Type | Best For | Example |
|---|---|---|
| B-Tree | Equality, range, sort | Primary keys, dates |
| Composite | Multi-column WHERE | (status, created_at) |
| Partial | Filtered queries | WHERE status = 'active' |
| Covering | Index-only scans | Include all SELECT columns |
| GIN/GiST | Full-text, JSON | Text search |
SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- PostgreSQL: Find slow queries
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
| Symptom | Cause | Fix |
|---|---|---|
| Seq scan on large table | Missing index | Add appropriate index |
| Index not used | Stale stats | ANALYZE table |
| Lock contention | Long transactions | Reduce scope |
| Table bloat | Dead tuples | VACUUM or pg_repack |
Always analyze execution plans:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;
Normalize when:
Denormalize when:
| Layer | Tool | Use Case |
|---|---|---|
| Application | Redis, Memcached | Query results |
| Database | Query cache | Identical queries |
| Materialized View | PostgreSQL | Pre-computed aggregates |
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as post_count, MAX(created_at) as last_post
FROM posts
GROUP BY user_id;
CREATE INDEX ON user_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
## Summary
Key insight in 1-2 sentences.
## Key Metrics
| Metric | Value | Trend |
|--------|-------|-------|
## Insights
- Finding 1 with supporting data
- Finding 2 with supporting data
## Recommendations
1. Action item with expected impact
2. Suggested follow-up analysis
-- Migration: Add index for performance
-- Date: 2025-12-23
-- Ticket: DB-456
-- Forward (non-blocking)
CREATE INDEX CONCURRENTLY idx_posts_user_created
ON posts(user_id, created_at DESC);
-- Rollback
DROP INDEX CONCURRENTLY idx_posts_user_created;
-- Validation
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1 ORDER BY created_at DESC;
When optimizing: