Debug: PostgreSQL query performance with EXPLAIN ANALYZE

SQL query is slow and need to understand why and how to optimize it.

Use EXPLAIN ANALYZE to understand query execution:

-- Basic analysis
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name;

Key things to look for in output:

• Seq Scan on large tables -> needs an index
• Nested Loop with many rows -> consider Hash Join (may need more work_mem)
• Rows estimated vs actual differ wildly -> run ANALYZE to update statistics
• Sort with large data -> add index matching ORDER BY

Common optimizations:

-- Add index for common queries
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Partial index for common filters
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Covering index (includes all needed columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at) INCLUDE (total);

-- Update statistics after large data changes
ANALYZE users;
ANALYZE orders;

-- Check index usage
SELECT relname, idx_scan, seq_scan
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_scan DESC;

Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) for buffer cache info.

EXPLAIN ANALYZE shows the actual execution plan with real timing, not just estimates. It reveals exactly where time is spent.

PostgreSQL databases with slow queries