Optimize Slow Database Queries in Production

Here's a systematic approach to optimize database performance:

Step 1: Identify Slow Queries

-- Enable query logging in PostgreSQL
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1 second
SELECT pg_reload_conf();

-- Or use pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    stddev_time
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

Step 2: Analyze Query Plans

-- Use EXPLAIN ANALYZE for detailed execution plan
EXPLAIN ANALYZE 
SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2024-01-01';

Step 3: Add Strategic Indexes

-- Index on foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite indexes for common queries
CREATE INDEX idx_users_created_status ON users(created_at, status);

-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

Step 4: Optimize Django ORM Queries

# ❌ N+1 Query Problem
users = User.objects.all()
for user in users:
    print(user.profile.bio)  # Hits database for each user

# ✅ Use select_related for foreign keys
users = User.objects.select_related('profile').all()

# ✅ Use prefetch_related for many-to-many
users = User.objects.prefetch_related('orders').all()

# ✅ Only fetch needed fields
users = User.objects.only('id', 'name', 'email').all()

Step 5: Query Optimization Techniques

-- Use LIMIT for pagination
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Use EXISTS instead of IN for large datasets
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Avoid SELECT * - specify only needed columns
SELECT id, name, email FROM users WHERE active = true;