E-commerce Product Search - Choosing the Right Index

intermediate

Build a product search system that handles exact matches, full-text search, and price ranges. Compare different index types.

-- Scenario: 5M products, multiple search patterns

-- 1. Exact SKU lookup (Hash vs B-tree)

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

sku VARCHAR(50) UNIQUE,

name TEXT,

description TEXT,

price DECIMAL(10,2),

category VARCHAR(100),

tags TEXT[],

metadata JSONB,

created_at TIMESTAMP

);

-- Option A: B-tree (default)

CREATE INDEX idx_sku_btree ON products(sku);

-- Option B: Hash (faster for equality)

CREATE INDEX idx_sku_hash ON products USING hash(sku);

-- Test query

EXPLAIN ANALYZE

SELECT * FROM products WHERE sku = 'LAPTOP-2024-XPS';

-- Results:

-- B-tree: 4.2ms (3 page reads)

-- Hash: 2.8ms (1 page read) - 33% faster!

-- Winner: Hash for exact SKU lookups

---

-- 2. Full-text product search (GIN)

CREATE INDEX idx_product_search ON products

USING gin(to_tsvector('english', name || ' ' || description));

-- Search query

EXPLAIN ANALYZE

SELECT product_id, name, price

FROM products

WHERE to_tsvector('english', name || ' ' || description)

@@ to_tsquery('english', 'wireless & headphones');

-- Without index: 2500ms (full table scan)

-- With GIN: 45ms (bitmap index scan) - 55x faster!

-- Advanced: Ranking results