SQL EXPLAIN ANALYZE for Query Tuning

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Explain with actual execution stats
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM orders WHERE user_id = 42;

-- Explain in JSON format (easier to parse)
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders
JOIN users ON users.id = orders.user_id
WHERE orders.created_at > '2024-01-01';

-- Slow query pattern: Sequential Scan on large table
-- Before (no index)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE email = 'user@example.com';
-- Seq Scan on orders  (cost=0.00..15234.00 rows=1 width=64)
--   Filter: (email = 'user@example.com')
--   Rows Removed by Filter: 500000
--   Planning Time: 0.1ms
--   Execution Time: 245.3ms

-- After adding index
CREATE INDEX idx_orders_email ON orders (email);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE email = 'user@example.com';
-- Index Scan using idx_orders_email (cost=0.42..8.44 rows=1 width=64)
--   Planning Time: 0.2ms
--   Execution Time: 0.05ms

-- Key things to look for:
-- 1. Seq Scan on large tables (may need index)
-- 2. Nested Loop with high row counts
-- 3. Sort with high memory usage
-- 4. Hash Join vs Merge Join efficiency
-- 5. Rows estimated vs actual (stats accuracy)

-- Update statistics for better plans
ANALYZE orders;

-- Find slow queries (pg_stat_statements)
SELECT
    query,
    calls,
    mean_exec_time::int AS avg_ms,
    total_exec_time::int AS total_ms,
    rows / GREATEST(calls, 1) AS avg_rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;