1-- Diagnose with EXPLAIN ANALYZE
EXPLAIN ANALYZE actually runs the query — use on production with caution for expensive queries
2EXPLAIN ANALYZE
3SELECT u.name, COUNT(o.id) as order_count
4FROM users u
5LEFT JOIN orders o ON o.user_id = u.id
6WHERE u.created_at > NOW() - INTERVAL '30 days'
7GROUP BY u.id, u.name
8ORDER BY order_count DESC
9LIMIT 100;
10 
11-- Bad plan output:
12-- Seq Scan on users  (cost=0.00..12847.00 rows=50000)
13-- Seq Scan on orders (cost=0.00..48000.00 rows=2000000)
14 
15-- Fix: add indexes
16CREATE INDEX idx_users_created_at ON users(created_at);
17CREATE INDEX idx_orders_user_id ON orders(user_id);
18 
19-- Good plan after indexes:
20-- Index Scan on users using idx_users_created_at
21-- Index Scan on orders using idx_orders_user_id
22 
23-- Composite index: column order matters
24-- Query: WHERE user_id = ? AND status = ? ORDER BY created_at
25CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
Composite index leftmost-prefix rule: (a, b, c) supports queries on a, (a,b), or (a,b,c) — not b or c alone
26-- Supports: WHERE user_id=? AND status=?
27-- Also supports: WHERE user_id=? alone
28-- Does NOT support: WHERE status=? alone (leftmost prefix rule)
29 
30-- Partial index: only pending orders (tiny fraction)
Partial index: if only 0.1% of orders are pending, this index is 1000x smaller than a full index
31CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
32WHERE status = 'pending';
33-- 1000x smaller than full index on large tables with few pending orders
34 
35-- Covering index: satisfy query from index alone
36CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name, id);
INCLUDE stores columns in leaf nodes — query never hits main table, much faster for read-heavy workloads
37-- Query: SELECT id, name FROM users WHERE email = ?
38-- → Index-only scan — never touches the main table
39 
40-- EXPLAIN keywords to know:
41-- "Seq Scan"    → full table scan, add an index
42-- "Index Scan"  → using index + fetching from table
43-- "Index Only"  → index has all needed data (fastest)