-- Composite index: (user_id, status, created_at)
CREATE INDEX idx_orders_composite ON orders(user_id, status, created_at);

-- GOOD: uses the index (leading columns present)
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending';
-- Index Scan on idx_orders_composite

-- GOOD: uses the index (leading column + range on 3rd)
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'pending'
  AND created_at > NOW() - INTERVAL '30 days';
-- Index Scan on idx_orders_composite

-- BAD: cannot use composite index (leading column missing)
SELECT * FROM orders
WHERE status = 'pending';
-- Seq Scan (unless there's a separate index on status)

-- BAD: cannot use composite index efficiently
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '30 days';
-- Seq Scan

Indexing — B-Trees, Hash Indexes, Composite Indexes

Indexing — B-Trees, Hash Indexes, Composite Indexes

Indexing — B-Trees, Hash Indexes, Composite Indexes