Debug: PostgreSQL slow queries with EXPLAIN ANALYZE

PostgreSQL queries are slow and need to be analyzed and optimized.

Using EXPLAIN ANALYZE to diagnose slow queries:

-- Basic analysis
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

-- Key things to look for in output:

-- 1. Seq Scan (full table scan) - usually bad on large tables
-- Seq Scan on orders  (cost=0.00..15234.00 rows=500 width=120)
--   Filter: ((user_id = 123) AND (status = 'pending'))
--   Rows Removed by Filter: 99500
-- FIX: Add index: CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 2. High actual vs estimated rows - stale statistics
-- Index Scan (estimated rows=10, actual rows=50000)
-- FIX: ANALYZE orders;  -- Update statistics

-- 3. Nested Loop with high row counts
-- Nested Loop (actual rows=1000000)
--   -> Seq Scan on a (actual rows=1000)
--   -> Index Scan on b (actual rows=1000, loops=1000)
-- FIX: Consider Hash Join or Merge Join
-- May need: SET work_mem = '256MB'; for in-memory sorts/hashes

-- Useful EXPLAIN options:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...
-- BUFFERS shows cache hit ratio:
-- Buffers: shared hit=100 read=5000
-- (hit=100 means 100 pages from cache, read=5000 from disk)

-- Check for missing indexes:
SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan  -- More seq scans than index scans
ORDER BY seq_tup_read DESC;

-- Find slow queries automatically:
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
-- Requires: CREATE EXTENSION pg_stat_statements;

EXPLAIN ANALYZE runs the actual query and shows real execution times and row counts. Without it, you're guessing where the bottleneck is.

PostgreSQL query performance optimization