Composite Index Design for E-commerce Search

advanced

E-commerce product search filters by category, price range, and rating. Query scans 5M products taking 8 seconds. Design optimal composite index to reduce to 0.3 seconds.

-- BEFORE: No index, full table scan (8 seconds)

SELECT product_id, name, price, rating

FROM products

WHERE category = 'Electronics'

AND price BETWEEN 100 AND 500

AND rating >= 4.0

ORDER BY price ASC

LIMIT 20;

-- Execution plan:

-- Seq Scan on products (cost=0..125000 rows=5000000)

-- Filter: category = 'Electronics' AND price >= 100 AND price <= 500 AND rating >= 4.0

-- Sort: price

-- Time: 8 seconds

-- Why it's slow:

-- 1. Scans all 5M products

-- 2. Filters in memory

-- 3. Sorts results

-- 4. No index usage

-- ATTEMPT 1: Single-column indexes (still slow - 6 seconds)

CREATE INDEX idx_category ON products(category);

CREATE INDEX idx_price ON products(price);

CREATE INDEX idx_rating ON products(rating);

-- Problem: Optimizer picks one index (category)

-- Uses idx_category: 500K Electronics products

-- Still filters price and rating in memory

-- Still sorts by price

-- Time: 6 seconds (only 25% faster)

-- ATTEMPT 2: Wrong column order (better but not optimal - 2 seconds)

CREATE INDEX idx_wrong_order ON products(price, category, rating);

-- Problem: price first (range condition)

-- Scans all products with price 100-500 (2M rows)

-- Then filters by category

-- Time: 2 seconds (4x faster, but not optimal)

-- SOLUTION: Optimal composite index (0.3 seconds)

CREATE INDEX idx_optimal ON products(

category, -- Most selective, equality (first)

rating, -- Equality (second)