PostgreSQL Performance Optimization: 10x Faster Queries

2025-12-20 148 words One minute

Contents

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
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.