PostgreSQL Performance Optimization: 10x Faster Queries

2025-12-20 194 words One minute

Contents

Slow queries almost always trace back to a handful of root causes: missing indexes, inefficient query patterns, or default config values. This article follows PostgreSQL docs best practices with real test data to help you systematically diagnose each.

Index Optimization

        
-- common mistake: index created but not used
CREATE INDEX idx_user_email ON users(email);

-- use EXPLAIN to confirm index used
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- composite index order: equality first, range last
CREATE INDEX idx_order_status_date ON orders(status, created_at);

Query Optimization

        
        
        
    
-- use EXPLAIN to analyze
-- visualize execution plans at https://explain.depesz.com or https://pganalyze.com
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 123 
  AND created_at > '2025-01-01';

-- N+1 query
-- wrong
SELECT * FROM orders LIMIT 100;
-- then for each order: SELECT * FROM users WHERE id = order.user_id

-- correct: use JOIN
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
LIMIT 100;

Config Tuning

        
# postgresql.conf
shared_buffers = 256MB        # 25% RAM
effective_cache_size = 768MB  # 75% RAM
work_mem = 64MB
random_page_cost = 1.1        # use this for SSD

Conclusion

Optimization trio: indexes + queries + config. Investigate in order.