Dashboard loading takes 2 minutes - users complaining. Investigation reveals: missing indexes (10x), inefficient query (5x), stale statistics (3x). Apply all fixes: 150x total speedup, dashboard loads in 800ms. E-commerce checkout timing out - query takes 45 seconds. Root cause: N+1 query pattern + missing index. Rewrite to single query with proper index: 450x faster, 100ms response.

Shopify: Black Friday Checkout Timeout - 450x Speedup

Black Friday: checkout timing out (45 seconds), orders failing, revenue loss $50K/hour. Investigation: N+1 query pattern (1 + 50 queries per checkout) + missing index on inventory check. Emergency fix: rewrite to single query with JOIN, add index. Result: 45 seconds → 100ms (450x faster), checkouts working, revenue recovered.

Systematic diagnosis: (1) Identified slow endpoint: checkout API taking 45 seconds. (2) Analyzed queries: 51 queries per checkout (N+1 pattern). (3) Root causes: Application fetching product details in loop + inventory check missing index. (4) Emergency fixes: Rewrote to single query with JOIN, added index on inventory table. (5) Deployed in 30 minutes. (6) Result: 450x speedup, checkouts working.

-- Problem: Checkout taking 45 seconds

-- N+1 pattern: 1 query + 50 product queries

-- Original code (N+1):

-- cart_items = SELECT * FROM cart WHERE user_id = ?

-- for each item:

-- product = SELECT * FROM products WHERE id = item.product_id

-- inventory = SELECT * FROM inventory WHERE product_id = item.product_id

-- Result: 1 + 50 + 50 = 101 queries, 45 seconds

-- Fix: Single query with JOINs

EXPLAIN ANALYZE

SELECT

c.cart_id,

c.quantity,

p.product_id,

p.name,

p.price,

i.stock_quantity

FROM cart c

JOIN products p ON c.product_id = p.product_id

JOIN inventory i ON p.product_id = i.product_id

WHERE c.user_id = ?

AND i.stock_quantity >= c.quantity;

-- Before index:

-- Nested Loop (cost=0..50000 rows=50)

-- -> Nested Loop

-- -> Index Scan on cart

-- -> Seq Scan on products -- SLOW

-- -> Seq Scan on inventory -- SLOW

-- Time: 5000ms (still slow)

-- Add missing index

CREATE INDEX CONCURRENTLY idx_inventory_product_id

ON inventory(product_id);

-- After index:

-- Nested Loop (cost=0..500 rows=50)

-- -> Nested Loop

-- -> Index Scan on cart