Slow queries are inevitable in production systems. The key is knowing how to identify and fix them before they become critical issues.
Start with EXPLAIN ANALYZE
Before optimizing anything, you need to understand what's happening. EXPLAIN ANALYZE runs the query and shows the actual execution plan:
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id;Look for these red flags in the output:
- Seq Scan on large tables (missing index)
- Nested Loop with high row counts
- Sort operations with high memory usage
- Large difference between estimated and actual rows
Indexing Strategies
Not all indexes are equal. Choose the right type for your query patterns:
-- B-tree: equality and range queries (default)
CREATE INDEX idx_users_created ON users(created_at);
-- Composite: multiple columns in WHERE/ORDER BY
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial: subset of rows
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Covering: include columns to avoid table lookup
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);Common Pitfalls
Watch out for these query patterns that prevent index usage:
- Functions on indexed columns:
WHERE LOWER(email) = '...' - Implicit type casting:
WHERE id = '123'(string vs integer) - Leading wildcards:
WHERE name LIKE '%smith' - OR conditions on different columns
An index that's never used is worse than no index - it slows down writes without benefiting reads.
Monitoring in Production
Enable pg_stat_statements to track query performance over time:
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;Conclusion
Query optimization is iterative. Measure, understand, change one thing, measure again. Don't optimize prematurely - focus on queries that actually impact your users.