OR to UNION ALL: E-commerce Product Search

intermediate

E-commerce search allows filtering by category OR brand. Query with OR takes 15 seconds scanning 5M products. Users abandon search. Rewrite using UNION ALL to enable index usage and reduce to 0.5 seconds.

-- BEFORE: OR prevents index usage (15 seconds)

-- Problem: Can't use index on category AND index on brand simultaneously

SELECT

product_id,

product_name,

category,

brand,

price

FROM products

WHERE category = 'Electronics' OR brand = 'Apple';

-- Execution plan:

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

-- Filter: (category = 'Electronics' OR brand = 'Apple')

-- Time: 15 seconds

-- Why it's slow:

-- 1. Full table scan of 5M products

-- 2. Can't use index_category (OR prevents it)

-- 3. Can't use index_brand (OR prevents it)

-- 4. Must check every row against both conditions

-- AFTER: UNION ALL enables index usage (0.5 seconds)

-- Solution: Split into two queries, each using its index

SELECT

product_id,

product_name,

category,

brand,

price

FROM products

WHERE category = 'Electronics'

UNION ALL

SELECT

product_id,

product_name,

category,

brand,

price

FROM products