E-Commerce Dashboard - 150x Speedup (Multiple Root Causes)

advanced

E-commerce dashboard loading takes 2 minutes. Users complaining. Investigation reveals 5 slow queries with multiple issues: missing indexes, stale statistics, N+1 pattern, inefficient subquery, memory spill. Apply systematic optimization: add indexes (10x), update statistics (3x), rewrite N+1 (100x), optimize subquery (5x), increase work_mem (10x). Combined result: 150x total speedup, dashboard loads in 800ms.

-- Problem: Dashboard takes 2 minutes to load

-- 5 queries identified as slow

-- Step 1: Identify slow queries

SELECT

substring(query, 1, 100) as query_preview,

calls,

total_exec_time / 1000 as total_seconds,

mean_exec_time / 1000 as avg_seconds

FROM pg_stat_statements

WHERE query LIKE '%dashboard%'

ORDER BY total_exec_time DESC;

-- Result: 5 queries taking 120 seconds total

-- Query 1: Recent orders (30 seconds)

EXPLAIN ANALYZE

SELECT * FROM orders

WHERE created_at > NOW() - INTERVAL '7 days'

ORDER BY created_at DESC

LIMIT 100;

-- EXPLAIN output:

-- Seq Scan on orders (cost=0..500000 rows=100000)

-- Filter: (created_at > NOW() - INTERVAL '7 days')

-- Rows Removed by Filter: 9900000

-- Time: 30000ms

-- Diagnosis: Missing index on created_at

-- Fix 1: Add index

CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- Verify:

EXPLAIN ANALYZE

SELECT * FROM orders

WHERE created_at > NOW() - INTERVAL '7 days'

ORDER BY created_at DESC

LIMIT 100;

-- EXPLAIN output:

-- Index Scan using idx_orders_created_at (cost=0..1000 rows=100)

-- Time: 50ms

-- Speedup: 30000ms → 50ms = 600x faster

-- Query 2: Customer orders with totals (40 seconds)