SQL Query Optimization and Schema Review

QUERY PERFORMANCE ANALYSIS

DIAGNOSTIC FIRST STEP: Run EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
AND c.status = 'active'
ORDER BY o.created_at DESC LIMIT 100;

COMMON BOTTLENECK: Sequential scan on orders table (30 sec runtime = ~16K rows/sec = full table scan)

ROOT CAUSE CHECK
1. Missing index on (created_at, customer_id)? Add composite index:
   CREATE INDEX idx_orders_date_customer ON orders(created_at DESC, customer_id);
2. Stale statistics? Run ANALYZE on table (planner uses outdated row estimates)
3. JOIN order wrong? Planner chose nested loop instead of hash join?

OPTIMIZED QUERY
SELECT o.id, o.total, c.name FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
AND c.status = 'active'
ORDER BY o.created_at DESC
LIMIT 100;

EXPECTED PLAN: Index scan on idx_orders_date_customer (milliseconds), hash join with customers, early LIMIT cut

REWRITE TACTICS
-> Filter early: Move c.status to JOIN condition (not WHERE) to reduce joined rows
-> Add covering index: Include columns needed in SELECT (c.name) to avoid table lookup
-> Partition strategy: If table > 1GB, partition by month (query scans fewer partitions)

VERIFY: Rerun EXPLAIN ANALYZE; target: < 100 ms, uses index scan, < 500 rows examined