SQL Optimization Techniques That Matter

Slow queries can kill application performance. Through working with PostgreSQL and Oracle databases, I've learned that small optimizations can yield massive performance improvements. Here are the techniques that have made the biggest difference in real projects.

1. Index Strategy

Proper indexing is the foundation of query performance:

-- Composite index for common query patterns
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at);

-- Partial index for specific conditions
CREATE INDEX idx_active_products 
ON products(category_id) 
WHERE is_active = true;

-- Covering index to avoid table lookups
CREATE INDEX idx_user_profile_covering 
ON users(id) 
INCLUDE (name, email, created_at);

Remember: indexes speed up reads but slow down writes. Find the right balance for your workload.

2. Query Rewriting Techniques

-- Bad: Fetches unnecessary data
SELECT * FROM users WHERE status = 'active';

-- Good: Only fetch what you need
SELECT id, name, email FROM users WHERE status = 'active';

-- Slower with large subqueries
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- Faster with EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.total > 100
);

3. Join Optimization

Proper join techniques can dramatically improve performance:

-- Use appropriate join types
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

-- Filter early to reduce join size
SELECT u.name, o.total
FROM (
    SELECT id, name FROM users 
    WHERE created_at > '2024-01-01'
) u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

4. Pagination Done Right

OFFSET becomes slow with large datasets. Use cursor-based pagination:

-- Slow with large offsets
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- Fast cursor-based pagination
SELECT * FROM posts 
WHERE created_at < '2024-01-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 20;

-- For next page, use the last created_at value as cursor

5. Analyzing Query Performance

Always use EXPLAIN to understand query execution:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending';

-- Look for:
-- • Sequential scans on large tables
-- • High cost operations
-- • Missing index usage
-- • Excessive buffer reads

The execution plan tells you exactly where the bottlenecks are.

Performance Monitoring

Optimization is an ongoing process. Set up monitoring for slow queries, track performance metrics over time, and regularly review query patterns. Tools like pg_stat_statements in PostgreSQL can help identify the queries that need attention most.

Remember: premature optimization is the root of all evil, but ignoring performance until it becomes a problem is equally dangerous. Profile first, optimize second.